Interogari
Interogarile sunt una dintre cele mai puternice componente din Access 97. Ele va permit sa regasiti anumite informatii stocate în baza de date. Abilitatea de a crea interogari este considerata a fi „creierul” unui sistem de baze de date relational, deoarece nu conteaza doar faptul ca puteti stoca informatii, ci mai ales faptul ca puteti regasi exact informatia de care aveti nevoie la un moment dat.
În Access, interogarile detin un rol foarte important (ele pot fi sursa unui raport, formular sau a listei de valori posibile pentru o coloana de cautare). Exista doua modalitati de creare a interogarilor în Access: folosirea ferestrei QBE (Query by Example), numita si Query Design, sau introducerea directa de comenzi SQL.indiferent pe care dintre cele doua moduri le folositi, intern, interogarea va fi definita tot ca o instructiune SQL. Access SQL este dialect care difera substantial de standardul ANSI. Despre diferentele dintre Access SQL si ANSI SQL vom vorbi, mai pe larg, în capitolul IV.
Dupa cum puteti observa din fig., fereastra QBE este compusa din doua parti. Partea de sus (pe care o vom numi si panou) prezinta tabelele pe care se bazeaza interogarea si relatiile dintre ele. Partea de jos, numita si grila QBE, precizeaza câmpurile tabelelor si criteriile pe baza carora este definita interogarea. Desi are limitarile sale, folosirea ferestrei QBE este un mod simplu si intuitiv de a crea o interogare.
Puteti specifica sursele de date pe care se bazeaza o interogare(tabele
sau alte interogari) fie în timpul crearii interogarii, fie dupa aceea prin intermediul cutiei de dialog Show Table. Aceasta va aparea automat la crearea unei noi interogari sau daca alegeti comanda Query | Show Table. Aici puteti selecta tabelele sau interogarile ce se vor servi drept sursa de date pentru interogarea curenta. O alta cale prin care puteti adauga tabele sau interogari la interogarea curenta este de a le plasa din fereastra Database în panoul ferestrei QBE.
Nota: Daca panoul ferestrei QBE a unei interogari se afla doua sau mai multe obiecte, între ele trebuie sa existe o relatie. Altfel, rezultatul interogarii va fi un produs cartezian.
Pentru a defini o interogare,trebuie sa aveti cel putin o tabela sau o interogare în panoul ferestrei QBE, si cel putin un câmp în grila ferestrei QBE. Dupa cum spuneam, rezultatele unei interogari sunt prezentate sub forma unei tabele virtuale, ale carei câmpuri sunt cele specificate în grila QBE. Datele acestei tabele sunt selectate din câmpurile din grila QBE pe baza unor criterii.
Daca doriti ca rezultatul interogarii sa contina anumite câmpuri ale unei tabele sau interogarii din panoul ferestrei QBE, introduceti-le în grila QBE în felul urmator: selectati-le în tabela sau interogarea respectiva (facând clic pe ele) si deplasati-le peste grila QBE. Pentru a introduce în grila toate câmpurile unei tabele, sau interogari din panou, selectati asteriscul care apare imediat sub bara de titlu si deplasati-l peste grila. Chiar daca rezultatul interogarii va contine toate câmpurile din tabela (rezultatul îl puteti vedea în modul Datasheet View, în care treceti cu comanda View | Datasheet View), în grila QBE va aparea doar asteriscul. Daca doriti sa stabiliti anumite criterii pe baza carora datele unui câmp sa fie incluse în rezultatul interogarii, câmpul trebuie sa fie vizibil în grila, asa ca va trebui sa-l introduceti separat.
O alta cale de a introduce în grila QBE un câmp este sa faceti clic în prima celula libera din linia Field a grilei si sa apasati butonul don dreapta. Se va deschide o lista cu toate câmpurile tavelelor si interogarilor din panoul QBE. O data selectat un câmp, se va completa automat si celula corespunzatoare din linia Table.
Când vorbim despre proprietatile unei interogari ne referim de fapt la proprietatile mai multor obiecte: ale interogarii ca obiect în sine, ale tabelelor, ale câmpurilor acestora si relatiilor.
Daca selectati o interogare din pagina Queries a ferestrei Database si apoi apasati butonul Properties de pe bara cu instrumente, se va deschide cutia de dialog prezentata în figura:
Pentru fiecare obiect puteti introduce o descriere de maximum 255 de caractere. Pe lânga descriere, cutia de dialog Properties va arata când a fost creat obiectul, când a fost modificat ultima data si cine este proprietarul lui. Daca validati caseta Hidden, obiectul nu va mai aparea în f 121d39b ereastra Database (aceasta este o masura de securitate). Cea de-a doua caseta de validare va lasa sa specificati daca obiectul este replicabil (despre replicare vom vorbi într-un capitol viitor).
Pentru a vedea si obiectele cu proprietatea Hidden (ascunse), alegeti comanda Tool | Options si, în pagina View, validati caseta Hidden Objects.
Pentru a vedea pagina de proprietati a ferestrei QBE, faceti dublu-clic în panou. Aceasta este o pagina detaliata de proprietati a interogarii.
În continuare, vom prezenta în detaliu aceste proprietati:
Acestea au fost proprietatile unei interogari privite ca întreg. Pe lânga acestea, puteti stabili si unele proprietati ale obiectelor ce compun interogarea: tabele, alte interogari si câmpuri.Proprietatile lor le puteti vedea în modul Design View selectându-le si, apoi, alegând comanda View | Properties.
Cele doua proprietati disponibile pentru tabele sunt urmatoarele:
Fiecare câmp din grila QBE are proprietatile sale. Cutia de dialog Properties pentru un câmp are doua pagini: General si Lookup. Iata care sunt aceste proprietati:
Nota: pentru acelasi câmp, puteti stabili calitati diferite în interogari diferite. Daca un formular este bazat pe o tabela, atributele câmpurilor sunt cele specificate în tabela. Daca formularul se bazeaza pe o interogare, atributele câmpurilor sunt cele specificate în interogare.
Daca optiunea AutoJoin este activata, Access creaza automat o asociere Inner Join între doua tabele, interogari sau între o tabela si o interogare aflata în panoul ferestrei QBE, în doua cazuri. Primul caz este cel în care exista relatii definite (vezi capitolul II), între obiectele din panou. Cel de-al doilea caz este cel în care doua tabele contin câmpuri cu acelasi nume si acelati tip de date si unul dintre câmpuri este cheie primara. Puteti schimba optiunea AutoJoin selectând comanda Tools | Options.
Între o asociere si o relatie exista o diferenta de baza: asocierile au relevanta numai în contextul interogarilor. O data ce interogarea a fost închisa, asocierile nu mai sunt importante din punct de vedere logic.
Puteti crea asocieri într-o interogare si manual, daca optiunea AutoJoin nu este activata. Pentru aceasta trebuie sa deplasati un câmp într-o tabela peste un câmp din alta tabela. Astfel, puteti crea mai multe asocieri între aceleasi doua tabele.
Daca doriti sa stergeti o asociere, faceti clic pe linia ce reprezinta asocierea în panoul ferestrei QBE si apasati tasta Delete.
Deoarece tipul implicit de asociere creat atunci când optiunea AutoJoin este activata este Inner Join, puteti schimba acest tip facând clic dreapta pe linia ce reprezinta asocierea în panoul ferestrei QBE si alegând din meniul context comanda Join Properties. Aici, selectati una dintre celelalte doua optiuni, Right Outer Join sau Left Outer Join.
Despre Inner Join, Left Outer Join si Right Outer Join am discutat amanuntit în capitolul II. Uneori însa este necesar sa facem o asociere între o tabela si ea însasi. O astfel de asociere poarta numele de Self Join.
Sa presupunem ca în tabela Profesor am avea coloana IdCoordonator, în care sa pastram identificatorul profesorului coordonator daca exista (fig.). Astfel, în tabela Profesor, exista o relatie recursiva între câmpurile IdCoordonator si IdProfesor.
Sa spunem ca dorim sa vedem numele fiecarui profesor si al coordonatorului sau, daca acesta exista. Pentru aceasta,vom crea o copie a tabelei Profesor, selectând-o de doua ori din lista prezentata în cutia de dialog Show Table. Acces va crea automat un alias pentru tabela Profesor, adica un nume alternativ, Profesor_1. În acest mod puteti crea mai multe aliasuri pentru o tabela. O asociere self-join poate fi inner, left outer sau right outer. Interogarea care ne da informatia dorita este prezentata în fig., iar rezultatele ei, în fig.
II.2. Tipuri de interogari
Exista interogari pentru regasirea datelor (cele de tip selectare) si pentru modificarea datelor (inserare, stergere si actualizare). Pâna acum am vorbit numai despre interogari simple de tip selectare. Pentru a crea o astfel de interogare nu trebuie decât sa folositi fereastra QBE pentru a specifica sursele datelor pe care doriti sa le aflati si, eventual, tipul de asociere. Rezultatele interogarii le puteti vedea intrând în modul Datasheet pentru acea interogare. Pentru aceasta, alegeti comanda Query | Run sau apasati butonul Run de pe bara cu instrumente.
În afara de interogarile de tip selectare simple, Acces va mai ofera un tip de interogari pentru regasirea datelor: interogarile agregat.
II.2.1. Interogari agregat
Interogarile agregat va dau posibilitatea de a analiza datele, calculând sume, extreme, medii, dispersii etc. Ele va pot fi foarte utile la întocmirea rapoartelor statistice sau a graficelor. Cele doua tipuri de interogari agregat pe care Access le pune la dispozitie sunt Totals si Crosstab.
II.2.1.1. Interogari de tip totals
Sa presupunem ca doriti sa aflati numarul de studenti înscrisi în fiecare curs optional. Interogarea care va va furniza aceste informatii este prezentata în fig. în modul Design View si în fig. în modul Datasheet View (rezultatele).
Pentru a crea o interogare de tip totals, procedati astfel:
creati o noua interogare de tip select si adaugati tabelele necesare la panoul ferestrei Qbe.
introduceti în grila ferestrei QBE coloanele pentru care doriti sa calculati totalul, media, extremele etc.
schimbati tipul interogarii în Totals, alegând comanda View | Totals sau apasând butonul Totals de pe bara cu instrumente. Observati ca în câmpul Total al grilei QBE va aparea în dreptul coloanelor selectate optiunea Group By.
selectati în grila QBE coloana în functie de care doriti sa aflati totalul, suma media etc. coloanelor cu optiunea Group By. În câmpul Total corespunzator acestei coloane alegeti una dintre functiile agregat, care sa efectueze operatia dorita.
stabiliti eventuale criterii si rulati interogarea.
În continuare, vom vorbi mai pe larg despre optiunile unei interogari de tip Totals. Fiecare coloana selectata în grila ferestrei QBE trebuie sa aiba specificata o optiune în câmpul Total. Iata care pot fi aceste optiuni:
Group By: folosita pentru a defini grupurile de înregistrari pentru care doriti sa calculati totalurile. Daca în grila QBE selectati o singura coloana, cu optiunea Group By, interogarea va returna o linie pentru fiecare valoare unica a coloanei respective. Daca selectati mai multe câmpuri cu optiunea Group By, rezultatele interogarii vor contine câte o linie pentru fiecare combinatie unica de valori ale câmpurilor respective.
Count: functie agregat folosita pentru a numara înregistrarile dintr-un grup ale caror valori sunt diferite de null.
Sum: functie agregat folosita pentru a calcula suma valorilor unui câmp, pentru fiecare grup definit de optiunea Group By.
Min: functie agregat ce calculeaza valoarea minima a unui câmp pentru fiecare grup. Valorile null nu sunt luateîn considerare.
Max: functie agregat ce calculeaza valoarea maxima a unui câmp pentru fiecare grup. Valorile null nu sunt luateîn considerare.
First: functie agregat folosita pentru a afla prima valoare (într-o anumita ordine) a unui câmp pentru fiecare grup. Valorile null nu sunt luate în considerare.
Last: functie agregat folosita pentru a afla ultima valoare (într-o anumita ordine) a unui câmp pentru fiecare grup. Valorile null nu sunt luate în considerare.
Avg: functie agregat ce calculeaza media valorilor unui unui câmp pentru fiecare grup definit prin optiunea Group By. Valorile null nu sunt luate în considerare.
StDev: functie agregat ce calculeaza deviatia standard a valorilor unui unui câmp pentru fiecare grup. Deviatia standard este un estimator statistic al dispersiei distributiei valorilor unui câmp. Valorile null nu sunt luate în considerare.
Var: functie agregat ce calculeaza dispersia valorilor unui unui câmp pentru fiecare grup. Dispersia este patratul deviatiei standard. Valorile null nu sunt luate în considerare.
Expression: folosita pentru a calcula alte totaluri decât cele oferite de functiile agregat, prin intermediul unei expresii introduse de dumneavoastra. O expresie poate fi orice combinatie de oparatori, contante, nume de coloane, functii agregat, etc., prin evaluarea careia obtinem o singura valoare. O astfel de expresie este Count(*), ce calculeaza numarul total de înregistrari dintr-un grup, incluzând inregistrarile care contin valoarea null.
Where: clauza folosita pentru specificarea unor criterii care sa limiteze rezultatele interogarii. Când folositi aceasta optiune, devalidati caseta din linia Show a grilei QBE corespunzatoare coloanei asupra carei impuneti limitarile.
În continuare, va prezentam doua exemple de interogari ce folosesc functia agregat:
sa presupunem ca dorim sa aflam media notelor obtinute de fiecare student la cursurile optionale la care s-a înscris. Pentru aceasta, avem nevoie de informatie din doua tabele: Student, pentru nume si prenume si Curs_Stud, pentru note. Adaugati aceste tabele la panoul ferestrei QBE al unei noi înregistrari. Înregistrarile vor fi grupate în functie de numele complet al studentilor (nume + prenume). De aceea, introduceti în grila QBE coloanele NumeSt si PrenumeSt din taqbela Student si schimbati tipul înregistrarii în Totals. În câmpul Total al grilei QBE, Access va selecta automat valoarea group by pentru coloanele NumeSt si PrenumeSt. Câmpul pentru care dorim sa calculam media pentru fiecare student este Nota din tabela Curs_Stud. Introduceti si acest câmp în grila QBE si alegeti optiunea Avg în celula corespunzatoare a liniei Total. Rulati interogarea. Figura. va prezinta în modul Design View.
Sa presupunem ca doriti sa aflati valoarea totala a impozitelor platite de profesorii fiecarei catedre, stiind ca impozitul este de 13% din salariul brut, pastrat în câmpul Salariu al tabelei Titlu. Deci, salariul fiecarui profesor depinde de titlul pe care îl are. Astfel, cele doua tabele de care vom avea nevoie în panoul ferestrei QBE a noii interogari sunt Profesor si Titlu. Grupurile vor fi prezentate de catedre, deci introduceti coloana Catedra a tabelei Profesor în grila QBE si schimbati tipul interogarii în Totals. Prin urmare, una dintre coloanele incluse în rezultatele interogarii va contine catedrele, iar cealalta va contine suma salariilor profesorilor ce lucreaza în cadrul fiecarei catedre în parte, înmultita cu 13 %. Pentru a calcula aceste valori, va trebui ca în grila QBE sa introducem o expresie. Expresiile se introduc în câmpul Field al grilei QBE si la formarea lor puteti folosi nume de coloane (incluse între paranteze drepte), operatori (+, -, *, / etc.),functii, constante, expresii predefinite etc. Cel mai simplu mod de a crea o expresie este cu ajutorul lui Expression Builder, un wizard specializat. Numele coloanei în care vor aparea valorile calculate ale expresiei îl puteti specifica tot în câmpul Field al grilei QBE astfel încât sa preceada expresia si sa fie urmat de doua puncte (:). Nu uitati ca, în câmpul Total corespunzator coloanei calculate, sa selectati optiunea Expression. Figura prezinta aceasta interogare în modul Design View. Observati expresia introdusa în câmpul Field al celei de-a doua coloane a grilei QBE, cât si titlul ei (Impozit).
Sortarea rezultatelor unei interogari de tip totals
Access va da posibilitatea de a sorta crescator sau descrescator rezultatele unei interogari, alegând în câmpul Sort al grilei QBE optiunea Ascending sau Descending. Daca folositi mai multe coloane cu optiunea Group By, Access va sorta automat datele, întâi în functie de prima coloana din grila QBE, apoi în functie de a doua, etc., de la stânga spre dreapta. În exemplul din figura., înregistrarile sunt sortate crescator, întâi în functie de numele studentului si apoi de prenume. Daca am fi dorit ca interogarile sa fie sortate întâi în functie de prenume si apoi în functie de nume (ca în figura), nu ar fi trebuit decât sa trecem în grila QBE coloana PrenumeSt în stânga coloanei NumeSt.
Access urmeaza urmatoarele reguli pentru sortarea rezultatelor unei interogari de tip Totals:
Daca în câmpul Sort al grilei QBE corespunzator coloanelor cu optiunea Group By alegeti Not sorted, adica nu specificati modul în care sa fie sortate datele, Access va sorta crescator aceste coloane, precedenta fiind de la stânga spre dreapta.
Daca în câmpul Sort al grilei QBE corespunzator coloanelor cu optiunea Group By specificati optiunea Ascending sau Descending, Access va sorta crescator respectiv descrescator aceste coloane, precedenta fiind de la stânga spre dreapta.
Daca specificati ordinea de sortare numai pentru anumite coloane (pentru unele dintre coloanele cu optiunea Group By, pentru coloanele agregat sau pentru o combinatie de coloane agregat si Group By), Access va sorta numai coloanele pentru care ati specificat ordinea respectând precedenta de la stânga spre dreapta.
Stabilirea criteriilor
Sa presupunem ca doriti sa aflati care sunt studentii al caror nume începe cu litera „P” si a caror medie este peste 7. Figura prezinta interogarea care va furnizeaza aceste informatii în modul Design View.
Primul criteriu este deci aplicat unei coloane având optiunea Group By, NumeSt, iar cel de-al doilea criteriu este aplicat unei coloane agregat. Criteriile impuse asupra coloanelor având optiunea Group By sau Where sunt luate în considerare înainte ca datele sa fie grupate, în timp ce criteriile asupra coloanelor agregat sunt impuse dupa ce datele au fost grupate si s-au aplicat functiile agregat.
Daca doriti totusi sa impuneti limitari si asupra datelor ce vor fi supuse operatiilor de agregare, puteti face acest lucru prin intermediul optiunii Where. Sa presupunem ca dorim sa aflam media cursurilor la care studentii al caror nume începe cu „P” au luat peste nota 7. acest lucru îl puteti realiza daca stergeti criteriul „>7” impus coloanei agregat Media si îl aplicati coloanei Nota având optiunea Where, ca în fig.
Nota: criteriile asupra coloanelor agregat sau cu optiunea Group By sunt specificate în SQL cu ajutorul clauzei HAVING, iar criteriile asupra coloanelor cu optiunea Where sunt implicate în SQL în cadrul clauzei WHERE.
II.2.1.2. Interogari de tip Crosstab
Interogarile de tip Crosstab se aseamana cu cele de tipul Totals prin faptul ca va permit aplicarea operatiilor de agregare asupra datelor. Ceea ce le deosebeste este modul de afisare a rezultatelor. Rezultatele unei interogari Crosstab sunt prezentate sub forma unei tabele în care atât coloanele, cât si liniile, au titluri.
Sa presupunem ca dorim sa aflam, pentru fiecare catedra, numarul de profesori din fiecare categorie (preparatori, asistenti, lectori, etc.).Ar fi convenabil ca datele sa fie afisate într-un tabel având catedrele drept capete de linii si titlurile drept capete de coloane. Pentru aceasta, veti crea o interogare Crosstab (pe care o puteti numi Total_Profesori_Crt), în felul urmator:
Creati o noua interogare de tip select si adaugati tabelele necesare.
Introduceti în grila QBE cele doua câmpuri de valori care vor defini grupurile (de exemplu, Catedra si Titlu).
Schimbati tipul interogarii în Crosstab, alegând comanda Query | Crosstab Query. În câmpul Total al grilei QBE corespunzator celor doua coloane va fi selectata optiunea Group By.
Alegeti care dintre cele doua câmpuri va da numele liniilor si care pe cele ale coloanelor în cadrul tabelei cu rezultatele interogarii, selectând optiunea Row Heading, respectiv Column Heading tn celula corespunzatoare din câmpul Crosstab al grilei QBE.
În functie de datele pe care doriti sa le aflati, introduceti într-o noua coloana a grilei QBE optiunile dumneavoastra. Pentru exemplul nostru, introduceti în câmpul Field expresia IdProf: Count(*), în câmpul Total selectati Expression, iar în câmpul Crosstab selectati optiunea Value.
Stabiliti eventuale criterii si rulati interogarea.
Figura prezinta interogarea în modul Design View.
Câmpul care da numele coloanelor din tabela cu rezultatele interogarii poarta numele de pivot. În cazul interogarii prezentate în figura câmpul pivot este Titlu iar în urmatoarea este prezentat rezultatul.
Puteti crea o coloana care sa arate totalul valorilor pe o linie, adaugând la grila QBE un duplicat al coloanei care are optiunea Value. Duplicatul trebuie sa aiba un alias(pe care îl puteti introduce in câmpul Field corespunzator, înaintea expresiei si urmat de doua puncte) si sa aiba optiunea Row Heading în câmpul Crosstab.
Limitarile intergarilor Crosstab
Dupa cum ati observat, într-o interogare de tip Crosstab numele coloanelor sunt luate dintre valorile coloanei cu optiunea Column Heading. De aceea, ele ar putea sa încalce regulile impuse numelor de coloane. De exemplu, daca o coloana de tip text, continând, printre altele si unele valori precum ON, AS sau alte cuvinte rezervate, va da numele coloanelor într-o interogare crosstab, acea interogare nu va putea fi folosita ca sursa de date pentru un raport, grafic sau alta interogare. De regula, puteti rezolva aceasta problema incluzând numele ilegale între paranteze drepte ([ON]).
În al doilea rând, în interogarile crosstab nu puteti impune criterii asupra câmpurilor cu optiunea Value. De exemplu, daca vom dori ca interogarea Total_Profesori_Crt sa ne dea catedrele care au mai mult de 2 profesori din fiecare categorie (titlu), nu vom putea specifica „>=2” în câmpul Criteria al coloanei de tip Value. Puteti înlatura acest inconvenient cu ajutorul unei interogari de tip Totals, pe care o vom folosi în interogarea Crosstab. Creati deci o interogare de tip Totals, numita Total_Profesori, bazata pe tabelele Profesor si Titlu, în care câmpurile Catedra si Titlu sa aiba optiunea Group By, iar pentru agregare sa se foloseasca expresia: Count (*) (ca în fig. ), pentru care puteti impune si criteriul „>=2”. Dati câmpului agregat numele TotalPr.
Apoi, creati interogarea Cross_Total_Profesori, de tip Crosstab si bazati-o pe Total_Profesori, în care catedra va fi Row Heading, Titlu va fi Column Heading si TotalPr va avea optiunea Value. Deoarece datele din coloana TotalPr a interogarii Total_Profesori_Crt au fost agregate o data, alegeti în câmpul Total al grilei QBE corespunzator lui TotalPr o functie agregat care, aplicata unei singure valori, sa de ca rezultat acea valoare (Sum, Min, Max, Avg, First, Last).
Valori NULL în coloane Row Heading sau Column Heading
Sa presupunem ca în tabela Profesor exista si înregistrari pentru care câmpul IdTitlu are valoarea NULL. În acest caz, rezultatele interogarii Total_Profesor_Crt vor contine si o coloana al carui nume va fi „<>” (ca în fig. )
Aceasta problema poate fi rectificata prin doua metode. Prima ar fi sa nu luam în considerare înregistrarile care contin valoarea Null în câmpul IdTitlu. Acest lucru îl putem realiza introducând criteriul „Is Not Null” în câmpul Criteria corespunzator coloanei Titlu. În acest mod, rezultatele interogarii nu vor da nici un fel de informatii despre profesorii pentru care titlul nu a fost specificat.
Daca totusi, nu ne putem lipsi de aceste informatii, vom face în asa fel încât în locul semnului „<>”, pentru numele coloanei sa apara un text dorit de noi în cazul în care valoarea este NULL. Pentru aceasta, vom introduce în câmpul Field al grilei QBE corespunzator coloanei cu optiunea Column Heading (care contine valorile null si da numele coloanelor tabelei cu rezultate), expresia:
IIf (IsNull([Titlu]), „Nespecificat”, [Titlu])
Expresia de mai sus se traduce astfel: „Daca valoarea câmpului Titlu este Null, atunci pentru titlul coloanei din tabela de rezultate se va afisa „Nespecificat”; altfel, se va afisa valoarea din câmpul Titlu”. Rezultatele interogarii vor fi cele din figura..
Folosirea numelor fixate pentru coloanele din tabela rezultatelor
Înterogarile de tip Crosstab au o proprietate Column Headings, pe care o puteti folosi în diferite împrejurari:
Pentru a pune coloanele într-o anumita ordine. Altfel, Access va ordona coloanele din tabela de rezultate crescator, alfabetic sau cronologic (în functie de tipul lor).
Pentru a include o anumita coloana, chiar daca ea nu va contine nici o valoare.
Pentru a exclude o coloana, chiar daca ea contine valori
De exemplu, daca veti dori sa ordonati coloanele din tabela de rezultate a interogarii Total_Profesori_Crt în ordinea fireasca a importantei titlurilor, fara a include si preparatorii, introduceti în câmpul Column Headings al paginii de proprietati a interogarii valorile coloanei Titlu, în ordinea dorita si fara valoare „Preparator”:
„Asistent”, „Lector”, „Lector dr”, „Conferentiardr”, „Profesor dr”.
Rezultatele vor fi afisate în figura.
Nota: Numele introduse în câmpul Column Heading al paginii de proprietatia interogarii trebuie sa corespunda exact valorilor din coloana cu optiunea Column Heading. Altfel, daca în loc de „Profesor dr” ati scrie „Prof dr”, în pagina de proprietati, rezultatele interogarii vor include coloana cu numele „Prof dr” care însa nu ar contine nici o vaoare, de unde ati putea trage concluzia eronata ca nici o catedra nu are cadre didactice cu titlul profesor dr.
II.2.2. Interogari pentru definirea si modificarea datelor
Interogarile pentru definirea si modificarea datelor va dau posibilitatea de a actualiza eficient, printr-o singura operatie, mai multeînregistrari. Cele 4 tipuri de interogari care pot efectua diferite actiuni asupra datelor sunt: interogari de tipul Make Table, Update, Append si Delete.
II.2.2.1. Interogari de tip Make Table
Interogarile de tip Make Table va ajuta sa creati tabele noi furnizate cu tabele existente sau alte interogari. Ele va pot fi utile daca doriti sa faceti o copie de siguranta a unei tabele sau sa exportati date catre alte baze de date Access sau ODBC.
Pentru a crea o interogare de tipul Make Table, deschideti o noua interogare si introduceti în panoul ferestrei QBE tabelele (sau interogarile) care vor furniza datele pentru noua tabela. Apoi selectati în grila QBE coloanele care vor compune noua tabela si, eventual, specificati criterii pentru limotarea datelor. Daca veti dori ca în noua tabela coloanele sa aiba alte nume, introduceti aceste nume în câmpul Field al grilei QBE, înaintea numelor coloanelor existente si urmate de doua puncte (de exemplu, Nume Profesor: Nume). Pe urma, alegeti comanda Query | Make Table Query sau apasati butonul Query Type de pe bara cu instrumente si alegeti Make Table Query. Va aparea cutia de dialog Make Table, în care va trebui sa specificati numele tabelei pe care vreti sa o creati si, în cazul încare tabela va face parte dintr-o baza de date externa, numele acesteia cu calea completa. Daca baza de date externa nu este Access, introduceti între ghilimele si urmat de punct si virgula numele aplicatiei în care a fost creata (de exemplu: c:\my documents\dbl „Paradox;”).
Puteti vedea noua tabela înainte ca ea sa fie creata, trecând în modul Datasheet View. Daca rezultatele sunt corecte, rulati interogarea si noua tabela va fi creata; daca nu, întoarceti-va în modul Design View si efectuati modificarile necesare.
Daca, ulterior, veti dori sa actualizati datele din tabela creata astfel, pentru ca ele sa reflecte modificarile din tabela de baza, nu va trebui decât sa rulati din nou interogarea respectiva.
Figura prezinta interogarea MakeProfBack care creaza tabela ProfMate, cu parofesorii catedrei „Matematici”, continând câmpurile IdProf, Nume si Titlu.
II. 2.2.2. Interogari de tip Append
Interogarile de tip append adauga un grup de înregistrari dintr-o tabela în alta tabela. Sa presupunm ca tabela ProfMate pe care am creat-o rulând interogarea MakeProfBack (de tip Make Table) dorim sa adaugam si profesorii catedrei de Informatica. Pentru aceasta, vom crea o noua interogare de tip Append (numita AppendProf), alegând comanda Query | Append Query. Va aparea cutia de dialog append, în care va trebui sa specificati numele tabelei în care vor fi introduse datelesi baza de date în care se afla aceasta tabela (interogarea nu va putea fi executata daca tabela nu exista deja). Apoi, cu ajutorul ferestrei Qbe, veti preciza tabelele si câmpurile acestora din care vor proveni datele, precum si câmpurile tabelei destinatie. Dupa cum puteti observa în fig., grila QBE a unei interogari de tip Append contine si linia Append To, în care, pentru fiecare câmp-sursa selectat în grila, puteti alege câmpul destinatie (cu conditia ca cele 2 câmpuri sa aiba acelasi tip de date).
Nota: în cazul încare câmpurile sursa si destinatie au acelasi nume, Access va alege automat câmpul destinatie, în momentul selectarii câmpului sursa în grila QBE.
Acum, tot ce va ramâne de facut este sa rulati interogarea si sa deschideti tabela ProfMate în modul Datasheet View pentru a vedea noile înregistrari.
II.2.2.3. Interogari de tip Update
Interogarile de tip Update va sunt utile atunci când doriti sa modificati mai multe înregistrari o data. Sa presupunem ca toate salariile mai mici de 1.000.000 lei se majoreaza cu 15%. Pentru a actualiza în consecinta datele din tabela Titlu, vom crea tabela Majorare_Salariu, de tip Update. Pentru aceasta, creati o noua interogare, introduceti tabela Titlu în panoul ferestrei QBE si schimbati tipul interogarii în Update, alegând comanda Query | Update. În grila ferestrei QBE va aparea o noua linie, Update To, în care veti introduce regula pe baza careia se va face actualizarea (în acest caz, [Salariu] + [Salariu]*.15). în câmpul Criteria al grilei QBE vom limita înregistrarile ce vor fi actualizate la cele pentru care salariul este 1.000.000. interogarea Majorare_Salariu este prezentata în figura.
Interogarile de tip Update nu returneaza valori, de aceea este inutil sa treceti în modul Datasheet View pentru a vedea rezultatele (nu veti vedea decât datele care urmeaza sa fie modificate). Rulati interogarea si apoi veti putea vedea schimbarile deschizând tabela respectiva în modul Datasheet View.
Interogarile de tip Update au anumite limitari care fac sa nu poata fi executate uneori. Înaceste situatii, Access va da un mesaj de eroare. Iata care sunt cele mai frecvente situatii în care o interogare de tip Update nu va putea fi executata:
interogarea este bazata pe alta înterogare de tip Crosstab, Union sau care contine totaluri;
interogarea este bazata pe 2 tabele între care exista o relatie de tipul 1:m (integritatea referentiala ar putea fi violata);
interogarea încearca sa scrie un text într-un câmp de tip Number;
interogarea încalca regulile de validare impuse asupra datelor unui câmp la nivel de tabela.
II.2.2.3. Interogari de tip Delete
Folosind o interogare de tip Delete, puteti sterge dintr-o tabela mai multe înregistrari o data.
Nota: înainte de a executa o interogare de tip Delete, este bine sa creati pe aceleasi criterii o interogare de tip Select care sa regaseasca datele pe care doriti sa le stergeti. Daca ele sunt corecte, puteti rula interogarea Delete fara grija.
Sa presupunem ca din tabela pe care am creat-o rulând interogarea MakeProfBack, dorim sa stergem înregistrarile referitoare la profesorii catedrei de informatica (pe care le adaugasem cu ajutorul interogarii AppendProf de tip Append).Pentru a crea o interogare de tip Delete, deschideti o interogare noua (în fereastra QBE) si introduceti în panoul ferestrei QBE tabela care contine înregistrarile ce pot fi sterse (ProfMate, în acest caz). Apoi, transformati acesta noua interogare într-una de tip alegând comanda Query | Delete Query (Access va adauga la grila QBE o noua linie, Delete). Câmpul Delete ne ofera doua optiuni: Where si From. Daca pentru un câmp introdus în grila QBE alegem înregistrarile ce vor fi sterse (în figura observati ca am limitat înregistrarile ce vor fi sterse la acelea pentru care câmpul Catedra are valoarea „Informatica”). Daca interogarea se bazeaza pe doua tabele aflate într-o relatie de tip 1:m (asupra carora a fost impusa integritatea referentiala cu optiunea Cascade Delete), vom specifica tabela din care se vor sterge înregistrari introducând în grila QBE câmpul asterisc (*)al acelei tabele si alegând în câmpul Delete corespunzator optiunea From. Dupa aceea, puteti impune limitari ca si în cazul folosirii unei singure tabele.
II.2.2.5. Interogari cu parametri
Puteti crea interogari care sa permita utilizatorului specificarea unui criteriu în timpul rularii interogarii. Pentru a crea o astfel de, interogare va trebui sa :
introduceti în câmpul Criteria corespunzator coloanei asupra careia vor fi impuse limitarile un text prin care sa cereti utilizatorului specificarea criteriului (textul va fi inclus între paranteze drepte, ca în figura )
folositi comanda Query / Parameters pentru a specifica tipul datelor ce vor fi introduse de utilizator în timpul rularii interogarii (figura)
La rularea interogarii Access va afisa o cutie de dialog în care utilizatorul va introduce valoarea parametrului.
Sa cream o interogare de tip Select care va regasi mediile studentilor al caror nume de familie încep cu o litera specificata de utilizator. (Precizam faptul ca daca am fi vrut, de exemplu, ca acesta litera sa fie „P”, atunci, în câmpul Criteria corespunzator coloanei Nume al tabelei Student, am fi introdus expresia: Like “P*”.) În acest caz, criteriul impus coloanei Nume va fi: Like [Introduceti prima litera a numelui] & “*”. Operatorul & indica faptul ca litera introdusa va fi concatenata cu caravterul *.
Oricare dintre tipurile de interogari prezentate anterior pot folosi parametri. Interogarile cu parametri pot fi folosite ca sursa de date pentru formulare si rapoarte.
II. 2.2.6 Interogari imbricate
Dupa cum am mai spus, o interogare poate fi bazata pe alta interogare car, la rândul sau, sa fie bazata peo interogare etc. interogarile de acest tip se numesc interogari imbricate. Decât sa cream o interogare bazata pe 1o tabele, mai bine cream câteva interogari intermediare, mai simple, care sa furnizeze datele pentru interogarea dorita. Deci, lucrul cu interogarile imbricate este mult mai simplu.
Pe de alta parte, o interogare cu mai multe nuveluri de imbricare este mai lenta în executie decât una bazata pe mai multe tabele, deoarece Access trebuie sa ruleze fiecare interogare în parte.
II.2.2.7. Interogari de tip Union
Interogarile de tip Union sunt folosite pentru a pune laolalta datele din 2 sau mai multe tabele sau interogari diferite. Ele nu pot fi creat e în modul Design View, ci numai scriind direct codul în modul SQL View.
Pentru a crea o interogare de tip Union, deschideti o noua interogare, apoi treceti în modul SQL View cu comanda View | SQL View. Va aparea fereastra SQL , continând un singur cuvânt: SELECT. Acesta este începutul interogarii. Pentru ca interogarea sa poata fi rulata cu succes, câmpurile tabelelor care vor fi reunite trebuie sa aiba aceleasi denumiri. Iata un exemplu simplu de interogare Union, în care am presupus ca Profesor_Buc si Profesor_Iasi sunt cele 2 tabele ale caror date vrem sa le reunim:
SELECT Nume, Titlu, Catedra FROM Profesor_Buc UNION SELECT
Nume, Titlu, Catedra FROM Profesor_Iasi
Rezultatul interogarii va fi o tabela cu câmpurile Nume, Titlu si Catedra, continând toate înregistrarile distincte din cele 2 tabele, Profesor_Buc si Profesor_Iasi.
O interogare de acest tip poate contine mai multi operatori UNION, în functie de numarul tabelelor pe care vrem sa le reunim.
Desi atât numarul câmpurilor cât si denumirile lor trebuie safie aceleasi de o parte si de alta a operatorului UNION, nu este necesar ca tipurile de date sa corespunda.
II.3. Expresii si functii în interogari
Expresiile sunt instrumente folosite de programatori pentru a spori performantele aplicatiilor. Ele sunt formate dintr-o combinatie de operatori, constante, variabile si functii care au ca rezultat o numita valoare. Expresiiloe pot fi folosite în interogari, formulare, rapoarte, proprietati ale câmpurilor tabelelor sau ale controalelor din formulare si rapoarte, în functii macro si module. Ele pot fi folosite pentru a stabili criterii, ca rguli de validare sau ca baza pentru coloanele calculate. De exemplu, daca într-o interogare introduceti urmatoara expresie în câmpul Criteria corespunzator unei coloane de tip Date/Time (tip folosit pentru stocarea datelor si orelor), ea va returna numai înregistrarile mai vechi de 3 zile:
<Now () + 3.
II.3.1. Partile componente ale expresiilor
Expresiile pot fi privite ca niste propozitii matematice, în componenta carora pot intra:
operatori: simboluri matematice;
constante: valori numerice sau siruri de caractere ce nu îsi schimba valoarea;
functii: proceduri ce returneaza o valoare (de exemplu, functia Now returneaza data curenta);
nume de câmpuri: de obicei sunt introduse între paranteze drepte
II.3.1.1 Operatori
Operatorii joaca un rol important în cadrul expresiilor. Exista mai multe tipuri de operatori pe care vi le prezentam în cele ce urmeaza.
Operatori aritmetici
Sunt folositi pentru a efectua calcule matematice cu 2 sau mai multe valori numerice.
ridica un numar la o putere (de exemplu 2^3=23)
înmulteste 2 numere
împarte 2 numere si returneaza un numar real
împarte 2 numere si returneaza un numar întreg
MOD returneaza restul împartirii a 2 numere
aduna 2 numere
scade 2 numere
Operatori de concatenare
Sunt folositi pentru a lega 2 siruri de caractere.
& concateneaza 2 siruri de caractere
aduna valorile a 2 câmpuri numerice. Poate fi folosit si pentru a concatena siruri de caractere.
Operatori de comparatie
Sunt folositi pentru a compara valorile a 2 sau mai multe câmpuri si/sau expresii.
verifica egalitatea a 2 valori
<> verifica daca 2 valori sunt diferite
< verifica daca o valoare este strict mai mica decât alta
> verifica daca o valoare este strict mai mare decât alta
<= verifica daca o valoare este mai mica sau egala cu alta
>= verifica daca o valoare este mai mare sau egala cu alta
Operatori logici
Efectueaza operatii logice.
Not introduce o negatie (de exemplu, Not T* introdus într-o interogare în câmpul Criteria al coloanei Nume va regasi toti studentii ale caror nume nu încep cu litera T)
And efectueaza conjunctia a 2 valori
Or efectueaza disjunctia a 2 valori
Xor efectueaza disjunctia exclusiva a 2 valori
Eqv verifica echivalenta a 2 valori
Imp operatorul „implica”
Precedenta operatorilor
De multe ori, a calcula valoarea unei expresii implica efectuarea mai multor operatii. Aceste operatii se efectueaza într-o anumita ordine predefinita, care este si ordinea în care au fost prezentati operatorii mai sus.
Daca doriti o schimbare în aceasta ordine (de exemplu sa efectuati întâi o operatie de adunare si apoi o ridicare la putere) folositi parantezele pentru a preciza care operator va fi aplicat primul.
II.3.1.2. Constante
Constantele sunt un fel de cuvinte rezervate, în sensul ca valoarea lor nu se schimba pe parcursul rularii aplicatiei. Ele se împart în 3 grupe, pe care vi le prezentam în continuare.
Constante predefinite
Acestea sunt definite de programator, de obicei în module. Declaratia unei constante predefinite începe cu cuvântul cheie CONST. De exemplu:
CONST Pi = 3.14
Dupa ce au fost declarate, ele pot fi folosite oriunde
Constante intrinsece
Sunt constante furnizate de Visual Basic (cum ar fi VbString sau VarType) si nu trebuie sa fie declarate separat. Programatorul nu poate defini o constanta care sa aiba acelasi nume ca o constanta intrinseca. Constantele intrinseci se pot folosi numai în module.
Constante sistem
Yes, No, On, Off si Null sunt cele 5 componente sistem. Ele pot fi folosite în orice obiect din baza de date, mai putin în module.
II.3.1.3. Functii
Functiile pot intra în componenta expresiilor. Ele actioneaza ca si operatorii, dar nu sunt reprezentate prin simboluri. De exemplu, operatorul + si functia Sum efectueaza aceeasi operatie: cea de adunare. Diferenta este aceea ca functia Sum poate fi aplicata mai multor valori o data. Access va pune la dispozitiepeste 160 de functii, dintre care câteva le puteti vedea în câmpul Totals al grilei QBE a unei interogari de tip Totals.
II. 3.1.4. Nume de câmpuri si parametri
Numele coloanelor din tabele sau sau cele ale parametrilor pot intra si ele în componenta unei expresii. De obicei, numele coloanelor trebuie incluse între paranteze drepte. Numele de parametri sunt cele pe care le declarati în cutia de dialog Parameters a unei interogari. De asemenea, în expresii pot aparea si aliasuri pentru numele coloanelor.
Puteti folosi numele unui câmp într-o interogare numai daca el se afla în panoul ferestrei QBE sau daca el este obtinut printr-o sub-interogare.
II.3.2. Localizarea expresiilor
În functie de locul în care introduceti expresiile, acestea vor avea un anumit efect. Daca scrieti o expresie în câmpul Field al grilei QBE a unei interogari, în modul Datasheet View va aparea o noua coloana. Daca expresia este introdusa în câmpul Criteria sau Total al grilei QBE corespunzator unei coloane, asupra coloanei respective vor fi impuse limitari sau se vor efectua operatii de agregare.
Dupa ce ati introdus o expresie într-o celula a grilei QBE si ati apasat tasta Enter, Access va parcurge expresia pentru a o verifica din punct de vedere sintactic . daca va fi detectata vreo eroare “gramaticala”, Access va va indica portiunea din expresie unde se afla eroarea respectiva. Nu puteti rula interogarea pâna când problema nu va fi rezolvata.
II. Lucrul cu date si ore
În Access, expresiile pot contine date sau ore. Acestea sunt stocate ca numere reale pe 64 de biti. Intervalul maxim pentru date este 1 Ian 100 si 31 Dec 9999, iar cel pentru ore, între 0:00:00 si 23:59:59. Dupa ce o data/ora a fost introdusa într-o expresie, Access parcurge expresia si recunoaste formatul respectiv, incluzând data (ora) între 2 caractere diez (
Access va pune la dispozitie aproximativ 22 de functii pentru lucrul cu date si ore. De exemplu, în interogarea Vechime, prezentata în figura., am folosit functiile Yes si Now pentru a calcula câti ani au trecut de la angajarea unui profesor si pâna în prezent. Functia Year () returneaza numai anul dintr-o data completa, iar functia Now () returneazadata (si ora) curenta. Rezultatele interogarii sunt prezentate în figura.
Exista câteva formate predefinite pentru afisarea datelor/orelor în formulare, rapoarte, în modul Datasheet View al unei tabele sau interogari etc. tabelul va prezinta o lista cu aceste formate, pe care o puteti vedea si în câmpul proprietatii Format a unei coloane de tip Date/Time a unei tabele (în modul Table Design).
|
Format |
Afisare (exemplu) |
|
General |
12:00:00 AM |
|
Short date | |
|
Medium date |
1-Jan-98 |
|
Long date |
Thursday, January, 1, 1998 |
|
Short time | |
|
Medium time |
12:00 AM |
|
Long time |
12:00:00 AM |
Prin intermediul expresiilor puteti sa personalizati modul cum vor fi afisate datele/orele. În tabelul . sunt descrise diferite formate pe care le puteti folosi în acest scop.
|
Format |
Afisare (exemplu) |
|
d |
Ziua din luna, între 1 si 31 |
|
dd |
Ziua din luna, între 01 si 31 |
|
ddd |
Ziua saptamânii (primele 3 litere din ziua saptamânii; de exemplu, Monday pentru luni, Tuesday pentru marti etc) |
|
dddd |
Ziua saptamânii din limba engleza (întreg cuvântul; de exemplu, Monday pentru luni, Tuesday pentru marti etc) |
|
w |
Ziua saptamânii, între 1 si 7 |
|
ww |
Saptamâna din an, între 1 si 52 |
|
m |
Luna anului, între 1 si 12 |
|
mm |
Luna anului, între 01 si 12 |
|
mmm |
Primele 3 litere din luna anului în engleza (de exemplu, Jan pentru ianuarie, Feb pentru februarie, etc) |
|
Mmmm |
Luna anului în limba engleza (întreg cuvântul; de exemplu, Jan pentru ianuarie, Feb pentru februarie, etc) |
|
q |
Trimestrul anului, între 1 si 4 |
|
y |
Ziua din an, între 1 si 365 |
|
yy |
Anul, între 00 si 99 |
|
yyyy |
Anul, între 0100 si 9999 |
|
h |
Ora, între 1 si 23 |
|
hh |
Ora, între 01 si 23 |
|
n |
Minutul, între 1 si 59 |
|
nn |
Minutul, între 01 si 59 |
|
s |
Secunda, între 1 si 59 |
|
ss |
Secunda, între 01 si 59 |
|
AM/PM |
AM pentru ore între miezul noptii si miezul zilei; PM pentru ore între miezul zilei si miezul noptii |
|
am/pm |
am pentru ore între miezul noptii si miezul zilei; pm pentru ore între miezul zilei si miezul noptii |
|
A/P |
A pentru ore între miezul noptii si miezul zilei; P pentru ore între miezul zilei si miezul noptii |
Semnul slash (/) este folosit, de obicei, ca separator pentru data, iar doua puncte ( ), ca separator pentru ora.
Folosind aceste formate, puteti crea expresii care sa returneze valori de genul:
Luna angajarii este Feb
Pentru aceasta, introduceti în câmpul Field al grilei QBE expresia:
Luna: “Luna angajarii este “ &, unde:
Luna: este numele coloanei care va contine, în tabela de rezultate, valorile expresiei;
“Luna angajarii este “ este textul care va aparea înaintea lunii angajarii;
Format ([Data Angajarii], “mmm”) converteste valoarea stocata în câmpul Data Angajarii la formatul mmm.
Functia Now () returneaza data si ora curenta în functie de ceasul sistemului. O functie asemanatoare este Date (), care returneaza doar data curenta.
Nota: daca observati ca informatiile returnate sunt eronate, verificati data si ora sistemului (în Control Panel, la sectiunea Date/Time), deoarece functiile Now () si Date () se bazeaza pe acestea.
II.3.4. Functia IIF în expresii
Sintaxa functiei IIF este urmatoarea:
IIF (<Expresie>, valoare1, valoare2)
Unde:
este o expresie a carei valoare de adevar este evaluata pentru fiecare înregistrare în parte.;
valoare1 este valoarea returnata daca <expresie> este adevarata;
valoare2 este valoarea returnata daca <expresie> este falsa.
Astfel, sa presupunem câmpul pentru fiecare student dorim sa aflam daca a promovat sau nu oate cursuriile optionale la care s-a înscris (adica, daca nota minima obtinuta este sau nu mai mare sau egala cu 5). În tabela cu rezultatele interogarii vom avea o coloana, numita Situatia, care va avea valoarea “promovat” sau “Nepromovat”. Expresia care da valorile acestei coloanea este urmatoarea:
Situatia: IIf (Min ( [ Nota ] )>= 5, “promovat”, “nepromovat”
Interogarea (de tip Totals) este prezentata în figura., iar rezultatele ei în figura.
II.3.5. Crearea expresiilor cu Expression Builder
Expression Builder este una dintre componentele importante din Access 97, ce va permite crearea de expresii complicate folosind doar mouse-ul. El poate fi lansat de oriunde poate fi introdusa o expresie (câmpuri pentru specificarea regulilor de validare, a surselor de date, a crieriilor etc.), dupa caz, fie facînd clic pe butonul … ce apare în dreapta câmpului, fie facând clic dreapta în celula respectiva si alegând din meniu context comanda Build.
Expresiion builder se prezinta sub forma unei cutii de dialog modale, ampartita în 2 (fig. ). În partea sa superioara se afla un câmp de editare în va fi scrisa expresia, precum si butoane pentru introducerea rapida a opratorilor aritmetici, logici, de concatenare si de comparatie. În partea de jos exista 3 coloane. În cea mai din stânga, veti gasi toate obiectele care contin elemente ce ar putea intra în componenta unei expresii: tabele, interogari, formulare, rapoarte, functii, constante, operatori, expresii predefinite. Sa presupunem ca veti dori sa folosim functia Date () în cadrul expresiei. Pentru aceasta, faceti clic pe grupul care contine functia, anume Date/Time. În coloana din dreapta vor aparea toate functiile predefinite care se ocupa cu lucrul cu date si ore. Aici, facând dublu-clic pe functia Date, ea va aparea în câmpul de editare din partea de sus a cutiei de dialog. Astfel, puteti introduce în câmpul de editare toate elementele care compun expresia dorita. Dupa ce ati terminat, apasati butonul OK si expresia va fi introdusa în celula respectiva.
II.4 Access Jet Engine: procesarea interogarilor
Este important pentru un programator sa înteleaga modul în care motiorul Jet Engine proceseaza interogarile, deoarece, astfel, aplicatiile sale pot fi mult mai rapide în executie.
Motorul Access Jet Engine proceseaza interogarile în 3 etape: compilarea, optimizarea si executia. Fiecare dintre aceste etape trebuie sa fie efectuata cu succes, altfel procesul este oprit si apare un mesaj de eroare. Acest mesaj poate fi interceptat de catre programator si personalizat înainte de a fi afisat.
II.4.1. Definirea unei interogari
Prin definirea unei interogarise înâelege crearea sa cu ajutorul cu ajutorul unuia dintre instrumentele furnizate de Access: fereastra QBE, limbajul Access SQL sau Data Access Objects (DAO). Access transpune apoi interogarile în instructiuni SQL si le transmite mai departe motorului lui Jet Query Engine, pentru a pitea fi procesate.
II.4.2. Compilarea unei interogari
Prima operatie pe care o efectueaza motorul Jet Engine este verificarea sintaxei interogarii SQL si returnarea unui mesaj de eroare, daca este cazul. Urmeaza parcurgerea interogarii, pentru a face legatura dintre numele invocate si coloanele corespunzatoare din tabelele pe care se bazeaza interogarea. Pe urma, interceptarea este compilata într-un format intern, dupa care urmeaza faza de preoptimizare. Acum, interogarea este împarâita în elemente fundamentale (tabele de baza, coloane ce vor aparea în tabela de rezultate, restrictii, coloane de asociere (join), coloane dupa care vor fi sortate rezultatele). Astfel, se reduce complexitatea interogarii, crescând probabilitatea ca optimizatorul sa îmbunatateasca semnificativ structura si performanta ei.
II.4.3. Optimizarea unei interogari
Optimizatorul este una dintre cele mai complexe componente ale motorului Jet Query engine, el fiind responsabil de alegerea unei strategii optime de executie a interogarii. Alegerea se bazeaza pe crearea unei liste cu strategiile de executiem posibile si pe evaluarea timpilor de executie necesari fiecareia. Optimizatorul va alege strategia pentru care timopul de executie este minim. Fiecare strategie consta într-o întiruire de operatiuni care trebuiesc efectuate si care necesita, fiecare, un timp de executie. Operatiile care pot afecta în cea mai mare masura timpul total de executie a interogarii sunt parciurgerea tabelelor de baza si efectuarea asocierilor. Pentru fiecare dintre acestea, optimizatorul poate alege dintre mai multe strategii, pe cea mai performanta (a se vedea sectiunile III.4.5. si III.4.6.)
II.4.4. Executia unei interogari
Dupa ce a fost elaborat planul optim, motorul motorului Jet Query Engine executa acest plan pas cu pas si returneaza rezultatele. Aveti posibilitatea de a alege între 2 tipuri de tabele-rezultat: dynaset si snapshot.
Un dynaset este o tabela de rezultate care permite modificarea datelor. Când Jet Engine ruleaza o interogare ce va avea ca rezultat un dynaset, el creeaza în memorie un sir de valori-cheie unice care indica datele din tabelele de baza. Valorile coloanelor ce compun un dynaset nu sunt citite decât atunci când este necesar (de exemplu, când utilizatorul îl parcurge pentru a vedea o anumita linie), nefiind astfel nevoie de prea mult spatiu în memorie.
La crearea unui snapshot, motorul Jet Engine încarca în memorie toate rezultatele interogarii. Astfel, daca memoria nu este suficienta, datelevor fi scrise si pe disc, fiind stiut faptul ca ca accesul la datele de pe disc este mult mai lent decât cel la dateledin memorie.
În concluzie, tabelele dynaset sunt mult mai eficiente atunci când interogarea returneaza multe date. Daca interogarea returneaza un numar redus de coloane si linii, este mai eficient sa folositi tabelele snapshot. Retineti totusi faptul ca tabelele snapshot nu permit modificarea datelor.
II.4.5. Strategii de parcurgere a tabelelor de baza.
Access Jet Engine poate alege una dintre urmatoarele 3 strategii pentru parcurgerea datelor din tabelele de baza:
Parcurgerea secventiala: este, de obicei, cea mai putin rapida metoda, deoarece sunt citite toate înregistrarile din tabela de baza, si, pentru fiecare înregistrare sunt verificate restrictiile. Aceasta metoda este aleasa pentru tabele mici si neindexate.
Parcurgerea pe indecsi: Aceasta metoda este aleasa atunci cînd exista restrictii asupra unei coloane indexate dintr-o tabelă de baza. Dupa ce subnt selectate doar înregistarile pentru care sunt verificate aceste restrictii, aceste înregistrari vor fi supuse si restului de restrictii (daca exista). Aceasta metoda este eficienta atunci când tabelele de baza sunt mari, astfel încât, desi se vor citi aceleasi înregistrari de mai multe ori, timpul total este mai mic decât cel necesar pentru a citi întraga tabela.
Tehnica restrictionarii Rushmore: este aplicata atunci când exista restrictii asupra mai multor coloane indexate. Prin folosirea mai multor indecsi, motorul Jet reduce considerabil numarul de înregistrari ce trebuie citite. În functie de tipul restrictiei, Jet engine va efectua una dintre urmatoarele 3 operatii:
intersectia indecsilor: pentru restrictii de forma:
unde col1 si col2 sunt coloane indexate. Pentru fiecare restrictie este creata câte o multime de rezultate, care apoi sunt intersectate pentru a gasi înregistrarile ce verifica ambele restrictii.
reuniunea indecsilor: pentru restrictii de forma:
col1 = <exp> OR col2 = <exp>
unde col1 si col2 sunt coloane indexate. Pentru fiecare restrictie este creata câte o multime de rezultate, care apoi sunt reunite pentru a gasi înregistrarile ce verifica ce verifica cel putin una dintre cele 2 restrictii.
numararea indecsilor: pentru interogari ce returneaza numarul înregistrprilor ce verifica o anumita multime de restrictii:
SELECT Count (*) FROM Tabela
WHERE col1 = <exp> AND col2 <exp>
Pentru astfel de interogari, Jet Engine nu trebuie decât sa citeasca paginile cu indecsi.
II.4.6. Strategii de efectuare a asocierilor
Pe baza unor statistici asupra tabelelor, motorul Jet Engine alege una dintre urmatoarele strategii de asociere, atunci când interogarea se bazeaza pe mai multe tabele.
Asocierile prin iteratii imbricate: folosita când nu exista indecsi si tabelele contin mai putine înregistrari.
Asociere pe index: parcurge înregistrarile dintr-o tabela si cauta înregistrarile corespunzatoare în cea de-a 2-a tabela, folosindu-se de un index. Este aleasa când cea de-a doua tabela este mica si are coloana de asocoere indexata sau când prima tabela e mica si puternic restrictionata.
Asociere de tip lookup: este similara asociatiei pe index, ci diferenta ca înainte de efectuarea asocierii din tabela a 2-a sunt selectate si sortate doar coloanele ce vor aparea în tabela de rezultate. Este folosita când cea de-a 2-a tabela nu are coloana de asociere indexata.
Asociere de tip merge: sorteaza cele 3 tabele dupa coloanele de asociere si le combina parcurgându-le simultan. Este folosita când cele 2 tabele tabele suntla fel de mari si rezultatele trebuie sa fie sortate în functie de coloane de asociere.
Asociere de tip index-merge: este similara unei asocieri de tip merge, cu diferenta ca, pentru a ordona cele 2 tabele, sunt folositi indecsii. Este aleasa atunci când ambele tabele au coloana de asociere indexata si cel putin una dintre cele 2 nu contine valoarea NULL.
II.4.7. Statistici
Pentru a putea evalua diferitele strategii de parcurgewre a tabelelor sau de asociere a tabelelor sau de asociere, motorul Jet Engine foloseste urmatoarele statistici asupra fiecarei tabele pe care se bazeaza interogarea:
numarul indecsilor din tabela:
numarul paginilor ocupate de tabela;
indecsi daca sunt unici;
numarul paginilor cu indecsi;
daca exista valoarea NULL în coloanele indexate.
II.4.8. Greseli frecvente
În continuare, va prezentam câteva dintre greselile cel mai des întâlnite la crearea interogarilor si care pot duce la marirea semnificativa a timpului de executie.
Expresii îmn coloanele rezultat: motorul Jet Engine nu poate optimiza interogarile care ciontin expresii de tip IIF pentru coloanele rezultat. Folositi expresiile la nivelul controalelor din rapoartele sau formularele care vor folosi rezultatele interogarii.
Prea multe coloane cu clauza GROUP BY: când creati o interogare de tip Totals, impuneti clauza Group By asupra cât mai putinor coloane. Astfel, timpul de executie va fi mai redus.
Indexarea coloanei de asociere pentru o singura tabela: când folositi o asociere între 2 tabele, indexati c-mpurile de asociere din ambele tabele.
Folosirea insuficienta a indecsilor: daca nu se efectueaza modificari frecvente asupra datelor, este bine sa folositi indecsi pentru coloanele de asociere sau pentru coloanele asupra carora se impun restrictii. Altfel, indecsii pot îngreuna sensibil operatiile de actualizare a datelor.
II. 4.9. Importanta compactarii
Compactarea unei baze de date este o operatie asemanatoare defragmentarii discurilor fizice. Pe masura ce stergeti obiecte sau înregistrari din baza de date, vor ramâne spatii nefolosite care maresc dimensiunile fisierului si scad performantele actiunilor. Singurul mod în care pot fi eliminate aceste spatii este comanda Database Utilities | Compact Database. Astfel, vor fi reînoite statisticile asupra tabelelor, vor fi reordonate înregistrarile din tabele, iar spatiul liber va putea fi folosit. În consecinta, vor creste si performantele interogarilor, despre care am mai spus ca sunt cel mai important instrument pus la dispozitia programatorilor si fara de care bazele de date nu si-ar mai afla utilitatea.
|
