Cum creati interogari?

Cum creati interogari?

Intrerogarea (query) este un instrument pe care il puteti folosi pentru a obtine anumite informatii de sinteza dintr-o baza de date. Pentru a obtine astfel de informatii, veti construi interogari in care veti folosi campuri din unul sau mai multe tabele, punand si anumite conditii, sau restrictii. Cand veti lansa o astfel de interogare, veti obtine o lista in modul de vizualizare Datasheet, care va contine numai inregistrarile conforme cu restrictiile din interogarea respectiva.

Puteti construi interogari simple, pe baza informatiilor dintr-un singur tabel. De exemplu, puteti crea o interogare pentru a vizualiza o lista cu inregistrari din tabelul Clienti, dar care sa contina numai clientii dintr-o singura localitate. De asemenea, puteti construi o interogare in care sa folositi informatii din doua sau mai multe tabele. De exemplu, puteti crea o interogare pentru a vizualiza lista clientilor care au facut comenzi mai mari decat o anumita valoare, sau alta interogare pentru a vizualiza lista clientilor care nu au facut comenzi in ultimele luni. In acest caz aveti nevoie de informatii din tabelele Comenzi si Clienti, dar si de pretul unitar al produselor comandate, pe care il preluati din tabelul Produse.

1. Cum formulati o intrebare simpla?

Cand doriti sa obtineti informatii dintr-un singur tabel, nu este necesar sa construiti o interogare (query) in adevaratul sens al cuvantului. In acest caz puteti obtine informatiile dorite daca formulati o intrebare intr-o forma mai simpla cu ajutorul comenzii Advanced Filter/Sort . din submeniul Filter al meniului Records. Sa presupunem ca doriti sa obtineti o lista care sa contina numai o parte din inregistrarile tabelului Clienti, de exemplu numai clientii din localitatea Cluj-Napoca. Pentru a obtine o astfel de lista, trebuie sa faceti urmatoarele operatii:

Deschideti tabelul Clienti in modul de vizualizare Datasheet.

Apelati comanda Records | Filter | Advanced Filter/Sort . . Pe ecran apare o fereastra Filter pentru tabelul Clienti, care are in partea superioara o macheta a tabelului Clienti, iar in partea inferioara un tabel care se numeste grila (grid), sau caroiaj, in care puteti construi eventuale conditii, sau criterii de filtrare pentru inregistrarile care vor fi vizualizate. Puteti introduce unul sau mai multe criterii de filtrare, legate intre ele prin operatori logici "and", sau "or". Fiecare criteriu este de fapt o comparare, avand in componenta urmatoarele elemente:

Campul folosit pentru comparare;

Operatorul de comparare;

Valoarea cu care se compara campul.

Introduceti campul "Localitate" (primul element din criteriul de comparare) in prima celula din linia "Field", folosind una din urmatoarele metode:

Actionati dublu-clic pe campul "Localitate" din macheta tabelului Clienti, sau actionati un clic pe campul "Localitate" si trageti-l in celula din linia "Field".

Actionati un clic pe celula din linia "Field" si observati ca celula respectiva se transforma intr-o lista ascunsa care contine campurile tabelului Clienti. Desfasurati aceasta lista si alegeti campul "Localitate".

Introduceti in prima celula din linia "Criteria" urmatoarele doua elemente (operatorul si valoarea de comparare) din criteriul de comparare. Pentru a lista numai clientii din Cluj-Napoca, introduceti "= Cluj-Napoca". Operatorul "=" fiind implict, puteti omite acest operator si sa introduceti pur si simplu "Cluj-Napoca".

Pentru a vizualiza rezultatele obtinute prin aceasta filtrare, apelati comanda Records | Apply Filter/Sort, sau actionati butonul Apply Filter din linia cu instrumente Table Datasheet. Pe ecran va aparea in modul de vizualizare Datasheet o lista a clientilor din Cluj-Napoca, asa cum se vede in figura urmatoare.

Pentru a elimina acest filtru, revenind la vizualizarea intregului tabel Clienti, apelati comanda Records | Remove Filter/Sort, sau actionati butonul Remove Filter din linia cu instrumente Table Datasheet.

Observatie

