Descrizione

Le relazioni e le query in un database di Excel.

Obiettivi della lezione

Saper creare delle relazioni e le query in un database di Excel.

Indice lezione

Lezione 17  -  Foglio Elettronico

17.1          Le Relazioni;

17.2          Le Query.

Dati sulla lezione

Versione:

v1.0

Tempi:

1 ½ ora / 90 minuti

Data svolgimento lezione:

16 Aprile 2003

Autore dei contenuti:

Vastapane Marco Attilio – Giuliano Luca

Autore materiali didattici:

Togni Francesca

 

La Teoria    

17.1  -  Le Relazioni

q     Apriamo un foglio di Excel e con la nota procedura andiamo a connetterci con l’archivio database_excel.xls. già in nostro possesso.

 

q     Dopo aver selezionato l’archivio premiamo OK

q     Aggiungiamo le Tabelle Agenti e Produttori alla finestra di destra. Compariranno i rispettivi campi

 

Premendo Avanti , comparirà il messaggio che ci avvisa che è impossibile unire le tabelle nelle Query. È necessario andare ad unire manualmente le varie tabelle all’interno del Microsoft Query, andando a trascinare i campi da unire tra le tabelle.

Infatti, utilizzando il  Microsoft Query, possiamo creare le necessarie relazioni che permetteranno alle tabelle di legarsi tra di loro.

 

Andando ad eseguire il Microsoft Query, comparirà una schermata  in cui sono presenti le tabelle Agenti$ e Produttori$ e nella finestra riservata ai dati saranno presenti un numero enorme di record, assolutamente privi di senso, poiché il programma cerca di mettere in relazione come può le due entità presenti.

A differenza di quanto era avvenuto in precedenza, quando i file del database erano stati preparati, mettendo in relazione tra di loro le tabelle nel modo corretto in ambiente Access, qui deve essere ancora fatta questa parte del lavoro.

Per creare una relazione tra le tabelle Agenti$ e Produttori$, occorre selezionare il campo Codice della tabella Agenti$, comune ad entrambe,e trascinarlo sul campo codice della tabella Produttori$.

Non appena effettuata questa operazione sarà necessario stabilire che tipo di relazione lega le due tabelle. Notiamo che ora le 2 tabelle sono unite da un filo.

Per poter specificare che tipo di relazione operi, occorre pensare a che tipo di relazione vi sia tra la tabella degli Agenti e quella dei Produttori.

Le relazioni esistenti tra le tabelle possono essere di 3 tipi:

q     Uno a Uno: Con questa relazione, ogni record della prima tabella fa riferimento ad un solo record della seconda tabella; questo tipo di relazione è poco utilizzata; infatti converrebbe unire le due tabelle in una unica; ad esempio il nome del fornitore ed il suo codice fiscale.

q     Uno a molti: creando una tabella con il nome dei dipendenti ed i loro codici identificativi, è possibile relazionare questa tabella con un'altra chiamata città di residenza. Questa relazione è detta uno a molti, perché un dipendente può vivere in una sola città nella quale invece possono vivere più dipendenti.

q     Molti a molti: avendo una tabella con il nome clienti, contenente i nomi e i loro codici identificativi, è possibile mettere in relazione questa tabella con un'altra chiamata prodotti venduti (contenente tutti i prodotti che si vendono ai clienti). Questa relazione è chiamata molti a molti perché un cliente può acquistare molti prodotti e un prodotto può essere acquistato da più clienti.

 

Nel nostro esempio, un Agente può avere alle sue dipendenze uno , nessuno o molti Subagenti, mentre ogni singolo Subagente avrà rapporti di lavoro con un solo Agente.

Quindi il tipo di relazione che dobbiamo andare a creare è sicuramente di tipo uno a molti.

q     Clicchiamo 2 volte in rapida successione sul filo che ora unisce le due tabelle.

q     Apparirà la finestra Join

 

q     In essa possiamo facilmente vedere come i campi che sono stati uniti nella relazione  siano il codice della tabella Agenti$  con quello della tabella Produttori$

 

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 Agenti$ e solo i record della tabella Produttori$ in cui il campo codice sia uguale.

 

q     Premiamo il tasto Aggiungi, quindi Chiudi

 

q     Come possiamo notare nella figura sottostante, ora la relazione tra le tabelle presenta una freccia che parte dalla tabella Agenti$ per andare a colpire la tabella Produttori$. È stata definita una relazione uno a molti.

 

 

17.2  -  Le  Query

Ora, puliamo il Microsoft Query dai dati inutili, ed aggiungiamo, se non fosse presente la zona dedicata ai criteri.

 

q     Selezioniamo le varie voci del database con il mouse in modo da annerirle

 

q     Premiamo CANC da tastiera.

 

La finestra che dovremmo avere è quella rappresentata nella figura sottostante.

 

 

Ora che le relazioni sono state create, siamo pronti per sperimentare la funzionalità degli operatori logici. Per il momento portiamo la nostra attenzione solo sulla tabella Agenti$. Nelle lezioni che seguiranno ci cimenteremo on Query tra più tabelle.

