Descrizione
Le query tra più tabelle
Obiettivi della lezione
Saper creare delle query tra più tabelle di excel.
Indice lezione
Lezione 18 - Foglio Elettronico
18.1
Query tra tabelle;
18.2
Esercizio 1;
18.3
Esercizio 2;
18.4
Esercizio 3;
18.5
Esercizio 4;
18.6
Esercizio 5;
18.7
Esercizio 6;
18.8
Esercizio 7;
18.9
Esercizio 8;
18.10 Esercizio 9;
18.11 Esercizio 10.
Dati sulla lezione
|
Versione: |
v1.0 |
|
Tempi: |
1 ora / 60 minuti |
|
Data svolgimento lezione: |
16 Aprile 2003 |
|
Autore dei contenuti: |
Vastapane Marco Attilio – Giuliano Luca |
|
Autore materiali didattici: |
Togni Francesca |
La Teoria
Per effettuare delle Query tra più tabelle, è necessario creare una relazione tra le medesime utilizzando il Microsoft Query, dal momento che la creazione guidata non ci permette di fare questa operazione. Nella lezione precedente abbiamo visto come effettuare questa operazione.
Una volta messe in relazione tra di loro è possibile andarci a lavorare sopra.
Richiamiamo il file salvato nella lezione precedente database esercizi.xls . La schermata che ci appare dovrebbe essere questa.
Apriamo il Microsoft Query nel modo che conosciamo, ed andiamo a fare una query tra le due tabelle.
Ripuliamo i vari criteri e la parte riservata alla visualizzazione dei dati, semplicemente selezionando gli elementi che desideriamo eliminare, quindi premere CANC, come spiegato nella lezione precedente.
Al termine di questa operazione dovremmo trovarci di fronte ad una finestra simile a questa:
Il risultato che desideriamo ottenere riguarda la visualizzazione dei subagenti e del rispettivo agente della zona Sud.
In questo caso occorre prelevare da entrambe le tabelle i dati necessari per visualizzare i risultati ed ovviamente i campi che assumeranno nel loro interno i dati per il filtro.
Quindi saranno necessari nell’area di visualizzazione dei dati, dalla tabella Agenti$ i campi Nominativo, Zona e codice; della tabella Produttori$ , codice e nome subagente.
Vedremo che quando andremo a leggere i risultati, il campo codice sarà uguale per entrambe le tabelle, poiché è proprio quel campo a legarle tra di loro con una relazione uno a molti.
Ecco come dovrebbe apparire la schermata. L’ordine dei campi è comunque soggettivo.
I campi riferiti al Nominativo degli Agenti, sono ripetuti tante volte quanti sono i subagenti alle loro dipendenze.
Se lo si desidera, si può inserire anche il campo Provvigioni della tabella Produttori , per ordinare i dati in modo crescente,
Dopo aver inserito nella parte riservata ai dati anche il campo Provvigioni, selezionare l’intera colonna e premere l’apposito tasto per ordinare i dati in modo crescente. Non appena verrà lanciata la query, il giusto ordinamento comparirà nella zona riservata alla visualizzazione dei dati.
Restituendo il risultato ad Excel, ecco cosa compare.
Torniamo al Microsoft Query .
Per andare a variare i vari criteri di ricerca, è anche possibile cliccare 2 volte sul campo criteri, ove avevamo trascinato il nome del campo da analizzare. Nell’esempio sottostante sarà sufficiente cliccare sulla voce Zona
Così facendo si aprirà una finestra di dialogo, con la quale sarà relativamente semplice impostare nuovi criteri di ricerca, modificando il nome del campo su cui vogliamo porre delle limitazioni.
La stessa operazione può essere effettuata premendo il tasto recante una freccia rivolta verso il basso, che compare quando posizioniamo il mouse sulla voce del Campo criteri.
Questa possibilità esiste ovviamente per tutte le celle del Campo criterio.
Nella parte invece riservata alla lettura dei risultati, è possibile, in alternativa al trascinamento, andare a premere il tasto che si trova sulla cella ancora libera, non interessata quindi da nessun identificativo di campo. In questo modo è possibile scegliere tra i cari campi disponibili all’interno del nostro database.
q Per andare a variare invece solamente il tipo di criterio di ricerca, si potrà in alternativa alla digitazione, cliccare 2 volte sul Valore impostato. Nell’esempio di prima, il valore impostato era Sud.
Chi già conosce Access, è abituato ad utilizzare per queste ricerche comandi in lingua inglese. Il nostro editor, li ha convertiti in italiano, per facilitarci il compito. Quindi, nell’elenco che comparirà avremo la possibilità di sceglierli già tradotti, mentre se andremo a scriverli in inglese, non appena avremo terminato la digitazione, li osserveremo scritti in italiano.
Andiamo quindi ad utilizzare il nuovo comando per ottenere tra i criteri una schermata come la seguente.
q Cliccando 2 volte sulla cella, ora vuota del Valore, comparirà una finestra di dialogo simile per contenuti all’impostazione dei criteri della Creazione Guidata della Query. Nella casella operatore sarà possibile settare il criterio di ricerca desiderato.
q Nella finestra denominata Valore, potremo inserire uno specifico valore da ricercare.
q Premendo il tasto Valori…, vengono visualizzati relativamente al campo prescelto i vari dati presenti nell’archivio.
Tra i principali parametri di ricerca possiamo trovare:
|
equivale a |
il campo è identico al valore |
|
non equivale a |
il campo non è identico al valore |
|
è maggiore di |
il campo è maggiore del valore |
|
è minore di |
il campo è minore del valore |
|
è minore di o uguale a |
il campo è minore di o uguale al valore |
|
è maggiore di o uguale a |
il campo è maggiore di o uguale al valore |
|
è tra |
il campo si trova tra 2 valori separati da
virgole |
|
inizia con |
il campo inizia con un valore |
|
Contiene |
il campo contiene i valori |
|
è nullo |
il campo è vuoto |
|
non è nullo |
il campo è pieno |
|
finisce con |
il campo finisce con un valore |
q Proviamo ad esempio ad inserire come operatore riguardo al campo Nominativo la voce Contiene, e come valore ss.
Nella zona del Microsoft Query andiamo ad inserire i campi che vogliamo visualizzare nella zona riservata ai dati.
La parte dei criteri dovrebbe essere simile a questa:
Questo comando va a ricercare tra i vari nominativi della tabella Agenti, tutti quelli in cui è presente una doppia esse.
Il risultato:
Nel prossimo esempio, utilizzando l’operatore “è nullo” andremo a scovare il nome degli Agenti che non dispongono di nessun subagente.
q La finestra del Microsoft Query deve essere impostata insrendo nei criteri il Nominativo della tabella Agenti, ed il nome subagente dalla tabella Produttori.
q Volendo il campo valore può essere ciccato 2 volte, per poter far comparire la finestra di dialogo per la modifica dei criteri.
q Selezionare quindi la voce è Null e premere OK
Nella schermata che comparirà, una volta lanciata la query, potremo osservare che la parte riservata alla presentazione dei campi relativi al nome del subagente è vuota.
Infatti, come criterio di ricerca è stato proprio impostato il filtro “è nullo”, ed infatti sono stati presentati solo quegli Agenti che alle loro dipendenze non contano nessun subagente.
È possibile utilizzare funzioni di calcolo per andare a effettuare comparazioni , some o conteggi.
q Ad esempio, per sapere a quanto ammonta il totale delle provvigioni pagate a tutti i subagenti, sarà sufficiente trascinare nella zona riservata ai dati il campo provvigioni della tabella produttori
q Cliccarla 2 volte per far apparire la finestra di dialogo sottostante
q Scegliere nel totale la voce Somma, e se si desidera modificare l’intestazione della colonna, quindi premere OK
Lanciando la query ecco il risultato.
q Oppure, per sapere quanti sono in totale i vari subagenti che operano nella zona Nord, occorrerà trascinare il campo nome subagente e zona dalla tabella Produttori nella zona riservata ai dati, oppure effettuare l’operazione in uno degli altri modi visti.
q Nel campo riservato ai criteri occorrerà trascinare il campo Zona dalla tabella Produttori ed impostare il valore un uno dei modi visti a Nord
q Cliccare 2 volte sulla voce nome subagente nella zona riservata ai dati, per far comparire la maschera per l’impostazione del conteggio, ed impostare il totale su Conteggio, quindi premere OK
Lanciando la Query, ecco il risultato che si ottiene.
Sfruttando una proprietà delle query parametriche, è possibile passare un valore dall’esterno come criterio di ricerca della query.
Nella voce Campo criteri, scriviamo tra parentesi quadre il testo della domanda che il programma ci farà. La risposta non sarà altro che il valore utilizzato per la ricerca.
Sfruttando l’esempio di prima, al posto di Nord scriviamo [ Inserire la zona di appartenenza]
Cancelliamo dalla zona riservata ai dati la colonna riservata al nome del subagente.
Andiamo a cliccare 2 volte sulla voce zona, nella parte riservata ai criteri, e potremo modificare le impostazioni affinché vadano ad effettuare un conteggio, quindi premiamo OK
Lanciamo la query ed osserviamo cosa avviene.
Compare un casella di input, recante la domanda che avevamo impostato tra le parentesi quadre.
Scrivendo nel campo bianco , ad esempio Est, avremo il totale dei subagenti che operano in quella zona; e se poi decidiamo di avere un nuovo risultato, non sarà necessario entrare nella struttura della query, ma lanciarla semplicemente e scrivere il nuovo valore nella casella di input.
Ed ecco il risultato
Ovviamente questa casella può essere utilizzata indipendentemente dalla funzione di conteggio.
Ad esempio, per avere subito tutti i dati di un determinato agente e dei suoi subagenti, basterà impostare nei criteri il valore del campo Nominativo della tabella Agenti con questo tipo di parametro.
Nell’immagine è raffigurato l’esempio, con le impostazioni necessarie
Inserendo il nome dell’Agente Anna Blussi, ecco cosa si ottiene
Mentre, se si inserisce il nome di un agente privo di collaboratori,ad esempio Marco Ginetti ecco il risultato.
Naturalmente, tutte le informazioni reperite possono essere restituite ad Excel, con la procedura ormai nota.
La Pratica
Esercizio 1 : Creazione database e connessione
; impostazione Microsoft Query
Creare un database in Excel per analizzare le caratteristiche delle vetture. I dati sono stati inventati. Mi scuso con gli appassionati per gli errori che ci saranno.
q Tabella Casa, in cui sono memorizzate le Case Automobilistiche
q Tabella Modelli, comprendente i modelli di vetture
q Salvare il file come database_auto.xls e chiuderlo
q Apriamo un foglio di Excel e con la nota procedura andiamo a connetterci con l’archivio appena creato.
q Uscire dalla Creazione guidata Query e passare al Microsoft Query
q Aggiungere le tabelle Casa e Modelli
q Per poter specificare che tipo di relazione operi, occorre pensare a che tipo di relazione vi sia tra la tabella Casa e quella dei Modelli
q La relazione è Uno a molti. Impostarla come nell’immagine sottostante.
q Per poter definire il tipo di relazione , dobbiamo cercare di interpretare cosa il programma ci scrive. In effetti sembra corretto il punto 2 ove si prendono tutti i valori dalla Tabella Casa$ e solo i record della tabella Modelli$ in cui il campo codice sia uguale.
q Premiamo il tasto Aggiungi, quindi Chiudi.
Esercizio 2: Visualizzazione dei modelli delle
auto Fiat
q Aprire un nuovo foglio di calcolo
q Impostare Campo Criteri : Marca
q Valore : Fiat
q In Visualizzazione Dati : Marca , Modello, Cilindrata
q Lanciare la Query ed esportare in Excel
q Salvare come q1.xls
Esercizio 3: Visualizzazione dei modelli delle
auto Fiat con motore a turbina
q Aprire un nuovo foglio di calcolo
q Ripetere la connessione al database
q Impostare Campo criteri : Marca, Turbina
q Valore : Marca=Fiat, Turbina=SI
q In Visualizzazione Dati : Marca , Modello, Cilindrata
q Lanciare la Query ed esportare in Excel
q Salvare come q2.xls
Esercizio 4: Visualizzazione dei modelli delle
auto non più in produzione
q Aprire un nuovo foglio di calcolo
q Ripetere la connessione al database
q Impostare Campo Criteri : In produzione
q Valore : In produzione = è nullo , dal momento che nel database le auto non in produzione non hanno nessun valore nella cella relativa.
q In Visualizzazione Dati : Marca , Modello
q Lanciare la Query ed esportarla in Excel
q Salvare come q3.xls
Esercizio 5: Visualizzazione di tutti modelli
con motore a turbina ed alimentazione Diesel , con il relativo costo
q Aprire un nuovo foglio di calcolo
q Ripetere la connessione al database
q Impostare Campo Criteri : Alimentazione,Turbina
q Valore : Alimentazione=Diesel , Turbina= SI
q In Visualizzazione Dati : Marca , Modello,Costo
q Lanciare la Query ed esportare in Excel
q Salvare come q4.xls
Ora alcune Query da fare in base alla richiesta, ma senza suggerimenti.
Esercizio 6: Marche e modelli con più di 80
cavalli
Esercizio 7: Tutte
le Audi e le Mercedes con costo inferiore ai 25.000 euro
Esercizio 8: Tutte
le auto con cilindrata > di 1800 cc
e potenza maggiore di 120 cv
Esercizio 9: Le
auto più economiche ( con un piccolo suggerimento )
Esercizio 10: Le
auto più care
Non è obbligatorio inviare gli esercizi al tutor.
Feedback
Per
chiarimenti, si prega di lasciare la domanda direttamente sul Forum della
piattaforma Maestra.