Operatorii pe care ii introduceti in criteriile de comparare sunt operatori de egalitate sau inegalitate. De exemplu, pentru a obtine o lista complementara, adica a clientilor care nu sunt din Cluj-Napoca, introduceti criteriul "<> Cluj-Napoca". Daca doriti ajutor in construirea unor expresii mai complicate, puteti lansa Expression Builder. Pentru aceasta actionati butonul secundar al mouse-ului pe linia "Criteria" si alegeti Build. Pe ecran apare fereastra Expression Builder, care va ajuta sa alegeti operatori si sa construiti conditii, in loc sa le tastati.

2. Cum creati o interogare simpla?

Puteti crea o interogare (query) cu informatii din tabele si din alte interogari. De exemplu, puteti crea o interogare cu informatii din tabelele Comenzi, Clienti si Produse din baza de date Farmacie.

Pentru a crea o interogare cu ajutorul expertului (wizard) pentru interogari, procedati in felul urmator :

Deschideti baza de date Farmacie, sau daca este deschisa, activati fereastra bazei de date (de exemplu, apasati tasta F11).

In fereastra bazei de date actionati butonul Queries pentru a aparea instrumentele de creare a interogarilor. In fereastra exista doua posibilitati de creare a unei interogari, pe care le puteti lansa imediat cu dublu-clic:

Create query in Design view - care permite crearea unei interogari in modul de vizualizare Design.

Create query by using wizard - care permite crearea unei interogari cu ajutorul unui asistent expert (wizard).

Daca actionati butonul traditional New, apare o fereastra de dialog cu titlul New Query in care regasiti aceste optiuni, impreuna cu altele.

Actionati dublu clic pe optiunea Create query by using wizard, pentru a crea interogarea cu ajutorul unui asistent expert (wizard) pentru interogari. Pe ecran apare imediat fereastra Simple Query Wizard, care contine lista ascunsa Tables/Queries, de unde puteti alege tabelele si interogarile care vor fi sursa de informatii pentru interogarea pe care o creati.

In fereastra de dialog Simple Query Wizard, alegeti din lista acunsa Tables/Queries un tabel sau o interogare, care sa stea la baza construirii interogarii. De exemplu, in figura de mai sus s-a ales tabelul "Comenzi", care contine informatii despre comenzile primite de la clienti pentru diferite produse.

Transferati din lista Available Fields in lista Selected Fields toate campurile pe care le folositi in interogare, cu ajutorul butoanelor de transfer ">" (move), sau ">>" (move all). Pentru a renunta la anumite campuri, folositi butoanele "<" (back), sau "<<" (back all).

Pentru a crea o interogare cu campuri din mai multe tabele sau interogari,  reveniti la pasul 4 pentru a face o noua selectie in lista ascunsa Tables/Queries, apoi continuati cu pasul 5 pentru a alege alte campuri de care aveti nevoie. De exemplu, puteti sa selectati din tabelul Clienti adresele clientilor care au facut comenzi, iar din tabelul Produse pretul unitar al produselor comandate. Dupa ce ati selectat toate aceste campuri din cele trei tabele, proiectul interogarii arata ca in figura urmatoare in modul de vizualizare Design. Pe linia "Show" sunt marcate toate campurile care au fost introduse, ceea ce inseamna ca toate vor fi vizualizate. Daca ati introdus unele campuri numai pentru a le folosi in anumite conditii si nu trebuie sa fie vizualizate, actionati un clic pe comutatoarele corespunzatoare pentru a sterge marcajele.

Actionati butonul Next> pentru a trece la pasul urmator, in care puteti alege intre o interogare detaliata (Detail), care listeaza toate inregistrarile care indeplinesc conditiile specificate, si o interogare rezumativa, sau sumara (Summary), care prezinta anumite informatii de sinteza din inregistrarile conforme cu toate conditiile specificate. Alegeti Detail, pentru a face o interogare detaliata.

Actionati butonul Next> pentru a trece la ultimul pas din Simple Query Wizard, in care vi se cere sa dati un nume interogarii. Puteti accepta numele implicit "Comenzi Query" pe care vi-l propune Excel si care apare automat in campul respectiv.

Actionati butonul Finish pentru a incheia procesul de creare a interogarii. Daca la ultimul pas a ramas activa optiunea Open the query to view information, pe ecran apare imediat un tabel care are ca titlu numele interogarii (Comenzi Query in cazul nostru) si care contine informatiile alese pentru interogare, in modul de vizualizare Datasheet.

