1

INTEROGAREA Interogarea const n extragerea datelor dintr-o tabel (tabele), dintr-o interogare anterioar sau din ambele,

prelucrarea acestora ntr-o form mai mult sau mai puin complex i furnizarea informaiilor ctre utilizatori. Rezultatele interogrilor pot fi folosite ca atare sau pot constitui surs de nregistrri pentru crearea formularelor i

rapoartelor. Principalele operaii care se pot realiza cu ajutorul interogrilor sunt: extragerea din tabele numai a cmpurilor relevante pentru utilizatori;extragerea nregistrrilor din tabele prin

specificarea unor criterii de selecie;regsirea i ordonarea datelor dup anumite criterii; crearea de cmpuri calculate; extragerea datelor din una sau mai multe tabele i realizarea unor informaii sintetice; crearea de tabele, adugarea nregistrrilor n tabele, tergerea nregistrrilor din tabele i actualizarea datelor; combinarea i compararea ieirilor prin realizarea mai multor interogri n acelai timp; interogarea i a altor baze de date (FoxPro, SQL Server); pregtirea datelor n vederea afirii lor n formulare sau rapoarte.

n Microsoft Access se pot crea urmtoarele tipuri de interogri: interogri de selecie; interogri parametrice interogri ncruciate; interogri de aciune.

Interogrile de selecie extrag informaii din unul sau mai multe tabele i le afieaz sub form de list. Sunt cel mai uor de creat i au avantajul c pot afia un numr redus de date dintr-un tabel de mare capacitate (datele care ndeplinesc condiiile specificate). Ele permit i modificarea rezultatului afiat, modificare ce va fi vzut i n tabelul surs. De asemenea, permit i folosirea de parametri, cum este reuniunea de cmpuri din tabele ntre care nu exist nici o legtur precum i efectuarea de calcule.

Interogrile parametrice nu sunt un tip special de interogri, o funcie parametru putnd fi folosit pentru toate celelalte interogri prezentate mai sus; ele folosesc n mod repetat o interogare, efectund modificri n criteriile de selecie. Interogrile tip tabel ncruciat centralizeaz n formatul unei foi de calcul tabelar datele din unul sau mai multe tabele. Datele rezultate dup execuia unei astfel de interogri sunt prezentate ntr-un format potrivit pentru analiza datelor i crearea de grafice. Interogrile de aciune creeaz un nou tabel n baza de date sau realizeaz modificri majore ale unui tabel existent. n general, toate interogrile de aciune pot fi realizate pe baza unei interogri de selecie. Ele permit adugarea, modificarea sau tergerea de nregistrri ntr-un tabel. Exist patru tipuri de interogri de aciune: interogri de generare a unui nou tabel din datele coninute n setul de rezultate al interogrii; interogri de adugare a noi nregistrri ntr-un tabel; interogri de tergere a unor nregistrri dintr-un tabel; interogri de actualizare a unor nregistrri dintr-un tabel, conform cu o condiie ce trebuie ndeplinit.

Utilizarea operatorilor

Pentru a construi expresii pe rndul Criteria se utilizeaz operatorii: aritmetici:

adunare (+); scdere (), nmulire (*), mprire (/), ridicare la putere (^), mprirea a dou numere cu returnarea unui ntreg (\), mprirea a dou numere cu returnarea restului mpririi (MOD).

de comparaie: , =, =. Aceti operatori returneaz valorile logice True i False. Excepie reprezint cazul n care unul dintre operatori are valoarea NULL i deci orice comparare va returna valoarea NULL.

asociai operatorilor de comparare: IS NULL, IS NOT NULL o valoare NULL (cmp necompletat) nu este nici TRUE nici FALSE. nregistrrile care au valoarea NULL n cmpurile selectate nu apar ca rezultate ale interogrii; LIKE se folosete mpreun cu caracterele de nlocuire * i ? pentru a stabili dac o valoare ncepe cu unul sau mai multe caractere; caracterul * poate nlocui orice numr de caractere; caracterul ? nlocuiete numai un caracter; IN stabilete dac o valoare este cuprins ntr-o list; BETWEEN stabilete dac o valoare aparine unui interval specificat.

logici: NOT negaia; AND pentru conjuncia a dou valori; OR pentru disjuncia a dou valori; XOR pentru disjuncia exclusiv a dou valori; Eqv verific echivalena a dou valori.

de concatenare a irurilor de caractere: + i &.

2