Anche in questo caso, si desidera selezionare dalla tabella  Agenti$ tutti quelli che appartengono alla zona Nord ed ( OR ) alla zona Sud.

q     Selezioniamo i nomi dei campi che ci interessano nell’area riservata ai Dati

 

q     Tra tutti i campi disponibili servirà solamente quello identificativo della Zona per effettuare l’operazione. Trascinare il campo Zona nel Campo criteri.

q     Come valore impostare Nord

q     Nella riga sottostante scriviamo Sud

 

 

Lanciamo la nostra Query, ed osserviamo un curioso comportamento.

 

 

Effettivamente il risultato che viene restituito corrisponde al vero. Ci sono tutti gli Agenti che operano nella zona Nord e Sud, come era stato richiesto.

Però ci sono delle ripetizioni di dati.

È assolutamente giusto che queste ripetizioni ci siano, poiché la tabella è collegata con una relazione UNO a MOLTI alla tabella Produttori$, quindi dal momento che nessuno ha ancora comunicato al programma che cosa si desidera prelevare dalla tabella Produttori, la schermata che ci offre è già pronta per la corretta visualizzazione di eventuali dati provenienti dall’altra tabella.

Per poter eliminare le voci ripetute, occorre dal menu Visualizzaè proprietà Query scegliere la voce Solo valori univoci e premere OK

 

Lanciando la query si otterrà un risultato più famigliare.

 

Restituendo ad Excel il risultato, dopo averli ordinati in modo crescente riguardo alle provvigioni, ecco cosa si ottiene

q     Salviamo questo file sul nostro PC.

Se ora desiderassimo modificare la query in funzione di ottenere un differente risultato, non sarebbe possibile ovviamente apportare le dovute variazioni  con la creazione Guidata. Premendo il tasto OK si avvierà automaticamente il Microsoft Query

 

Nella schermata che apparirà, saranno ovviamente presenti i medesimi valori inseriti durante la costruzione della Query.

 

L’operatore logico OR può essere utilizzato anche in un secondo modo, e questo ci introdurrà all’utilizzo dell’operatore AND.

 

Andando a modificare la Query,  è possibile utilizzare l’operatore OR andando a scrivere direttamente nell’area riservata ai criteri, il secondo valore di ricerca, nella stessa cella in cui si è scritto  il primo valore, separati dalla stringa OR  o OPPURE.

Nell’esempio, vogliamo ricercare i tutti gli Agenti che siano assegnati alle Zone Est ed anche alle Ovest.

 

 

Restituendo i dati ad Excel, ecco il risultato.

 

Ora, andando ad utilizzare l’operatore logico AND, abbiamo la possibilità di trovare solamente gli Agenti che percepiscono provvigioni superiori a 300 euro e tutti quelli che ne percepiscono per un importo inferiore a 700.

Il risultato che verrà restituito terrà conto dei 2 parametri di ricerca che dovranno essere entrambi rispettati.

q     Scriviamo nella zona dei criteri, riguardo alle Provvigioni >300 e < 700

 

Come risultato otterremo esclusivamente  l’intervallo di dati che avevamo prestabilito.

 

 

Se ora desiderassimo filtrare ulteriormente i dati in base all’identificativo della zona, ad esempio per quelle Nord e Sud, dovremo utilizzare l’operatore OR, poiché nessun Agente è simultaneamente in 2 zone.

La precisione di questo strumento è estremamente elevata. Infatti, occorre sempre specificare con assoluta chiarezza quale risultato si desidera ottenere.

Infatti, se vogliamo ottenere come risultato i dati degli Agenti che limitatamente alle zone Nord e Sud  hanno provvigioni maggiori di 300 euro e minori di 700, andando a scrivere questo criterio nella zona dei Criteri, otteniamo un risultato interessante:

 

 

Spostiamo la nostra attenzione sulla prima riga del riepilogo: Gianno Bianchi, appartenente alla zona Sud è presente nell’elenco, benché abbia una provvigione di soli 200 euro, e quindi NON COMPRESA tra i criteri di ricerca.

Come è potuto accadere?

La risposta è scontata: i programmi, e per estensione i computer sono macchine stupide, che si limitano a svolgere i compiti loro assegnati, come del resto fa qualsiasi tipo di meccanismo costruito dall’uomo.

In questo caso noi abbiamo detto al programma di ricercare “ gli Agenti delle zone Nord e Sud con la restrizione che per i soli  Agenti della zona Nord la Provvigione doveva essere  >300 e < 700 . Quindi il risultato è esatto.

Ovviamente, se andiamo a forzare lo stesso criterio di ricerca anche per il Sud, sarà necessario inserire in corrispondenza del criterio Sud , la stessa limitazione; è tuttavia possibile differenziare tra di loro i criteri di ricerca.

 

 

 

Ecco che in questo caso, Gianni Bianchi non risulta più essere in elenco. Restituiamo il risultato ad Excel, in modo da poter, se necessario modificare la Query in seguito; quindi salviamolo come database esercizi.xls

Feedback

Per chiarimenti, si prega di lasciare la domanda direttamente sul Forum della piattaforma Maestra.