Dupa cum vedeti in figura de mai sus, in tabel sunt incluse toate campurile pe care le-ati ales pentru interogare, deoarece toate au ramas marcate in linia "Show" din proiect

3. Cum vizualizati rezultatele interogarii?

Cand proiectati o interogare, aveti nevoie de multe ori sa va comutati intre modul de vizualizare Design in care proiectati interogarea, si modul de vizualizare Datasheet in care puteti vedea rezultatele interogarii. Exista mai multe modalitati prin care va puteti comuta dintr-un mod de vizualizare in altul. In meniul View, puteti alege comanda Design View pentru a trece in modul de vizualizare Design, sau comanda Datasheet View pentru a trece in modul de vizualizare Datasheet. Aceleasi facilitati de vizualizare le intalniti si in lista ascunsa a butonului View din linia cu instrumente Query Design. Desfasurati lista ascunsa a butonului View si comutati-va pe modul de vizualizare pe care il doriti. De asemenea, daca sunteti in modul de vizualizare Design, puteti trece in modul de vizualizare Datasheet prin actionarea butonului Run din linia cu instrumente Query Design. Cand treceti in modul de vizualizare Datasheet, se lanseaza imediat interogarea si se vizualizeaza rezultatele conform cu definitia curenta a interogarii.

4. Cum creati Interogari "Top Values"?

Sunt situatii in care este necesar sa vedeti valorile cele mai mici, sau cele mai mari pe care le are un camp. De exemplu, cand analizati tabelul Comenzi, va intereseaza ca suma cantitatilor comandate pentru un anumit produs sa nu fie mai mare decat stocul din depozit, care este inregistrat in tabelul Produse. Pentru a putea face aceasta verificare, construiti o interogare in felul urmator:

Lansati asistentul (wizard) pentru a aparea fereastra de dialog asociata Simple Query Wizard.

Folosind lista ascunsa Tables/Queries, introduceti in interogare toate campurile din tabelul Comenzi.

Din tabelul Produse, introduceti campul "Stoc".

Pe linia "Sort"activati celula corespunzatoare campului "Stoc" si alegeti Ascending. Proiectul interogarii va arata ca in figura de mai jos.

Daca vreti sa limitati listarea la cateva inregistrari din varful listei, desfasurati lista Top Values din linia cu instrumente Query Design, asa cum se vede in figura de mai jos. Puteti alege un numar de inregistrari, sau un procent din totalul inregistrarilor, care sa fie listate. Daca vreti sa precizati un numar care nu este in lista, introduceti cursorul de editare si tastati numarul pe care il vreti. In mod implicit este activa optiunea "All", ceea ce inseamna ca vor fi listate toate inregistrarile.

Treceti in modul de vizualizare Datasheet pentru a vedea rezultatele interogarii. Intrucat pe linia "Sort" ati ales optiunea Ascending pentru campul "Stoc", inregistrarile vor fi listate in ordinea crescatoare a stocurilor produselor, astfel ca vor putea fi vazute stocurile mai mici la inceputul listei.

Observatie

Daca vreti sa vedeti la inceputul listei valorile cele mai mari ale unui camp, faceti o interogare in care alegeti tipul de sortare Descending pentru acel camp. De exemplu, puteti face acest lucru daca va intereseaza clientii care au facut comenzi in valoare mai mare, sau produsele care s‑au vandut mai bine.

5. Cum creati interogari de sinteza?

Uneori aveti nevoie ca rezultatele unei interogari sa fie informatii de sinteza. De exemplu, daca vreti sa aflati cantitatile totale comandate pentru fiecare produs, trebuie sa creati o interogare care sa aiba o linie de "Total" in caroiajul din partea de jos. Pentru a adauga linia de "Total", trebuie sa lucrati cu o interogare standard (de tip Select), care sa fie deschisa in modul de vizualizare Design.

Pentru a face o interogare care sa afiseze suma cantitatilor comandate pentru fiecare produs, procedati in felul urmator:

Lansati asistentul (wizard) pentru a aparea fereastra de dialog asociata Simple Query Wizard.

Pentru a adauga linia de "Total", apelati comanda View | Totals, sau actionati butonul Totals din linia cu instrumente Query Design.