de identificare: ! i . . Aceste dou caractere sunt utilizate ca separatori, astfel: Combin numele coleciilor de obiecte i numele obiectelor pentru a selecta un anumit obiect sau proprietate a lui: Forms! [Clieni] Identific atribute aparinnd unei tabele: Clieni! [Localitate] Deosebete numele obiectelor de cele ale proprietilor: TextBox1.FontSize=12 unde: TextBox reprezint un obiect de control iar FontSize reprezint o proprietate (stabilete dimensiunea fontului)

constante: Constantele utilizate n construirea expresiilor Access pot fi de natur numeric (ex: 1200,5,0); text (123, Toma Ion, str. Viilor 15); dat calendaristic (ex: #12.31.01# ceea ce indic data de 31 decembrie 2001).

Funciile: pot fi de natur:

Dat calendaristic : Date(), Month(), Year (), etc. Exemple: Date() returneaz data curent; Month(Date()) returneaz numrul lunii calendaristice curente. Year(Date()) returneaz anul curent.

De tip text: Len() returneaz lungimea unui ir; Trim() elimin spaiile de la nceputul i de la sfritul unui ir; Left() returneaz primele n caractere de la nceputul unui ir, etc.

Matematice i trigonometrice: ABS() returneaz valoarea absolut a unui numr; INT() returneaz partea ntreag dintr-o valoare numeric, ROUND() rotunjete o valoare cu un anumit numr de zecimale; SUM() calculeaz suma; AVG() calculeaz media, etc.

Financiare: PV() returneaz valoarea actual a unei anuiti pltite n rate periodice egale; SLN() returneaz valoarea amortizrii unui mijloc fix dup o anumit perioad (amortizare liniar) etc.

Funcii diverse: ISNUMERIC(), ISNULL(), etc. Reguli de formare a expresiilor introduse pe cmpul Criteria: datele de tip Text se tasteaz ca atare, iar Access adaug automat ghilimele; pentru datele de tip Number i Currency se tasteaz cifrele i eventual simbolul zecimal, fr simbolul monetar sau separatorul de mii; referirile la numele de cmpuri trebuie incluse ntre paranteze drepte, altfel se adaug automat ghilimele, considerndu-se text; formatul internaional de dat calendaristic este mm/dd/yy. Access adaug automat delimitatorul # ; Access adaug automat IS la referirile care implic valoarea NULL. Pe rndul Criteria din grila de interogare se poate introduce un singur criteriu de selecie sub un cmp sau mai multe criterii sub cmpuri diferite. Dac criteriile de selecie se introduc pe un singur rnd Criteria, se extrag nregistrrile care ndeplinesc toate condiiile (operatorul logic AND), iar dac se introduc pe rnduri diferite se includ n rspuns doar nregistrrile care ndeplinesc oricare dintre criteriile menionate (operatorul logic OR).

Crearea unui obiect de tip interogare Iniierea crerii cererii se realizeaz n fereastra Database prin activarea fiei Query (Interogri) i apoi apsarea butonului New, sau selectnd din meniul Insert opiunea Query. Access ofer mai multe modaliti de creare a cererilor. Aceste modaliti sunt afiate n fereastra New Query (figura 3.3) afiat ca urmare a iniierii operaiei de creare a cererii:

3

Fereastra Select Query (figura 3.5) este mprit n dou zone: zona superioar, n care se vizualizeaz tabelele/interogrile surs de date precum i relaiile definite ntre acestea; grila Query Design structurat astfel: linia Field: rezervat pentru selectarea unui cmp; linia Table: destinat precizrii sursei de date; linia Sort: permite precizarea sensului sortrii pentru atributul din coloana respectiv; linia Show: permite inhibarea afirii realizrilor cmpului respectiv; linia Criteria: ofer posibilitatea precizrii criteriilor de selecie prin introducerea expresiilor Access corespunztoare; liniile or: permite precizarea mai multor criterii de selecie n cazul expresiilor Access utiliznd operatorul OR. Definirea interogrii de selecie presupune parcurgerea urmtorilor pai: 1. Precizarea cmpurilor ale cror realizri urmeaz s le returneze cererea. Numele acestor cmpuri se vor preciza n grila Query Design n rndul Field utiliznd una din urmtoarele modaliti: selectarea cmpului din cadrul listei Field; executarea unui dublu clik de mouse asupra cmpului dorit din tabela/interogarea aflat n panoul superior; metoda drag-and-drop care presupune selectarea cu mouse-ul a cmpului dorit din panoul superior i tractarea acestuia n linia Field. Dac este necesar s fie aduse n panoul inferior toate cmpurile aparinnd unei tabele se va proceda n unul din urmtoarele moduri: selectarea tuturor cmpurilor din tabela surs (aflat n panoul superior) printr-un dublu clik de mouse pe numele tabelei i se trag cmpurile pe gril; utilizarea asteriscului aparinnd tabelei surs: tragei cu mouse-ul asteriscul n prima coloan Field;. chiar dac n grila de proiectare este completat doar prima coloan Field la execuie interogarea va returna realizrile tuturor atributelor; utilizarea proprietii Output All Fields: se va deschide caseta Query Properties utiliznd butonul Properties din bara de instrumente sau executnd dublu click ntr-o zon liber a panoului superior; n linia Output All Fields se va preciza Yes; precizarea valorii Yes pentru proprietatea Output All Fields nu va determina aducerea n grila de proiectare a cmpurilor din tabela surs, dar, n momentul executrii cererii, vor fi cuprinse toatea realizrile tuturor atributelor. n mod implicit, antetul coloanelor tabelului rezultat n urma interogrii este reprezentat de numele cmpului, cu excepia cazului n care la crearea tabelei ai precizat o alt etichet prin intermediul proprietii Caption. Dac dorii afiarea n tabelul rezultat n urma interogrii a unei noi etichete pentru un cmp plasai mouse-ul n linia Field naintea numelui cmpului, tastai eticheta dorit urmat de caracterul :. Observaie: Dup aducerea cmpurilor din tabele sau interogri pe grila de interogare sunt

4

posibile operaii de inserare a unui cmp, tergerea unui cmp, deplasarea unui cmp ntr-o alt poziie, modificarea dimensiunilor unei coloane i modificarea numelui unui cmp. 2. Se precizeaz criteriul de selecie (n mod implicit se returneaz realizrile tuturor tuplurilor pentru cmpurile specificate) prin introducerea unei expresii Access valide n rndul Criteria (eventual i rndul OR). Introducerea expresiei Access se face prin tastare sau se construiete prin intermediul generatorului de expresii (Expression Builder) a crui fereastr se deschide selectnd opiunea Build a meniului pe care l activai printr-un click dreapta de mouse n rndul Field. 3. Se precizeaz, dac este necesar, cmpul/cmpurile dup care se dorete o eventual sortare i sensul acesteia n linia Sort. Cmpuri calculate n interogri de selecie Interogrile de selecie pot cuprinde i cmpuri calculate. Aceste cmpuri returneaz, la executarea interogrii, valoarea expresiilor Access asociate lor. Pentru a crea un cmp calculat trebuie avute n vedere urmtoarele: se introduce n celula Field a grilei de interogare un nume de coloan (dac nu se specific se atribuie numele implicit Expr l, Expr2, ...), urmat de semnul : i formula de calcul, astfel: ex.: valuta: [solda]*4.2 cmpuri calculate pot fi create i pentru text (concatenarea cmpurilor): ex.: Numepren: [Nume] & & [ Prenume] cmpurile calculate pot fi sortate, li se pot aplica criterii de selecie sau se pot totaliza. n cmpurile calculate se poate utiliza funcia IIF cu urmtoarea sintax: IIF ( , valoare 1, valoare2) unde: este o expresie a crei valoare de adevr este evaluat pentru fiecare nregistrare; valoare 1 este valoarea returnat dac expresie este adevrat; valoare2 este valoarea returnat dac expresie este fals. Pentru a aduga un cmp calculat ntr-o interogare se tasteaz numele acestuia ntr-o nou coloan din grila Query Design, se adaug dou puncte i apoi se completeaz expresia dorit. Lista conine nou funcii de tip agregat (funcii ce permit calculul unor valori totalizatoare la nivel de grup) ce sunt prezentate n tabelul urmtor: Funcia Returneaz Tipul de cmpuri pentru care se poate aplica

Funcia Returneaz Tipul de cmpuri pentru care se poate aplica AVG Media aritmetic Number. Date. Currency. AutoNumber COUNT Numrul total de valori Toate tipurile FIRST Prima valoare Toate tipurile LAST Ultima valoare Toate tipurile MAX Valoarea maxim Text. Number. Date. Currency. AutoNumber MIN Valoarea minim Text. Number. Date. Currency. AutoNumber STDEV Deviaia standard a valorilor Number. Date. Currency. AutoNumber SUM Suma valorilor Number. Date. Currency. AutoNumber VAR Varianta valorilor Number. Date. Currency. AutoNumber Pe lng aceste funcii mai sunt disponibile opiunile Where i Expression. Opiunea Where se va utiliza dac un cmp trebuie utilizat pentru a preciza anumite criterii de selecie, dar nu se dorete efectuarea gruprii dup cmpul respectiv. Opiunea Expression se va utiliza pentru cmpurile calculate ce returneaz un singur rezultat la nivelul grupului.

5

Interogarea tip tabel ncruciat Sunt interogri de tip total, cu deosebirea c n listele obinuite care folosesc denumirea cmpurilor ca titluri de coloane, tabelul ncruciat este un mod de a sintetiza datele ntr-o form specific. Interogrile de tip tabel ncruciat (CrossTab Query) sunt extrem de utile n scopul analizei multidimensionale a datelor permind obinerea unor situaii sintetice asemntoare tabelelor pivot consacrate de procesoarele de tabele (Microsoft Excel, Lotus 123, etc.). Practic, este posibil elaborarea unor tabele n care gruparea i ordonarea datelor se realizeaz att pe linii ct i pe coloane la intersecia crora se pot efectua calcule complexe. Pentru o interogare tabel ncruciat sunt necesare cel puin 3 cmpuri: unul care s furnizeze valorile pentru titlurile de rnd (Row Heading), cu meniunea c se pot alege mai multe cmpuri antet de rnd; unul care s dea valorile pentru titlurile de coloane (Column Heading); un singur cmp poate fi antet de coloan; unul care s fie baz pentru calcularea valorilor sintetice de afiat la punctele de intersecie rnd coloan; aceste valori se obin, de regul, prin nsumare i numrare utiliznd funciile Sum i Count. Etapele ce trebuie urmate pentru realizarea unei astfel de cereri de interogare sunt urmtoarele (Access dispune i de un program wizard ce permite elaborarea asistat a unor astfel de interogri): 1. Elaborarea unei interogri de selecie n modul Design View. Se vor alege tabelele ce conin datele i se vor selecta cmpurile dorite pentru afiare i eventualele cmpuri pentru care se vor impune restricii. 2. Din meniul Query se va selecta opiunea CrossTab Query ce va avea ca efect imediat afiarea liniilor Total i Crosstab n grila interogrii. 3. Se va specifica modul de agregare a datelor, respectiv funciile totalizatoare n linia Total. 4. n linia CrossTab se va opta pentru Row Heading n cazul cmpurile ce vor fi afiate pe liniile tabelului, Column Heading pentru cmpul ce va fi afiat pe vertical, i Value pentru valorile ce vor fi afiate la intersecia liniilor cu coloanele. Este permis existena mai multor cmpuri ordonate pe orizontal (Row Heading), dar a unui singur cmp Column Heading i a unui singur cmp Value.

6

Interogri de aciune Interogrile de aciune pot avea ca rezultat: Crearea de noi tabele (Make Table Query) Actualizarea datelor (Update Query) Adugarea de noi nregistrri (Append Query) tergerea nregistrrilor (Delete Query) Modul de elaborare a unei interogri tip aciune este similar celui prezentat n cazul interogrilor de selecie, presupunnd ca etap suplimentar specificarea explicit prin intermediul meniului Query a tipului de cerere dorit. Modificrile asupra bazei de date sunt efectuate doar n momentul execuiei interogrii. Este recomandat proiectarea n prealabil a cererii ca o simpl interogare de selecie i verificarea rezultatelor ce vor fi afectate deoarece nu exist posibilitatea revenirii n cazul tergerii sau modificrii eronate a nregistrrilor. 3.4.1. Interogri pentru crearea de noi tabele (Make Table Query) Crearea de noi tabele pe baza nregistrrilor din tabelele deja existente presupune parcurgerea urmtoarelor etape: 1. Elaborarea unei interogri de selecie n modul Design View (vor fi luate n calcul doar cmpurile ce dorim s fac parte din structura noii tabele, iar, n cazul n care sunt necesare i alte cmpuri pentru aplicarea unor criterii, acestea nu vor fi marcate n linia Show a grilei QBE). 2. Apelarea din meniul Query a opiunii Make Table Query i specificarea n casete Make Table a numelui noii tabele (se poate opta pentru crearea noii tabele ntr-o alt baz de date bifnd opiunea Another Database i specificnd numele fiierului). 3. Lansarea n execuie a interogrii. Tabela rezultat va moteni doar tipurile de date i dimensiunile cmpurilor din tabelele surs, nu i cheia primar sau eventualele proprieti la nivel de cmp ori tabel. Interogri pentru actualizarea datelor (Update Query) Acest tip de interogri permit modificarea valorilor datelor din unul sau mai multe cmpuri ale nregistrrilor ce satisfac restriciile impuse de utilizator. Pentru a realiza o interogare de tip Update este necesar selectarea n modul Design View a cmpurilor ce vor fi actualizate i, eventual, a celor ce fac obiectul restriciilor, dup care se va apela din meniul Query opiunea Update Query. Ca rezultat al acestei operaii n grila QBE va fi disponibil linia Update To n care se va putea completa formula de calcul sau valoarea dorit pentru actualizare. n cadrul interogrilor Update nu sunt admise modificri asupra cmpurilor cheie primar sau modificri ce ar putea conduce la nerespectarea restriciilor de integritate. O situaie ce ar putea genera erori survine atunci cnd, n cazul cmpurilor de tip text, valoarea rezultat depete lungimea cmpului din tabel. n acest caz valoarea va fi trunchiat din partea dreapt. Interogri pentru adugarea de nregistrri (Append Query) Interogrile pentru adugare ofer utilizatorilor posibilitatea inserrii de noi nregistrri n tabel pornind de la nregistrrile existente. Pentru a realiza o interogare de tip Append este necesar parcurgerea urmtoarelor etape: 1. Elaborarea unei interogri de selecie n modul Design View. Se va alege tabela ce conine datele i vor fi luate n calcul doar cmpurile ce conin valorile ce vor fi adugate, iar, n cazul n care se dorete aplicarea unor criterii de selecie a acestora, se vor aduga cmpurile de restricionat. 2. Se vor tasta eventualele condiii n linia de criterii a interogrii. 3. Din meniul Query se va selecta opiunea Append Query ce va conduce la afiarea ferestrei Append n care se va selecta numele tabelei n care dorim adugarea. 4. Transformarea interogrii de selecie ntr-o interogare de adugare are ca efect apariia rndului Append To n fereastra de proiectare a interogrii. n acest rnd se va specifica pentru fiecare cmp din tabela ce conine datele denumirea cmpului corespondent din tabela unde vor fi adugate.

7

Interogri pentru tergerea de nregistrri (Delete Query) Interogrile aciune de tip Delete ofer posibilitatea eliminrii mai multor nregistrri din tabele pe baza unor criterii impuse de utilizator.. Aciunea interogrilor de tergere acompaniat de opiunea de tergere n cascad configurat n fereastra Relationships poate conduce la tergerea simultan a datelor din mai multe tabele. Pentru a realiza o interogare de tergere se elaboreaz n mod normal o cerere selectndu-se cmpurile dorite i impunndu-se restriciile necesare dup care se apeleaz din meniul Query opiunea Delete. Exemplul urmtor prezint modalitatea n care se pot terge din baza de date toate comenzile efectuate dup data de 9 februarie de clienii ce au codurile 2 sau 3 (figura 3.76). Este evident c interogarea nu va putea fi executat n cazul n care tergerea unor nregistrri n tabela Comenzi ar conduce la nclcarea regulilor de integritate a bazei de date (relaia ntre tabela Comenzi i Coninut comand trebuie s accepte tergerea n cascad).

8

Pentru exemplificare se utilizeaz tabela Telefon care cuprinde personalul dintr-o unitate cu sau fr telefon. Tabela are urmtoarele cmpuri: cod_persoan, nume, prenume, pavilion, etaj, sectia, nr_telefon, data_instalrii Se cere: 1. Realizarea unei interogri pentru obinerea unei liste cu persoanele care au numrul de telefon care ncepe cu 1 Sub cmpul nr_telefon pe rndul Criteria se scrie expresia: LIKE 1* 2. Realizarea unei interogri pentru obinerea unei liste cu abonaii care au numrul de telefon care nu ncepe cu 1. Sub cmpul nr_telefon pe rndul Criteria se scrie expresia: NOT LIKE 1* 3. Realizarea unei interogri pentru obinerea unei liste cu abonaii care nu au telefon. Sub cmpul nr_telefon pe rndul Criteria se scrie expresia: IS NULL 4. Realizarea unei interogri pentru obinerea unei liste cu persoanele din pavilionul J1 care au numrul de telefon care ncepe cu 1 sau a celor care nu au telefon. 5. Lista telefoanelor instalate ntre 1 Ianuarie 2008 i 31 Decembrie 2008 se obine printr-o interogare n care pe rndul Criteria sub data_instalarii se scrie expresia: LIKE *.*.2004 6. Lista telefoanelor instalate n luna Mai 2008 se obine dac sub cmpul data_instalarii pe rndul Criteria se scrie expresia: LIKE *.05.2008. 7. Interogarea pentru obinerea listei persoanelor crora li s-a instalat telefon n luna decembrie 2008 va cuprinde sub cmpul data_instalarii expresia: >= # 01.12.2008 # And