Introduceti in interogare campurile "Produs" si "Cantitate" din tabelul Comenzi. Campul "Produs" ramane cu optiunea Group By in linia "Total", pentru a grupa inregistrarile ale caror cantitati trebuie insumate.

Activati in linia "Total" celula corespunzatoare coloanei "Cantitate", apoi desfasurati lista ascunsa care apare si alegeti functia Sum, pentru a insuma cantitatile.

Treceti in modul de vizualizare Datasheet pentru a vedea rezultatul interogarii. Modul cel mai simplu in care puteti face acest lucru este sa activati butonul Run din linia cu instrumente Query Design, pentru a lansa executia interogarii. In tabel apar sumele cantitatilor comandate pentru fiecare produs.

In afara de functia Sum, la pasul 4 puteti alege una din functiile statistice, care se mai numesc functii de agregare, pe care le cunoasteti de la Excel. Functiile de agregare pe care le puteti folosi pentru a opera asupra grupelor de inregistrari sunt urmatoarele:

Sum - calculeaza suma unor valori numerice din grup.

Avg - media aritmetica (AVeraGe) a unor valori numerice din grup.

Min - cea mai mica valoare (MINim) numerica din grup.

Max - cea mai mare valoare (MAXim) numerica din grup.

Count - determina numarul valorilor din grup.

Var - calculeaza dispersia (VARiance) valorilor din grup.

StDev - calculeaza abaterea standard (STandard DEViation) a valorilor.

First - listeaza prima valoare din grup.

Last - listeaza ultima valoare din grup.

6. Cum calculati valori noi?

Uneori aveti nevoie sa faceti anumite calcule suplimentare in cadrul fiecarei inregistrari pentru a obtine informatiile pe care le doriti. De exemplu, pentru a vedea ce valoare trebuie sa incasati pentru un produs comandat, trebuie sa inmultiti cantitatea comandata, care se afla in tabelul Comenzi, cu pretul unitar al produsului, care se ia din tabelul Produse. Pentru a include rezultatul acestui produs intr-o interogare, trebuie sa creati un camp special in proiectul interogarii, pe care il puteti denumi "Valoare". Acest camp nu este un camp obisnuit din tabel si se numeste camp calculat, deoarece efectueaza niste calcule.

Pentru a crea o interogare care sa listeze valorile pe care trebuie sa le incasati pe cantitatile specificate in comenzi, procedati astfel

Lansati asistentul expert (wizard) pentru a aparea fereastra de dialog asociata Simple Query Wizard.

Introduceti in interogare campurile de care aveti nevoie, printre care sa fie "Cantitate" din tabelul Comenzi si "Pret_Unitar" din tabelul Produse, deoarece campul calculat "Valoare" se obtine prin inmultirea valorilor din cele doua campuri.

Pentru a crea campul calculat, efectuati un clic in linia "Field", in prima coloana libera si introduceti expresia care sa calculeze valoarea. Nu este nevoie sa tastati numele campurilor, deoarece le puteti alege dupa ce desfasurati lista ascunsa. De asemenea, daca actionati butonul secundar al mouse-ului, puteti lansa din meniul local comanda Build, pentru a aparea fereastra de dialog Expression Builder, care va ajuta la construirea expresiei. Puteti alege numele campurilor si operatorii pentru a construi expresia. Numele implicit al campului calculat este "Exp1", dar puteti schimba acest nume cu unul semnificativ, de exemplu "Valoare", asa cum se vede in figura de mai jos.

Dupa ce ati construit expresia, inchideti fereastra de dialog Expression Builder cu OK, pentru a crea campul calculat "Valoare". Excel va propune pentru aceasta interogare un nume generic, de exemplu "Comenzi Query1", dar puteti schimba acest nume cu unul semnificativ pentru continutul interogarii, de exemplu "Comenzi Medicamente". Proiectul interogarii arata ca in figura de mai jos. In coloana "Valoare" apare in paranteza formula dupa care se calculeaza valoarea. Daca vreti sa vedeti integral aceasta formula, mariti coloana respectiva.

Treceti in modul de vizualizare Datasheet pentre a vedea rezultatul interogarii (de exemplu, actionati butonul Run din linia cu instrumente). Este afisat un tabel care contine toate campurile pe care le-ati inclus in proiectul interogarii.