Interogarea bazei de date ACCESS

Programul ACCESS pune la dispoziția utilizatorilor mai multe metode de extragere a informațiilor din bazele de date. Aceste metode se bazează pe conceptul de interogare query (interogare).

Interogările sunt mici programe care comandă extragerea adăugarea sau modificarea datelor dintr-un tabel. Interogările pot fi independente sau înglobate în formulare sau rapoarte, însă toate sunt construite cam în același mod.

Interogarea este necesară atunci când nu avem nevoie de tot tabelul ci numai de articolele (sau poate numai de anumite părți din articolele) care prezintă anumite particularități, adică îndeplinesc anumite criterii pe care știm precis că nu toate articolele le pot îndeplini. Desigur nu ne oprește nimeni să facem o interogare și pe toate articolele din tabel, dar acesta ar fi un caz particular. Înterogarea în sine nu este altceva decât o comandă de genul "Selectează articolele care îndeplinesc condiția . și fă cu ele .", unde condiția este descrisă foarte clar, iar ce să facă cu ele, iarăși este precizat foarte bine. În aceste condiții, interogarea poate căpăta un nume și poate fi salvată (stocată) și apelată ori de câte dorim. Access stochează interogările în două moduri total diferite: prin grila QBE (Query By Example) și în limbaj SQL (Standard Query Language). Cum situația din baza de date se schimbă în timp, este normal ca și rezultatele obținute cu o anume interogare, chiar dacă ea s-a păstrat intactă, să difere de la o etapă de timp la alta (exceptând cazul când între timp nu s-a mai schimbat nimic în baza de date). Răspunsul la o interogare mai poate să fie diferit de la o rulare la alta și pentru că interogarea poate fi concepută să fie aplicată pe fișiere asemănătoare, dar diferite ca semnificație. De exemplu aceeași interogare poate fi aplicată pe soldul de la magazia 1, dar poate fi aplicată și pe soldul de la magazia 2. 121g66b În acest caz numele tabelului va fi un parametru pentru acea query.

Interogările care extrag date din tabele creează obiecte similare tabelelor (ca aspect și funcționare). În afară de interogările de tip Select, în Access pot fi folosite și alte tipuri de interogări:

- Interogări Delete ( de ștergere) și Update (de actualizare), care șterg sau modifică articolele ce îndeplinesc condițiile impuse prin interogare;

- Interogări Append (de adăugare în tabel) și Make-Table (care creează un tabel), care crează date noi și le adaugă la tabele existente sau creează tabele noi;

- Interogări Crosstab (încrucișate), care centralizează datele extrase din tabele contorizând și combinând datele din mai multe câmpuri sursă în unul sau mai multe câmpuri de ieșire.

Crearea interogărilor cu ajutorul grilei QBE

Acesta este modul implicit de a crea interogări. Pentru aceasta vom proceda stfel:

- In fereastra Database vom selecta Query și vom da clic pe New. Ca urmare pe ecran va apare caseta de dialog New Query, care ne va cere precizări în legătură cu metoda de generare a interogării.

- Vom alege Design View apoi Ok când va apare caseta de dialog Show Table și grila QBE, care pot fi văzute pe pagina următoare. Să remarcăm că o interogare se poate aplica nu numai pe un tabel ci și pe rezultatul unei alte interogări, care așa cum am precizat mai sus, se poate prezenta tot sub formă de tabel.

După cum se vede din caseta Show Table putem selecta numai Table, numai Queries sau amândouă (Both). După selectare se apasă Add și apoi Close pentru a elibera grila QBE. Această grilă este formată din două părți: cea de sus pentru sistemul de relații dintre tabele și cea de jos pentru a forma grila interogării. Această grilă conține în mod implicit șase rânduri. Putem scrie oriunde în aceste rânduri, folosind fie lista ascunsă (combobox), dacă este prevăzută, fie generatorul de expresii (Expression Builder) ­- o casetă de dialog ce ne oferă operatori și operanzi adecvați tabelelor ce vor face obiectul interogării. Rândurile din grila QBE sunt folosite astfel:

Field (câmp). Aici se introduc numai denumirile câmpurilor asupra cărora avem de gând să formulăm condiții de departajare între articole, câmpurile a căror conținut dorim să apară în rezultatul interogării, precum și câmpurile după care să se sorteze tabelul, în vederea interogării. Denumirile se introduc în acest rând fie prin tragere cu mouse-ul din zona superioară a grilei QBE, fie prin dublu clic.

Tot în linia câmpurilor (Field) se introduc și expresiile, dar cu condiția ca ele să aibă o intrare pe linia Total (care va fi prezentată la secțiunea Extragerea datelor folosind interogări SELECT), sub forma Expression. În expresii, numele câmpurilor apar între paranteze drepte, iar numele de tabele și interogări sunt scrise normal cu excepția cazului în care numele conțin spații, când numele vor fi cuprinse între ghilemele.

- Tabel este numele sursei de date pentru interogare (un tabel sau o query);

- Sort (ordinea de sortare). Se completează cu ascending, descending sau not sorted. Sortarea se aplică asupra câmpului afișat deasupra ordinii de sortare. Pot fi sortate oricâte câmpuri din grilă.

- Show (Afișare). Casetele de pe acest rând sunt validate automat, dar când câmpul respectiv este folosit doar pentru sortare și/sau criterii, putem să-l deselectăm.

- Criteria (criterii) Criteriile se introduc în celulele de pe acest rând sub forma unor șiruri separate prin cuvântul AND (adică se aplică simultan, nu alternativ). Evident criteriile trebuie să fie corelate cu tipul datelor stocate în câmpul respectiv. Astfel pentru un câmp de tip Yes/No putem pune condiția =False, dar pentru un câmp numeric această condiție este incompatibilă. De remarcat că se admit și forme ca Like A*, însemnând toate articolele care în câmpul respectiv conțin valori ce încep cu A, restul poate fi orice.

- Or (sau) unde se introduc, criterii alternative la cele de pe rândul criteria. Criteriile se aplică asupra câmpului din coloana respectivă.

O interogare se rulează selectând butonul Run de pe bara cu instrumente. Revenirea în modul de afișare Design, se face cu butonul View de pe bara cu instrumente.

Help on line se obține, dând clic în rândul care ne interesează și apăsând apoi tasta F1.

Extragerea datelor folosind interogări SELECT

Aceste interogări reprezintă tipul implicit pentru care este pregătit Access, când lansăm Query Design. Tabelul rezultat pe baza unei astfel de interogări poate fi folosit ca suport pentru un formular, un raport sau o altă interogare.    

În acest tip de interogări, în grila QBE se mai folosește o linie și anume linia Totals care se obține dând clic pe butonul cu același nume de pe bara cu instrumente.

Rândul Total apare pe locul trei și acolo se pot introduce tot felul de funcții statistice cum ar fi Sum, Avg, StDev, Var, funcții care gestionează gruparea datelor (Last, First), sau funcții care introduc expresii complexe în interogări (Expression).

Expression evidențiază faptul că intrarea din linia câmpului este o expresie care include funcții statistice sau orice altă expresie. De exemplu Numeîntreg: [nume] & " " & [prenume] sau vânzări:Sum([linie comandă].[pretunitar]*[cantitate]).

Remarcăm că Expression ocupă pe linia Total locul ce ar putea fi ocupat de o funcție statistică, cum ar fi Sum, dar cu Expression pus pe linia Total, Sum (sau oricare altă funcție), poate fi inclusă în expresia trecută pe linia Field ca în fereastra de mai jos:

Mai poate fi introdusă opțiunea Grouped By care realizează o grupare a articolelor după valorile din câmpul a cărui nume se găsește în linia Fields.

Aceste funcții se pot obține dintr-o listă ascunsă ce poate fi activată pe linia Total, din orice coloană, dând clic în extremitatea dreaptă a casetei situate la intersecția liniei Total cu coloana ce ne interesează.

Funcția selectată se aplică asupra valorilor din câmpul a cărui nume este scris în coloana unde a fost plasată funcția sau după caz, asupra întregului articol, dar ele nu acționează asupra înregistrărilor care în câmpul ce face obiectul funcției conțin valoarea Null.

De exemplu funcția Count doar numără articolele, nu execută operații asupra unui câmp anume, dar oricum aceste funcții acționează numai asupra articoleleor care au trecut testul impus de criteriul din linia Criteria.

Așa cum Expression se referă la linia Field, pentru a preciza că acolo este o expresie și nu un câmp, funcția Where se referă la linia Criteria indicând faptul că intrării din linia câmpului i s-a aplicat un criteriu ce se aplică articolelor, înainte ca ele să fie incluse într-un total sau într-o grupare. Where este o precizare la criteriul de grupare! Câmpul pentru care s-a selectat opțiunea Where, nu va apare în rezultatul interogării.

Odată introdus un criteriu, se pot introduce criterii adiționale pe același câmp sau pe alte câmpuri.

Când se introduc expresii de tip criteriu, în mai multe celule de pe linia Criteria, Access le combină folosind operatorii OR sau AND. Dacă expresiile sunt pe același rând, ele sunt legate cu AND. Dacă expresiile sunt pe rânduri diferite, între rânduri se consideră OR.

În grila QBE, momentul când se execută calculele depinde de locul unde am amplasat criteriul și anume:

- pentru ca înainte de a face calculele să grupăm articolele pe categorii (de exemplu pe țări, eventual și companii - aici intervine și ordinea amplasării câmpurilor) în linia Total, vom introduce Group By.

- pentru a selecta rezultatele după ce am făcut calculele, în coloana câmpului implicat în calcule (prin operații specificate pe linia Total), pe linia Criteria, vom introduce un criteriu privitor la rezultatele calculului. Este cazul câmpului ExtendedPrice din exemplul alăturat, unde se calculează suma acestui câmp, pentru toate articolele care au trecut filtrul cu ShipCountry, dar din ele se vor afișa doar sumele mai mici de 10000.

- pentru a limita înregistrările înainte ca ele să fie grupate și înainte de a se face calcule, vom include în grilă și câmpurile pentru care se vor defini criteriile de limitare, iar criteriile se vor introduce pe linia Criteria ca în cazul câmpului Ship Country din exemplul de mai sus, unde pentru ShipCountry se admit doar valorile Canada sau UK.

Dacă în interogare se execută calcule folosind linia Total (în exemplul de mai jos se face o sumă pe Extended Price) și vrem să limităm intrarea articolelor în calcule (de exemplu numai la cele cu Extended Price sub 500), vom deschide o nouă coloană pentru câmpul implicat în operatia de pe linia Total, iar criteriul pe cea de a doua apariție a câmpului , îl vom introduce cu ajutorul clauzei Where, plasată pe linia Total. Remarcăm că un criteriu de limitare a întrării articolelor în calcul ar putea fi provenit și din condiții impuse asupra altui câmp decât cel implicat în calcule. În acest caz, nu va mai fi o a doua apariție a câmpului implicat în operații ci în locul său, deci pe coloana unde se va introduce clauza Where, va fi introdus numele câmpul implicat în condiție.

Expresiile prin care se exprimă criteriile de pe linia Criteria, se pot introduce prin tastare sau folosind Constructorul de Expresii (Expression Builder). Pentru a obține Constructorul de Expresii, dați clic dreapta în celulă și apoi clic pe Build.

Dacă interogarea include tabele puse în relație, valorile ce le specificăm în criteriile privitoare la câmpuri, sunt sensibile la litere mari și mici, adică valorile din criteriu trebuie să se potrivească cu cele din tabele și la caractere (contează dacă sunt scrise cu litere mari sau mici).

Exemple de expresii ce folosesc drept criteriu valori de tip text

Denumire câmp Expresie Descriere

(pe linia Field) (pe linia Criteria)

OrașDestinație "London" Afișează comenzi de expediere la

Londra

OrașDestinație "London" Or "Hedge End" Folosește operatorul Or pentru a

afisa comenzi de expediere la

Londra sau Hedge End.

DataExpedierii Between #1/5/95# And #1/10/95# Folosește operatorul Between

.And. pentru a afișa comenzi de expediere cuprinse între 5 Ian.

95 și 10 Ian.95

DataExpedierii #2/2/95# Afișează comenzile expediate pe

2 Febr. 95

Țarădestinație    In("Canada", "UK") Numele Tăriidestinație să se afle

printre elementele mulțimii

[Canada, UK]. In este un operator

Țarădestinație    Not "USA" Folosește operatorul Not pentru a

afișa comenzile expediate oriunde

cu excepția USA

DenumireCompanie >="N" Afișează comenzi expediate la

companiile ale căror nume încep

cu litere de la N la Z.

NrCrtComandă    Right([NrCrtComandă], 2)="99" Folosește funcția Right pentru a

afișa comenzi ale căror număr

curent (cheie) se termină în 99.

DenumireCompanie    Len([DenumireCompanie]) Folosește funcțiile Len și Val

>Val(30) pentru a afișa comenzi pentru

companiile a căror denumire este

mai lungă de 30 de caractere

Exemple de expresii ce folosesc drept criteriu părți de cuvinte

Denumire câmp    Expresie Descriere

NumeDestinatar    Like "S*" Comenzi expediate clienților ale

căror nume încep cu litera S

NumeDestinatar    Like "*Imports" Comenzi expediate clienților ale

căror nume se termină cu

cuvântul "Imports".

NumeDestinatar    Like "[A-D]*" Comenzi expediate clienților

a căror nume începe cu litere de

la A la D.

NumeDestinatar    Like "*ar*" Comenzi expediate clienților a

căror nume include secvența de

litere "ar".

NumeDestinatar     Like "Cristea Ion??" Comenzi expediate clienților

cu numele de Cristea iar prenu-

mele începe cu Ion, dar se ter-

mină cu două litere necunoscute

Exemple cu expresii care folosesc câmpuri cu valoare necompletată ( șir de lungime zero sau valoarea Null)

Denumire câmp Expresie Descriere

Județuldestinație Is Null Afisează comenzile pentru clienții ale căror

câmpuri privitoare la Județuldestinație

sunt blanc sau conțin valoarea Null.

Județuldestinație Is Not Null Afisează comenzile pentru clienții ale căror

câmpuri privitoare la Județuldestinație

conțin o valoare.

Fax " " Afisează comenzile pentru clienții care nu au fax

situație indicată printr-un șir de lungime zero,

plasat în câmpul Fax în loc de o valoare Null

sau blanc cum se pune la cei care au fax.

Exemple de expresii care calculează sau manipulează date calendaristice și apoi folosesc rezultatele drept criteriu

Denumire câmp Expresie Descriere

Datacerută Between Date( ) And Folosește operatorul Between .And,

DateAdd("m", 3, Date( )) funcțiile DateAdd și Date pentru a afișa comenzi cerute între data de astăzi și trei luni în urmă.

Datacomenzii < Date( )- 30 Folosește funcția Date pentru a afișa

comenzi mai vechi de 30 de zile

Datacomenzii Year([Datacomenzii])=1996 Folosește funcția Year pentru a afișa

comenzi cu datacomenzii în 1996

Datacomenzii DatePart("q", [Datacomenzii])=4 Folosește funcția DatePart

pentru a afișa comenzi pentru trimestrul IV

Datacomenzii DateSerial(Year Folosește funcțiile DateSerial, Year și

([Datacomenzii]), Month pentru a afișa comenzi pentru

Month([Datacomenzii])+1, 1)-1 ultima zi a fiecărei luni.

Datacomenzii Year([Datacomenzii])= Folosește funcțiile Year și Month

Year(Now()) And precum și operatorul And pentru a Month([Datacomenzii])= afișa comenzi pentru luna și anul

Month(Now()) curent

Exemple de expresii care folosesc funcții agregat (funcții statistice ce se aplică pe un domeniu definit)

Funcțiile agregat sunt:

Davg: calculează media unui set de valori asociate unui set de articole (domeniu)

Sintaxa: Davg(expr, domain[,criteria])

unde expr poate fi un identificator de câmp ce conține valori numerice, domain poate fi un nume de tabel sau query și criteria poate fi o expresie șir folosită pentru a restrânge domeniul de aplicare a funcției.

Dcount: determină numărul de articole ce se află într-un set specificat de articole.

Sintaxa: Dcount (expr, domain[,criteria])

Dlookup: aduce valoarea unui câmp anume dintr-un set specificat de articole

Dfirst, Dlast: aduce un articol aleatoriu dintr-un câmp al unui tabel sau query, când se dorește orice valoare din acel câmp.

Dmin, Dmax: determină valorile minimă și maximă dintr-un set de articole.

DstDev, DstDevP: estimează deviația standard pe mulțimea de valori a unui set de articole. Exemplu: DStDevP("[Freight]", "Orders", "[ShipCountry] = 'UK'" calculează deviația standard pentru populația comenzilor trimise către UK.

Dsum: calculează suma mulțimii valorilor dintr-un set specificat de articole.

Dvar, DvarP: estimează varianța aplicată pe mulțimea valorilor dintr-un set specificat de articole.

Exemple de expresii care folosesc funcții agregat

Denumire câmp Expresie Descriere

Exemple de expresii care folosesc drept criteriu chiar rezultatul unei subquery

Denumire câmp Expresie Descriere

Exemple de queries de tip Select

II. Elaborați o interogare care să ne prezinte situația intrărilor de la începutul anului, având câmpurile: nr_NIR, data_NIR, cod_mat, cantitate, pret de achiziție.

III. Elaborați o interogare care să ne prezinte situația intrărilor totale, pe materiale, de la începutul anului, având câmpurile: cod_mat, total_cant_intrata.

IV. Completați tabelul STOC folosind o interogare concepută de Dv. care va fi rulată pentru fiecare material și pentru fiecare gestiune.

Problema se rezolvă în două faze: În prima fază se analizează situația din punct de vedere logic, iar în a doua fază se transpune logica cu privire la modul de interogare, în modul de operare a grilei QBE.

- faza 1: analizând structura tabelelor MATERIALE, MATERIALE CONSUMATE și NIR, precum și legăturile dintre aceste tabele, determinate de cheile lor primare și externe, putem observa că pentru o magazie (gestiune), a calcula întrările (pentru firecare material) pe baza Nir-urile cu care am completat baza de date pentru anul 2000, înseamnă a selecta materialele după COD_MAT din [MATERIALE], pentru fiecare material făcând sumă de CANTITATE [MATERIAL_APROVIZIONAT], folosind în acest scop relația MATERIALE COD_MAT MATERIALE_APROVIZIONATE NR_NIR NIR, unde COD _GESTIUNE=1

Structura tabelelor implicate în această interogare ca și legăturile dintre ele, sunt reprezentate pe pagina următoare.

- faza doua constă în a traduce obiectivul propus în faza întâia, în regulile de exprimare folosite în grila QBE.

Imaginea grilei care rezolvă acest obiectiv este dată mai jos. Din imagine se vede că inițial s-a solicitat o grupare a materialelor din tabelul MATERIALE pe coduri, ceea ce este foarte simplu, pentru că aceste coduri sunt unice, dar în cadrul acestei grupări, articolele corespunzătoare, deci cele din tabelul MATERIALE_APROVIZIONATE, vor fi grupate fiecare pe codul venit din tabelul MATERIALE. Totusi la această grupare care în cadrul unui cod de material se concretizează și cu o însumare (Sum), nu sunt admise decât înregistrările a căror Nr_NIR ne duc în tabelul NIR, la un Cod_Gestiune egal cu 1 (evident pentru magazia 2 Cod_Gestiune va trebui să fie egal cu 2 , ș.a. m.d.)

Rulând de trei ori interogarea de mai sus, deci odată pentru fiecare gestiune, vom obține trei tabele pe care le vom putea folosi la completarea tabelului STOC. Desigur această soluție este una de început, deoarece se poate concepe o interogare care să ia în calcul și soldul existent la data începerii evidenței din tabelul materiale aprovizionate, chia dacă soldul la acea dată era unul diferit de zero. Pentru simplificare, în exemplul nostru, soldul la data începerii evidenței, era zero la toate materialele, astfel că soldul calculat cu interogarea este și soldul absolut pentru fiecare material în parte.

V. Completați câmpul valoare factură folosind o interogare proiectată de Dv. în acest scop. În această problemă vom lua fiecare factură (Group By) și pentru fiecare factură, vom parcurge toate articolele din MATERIALE_APROVIZIONATE. Dacă Nr_Nir din articolul luat din MATERIALE_APROVIZIONATE, ne duce la un NIR, al cărei NR-Factură este egal Nr-Factură pentru care calculăm în acel moment valoarea facturii, atunci articolul din MATERIALE_APROVIZIONATE, va contribui la creșterea valorii facturii prin faptul că produsul Preț_Achiziție*Cantitate, va fi însumat la valoarea factu-rii, Val_Factură. Rezultatul acestui raționament este concretizat în grila QBE de mai jos:

Returnarea valorilor superioare dintr-o interogare

De exemplu vrem să vedem primele 5 facturi având valorile cele mai mari. Formularea SQL a unei asemenea interogări este dată în imaginea alăturată. Pentru aceasta am elaborat normal interogarea cu grila QBE, doar că am făcut o sortare după câmpul valoare din factură, în ordine descrescătoare (descending), folosind linia Sort. În timp ce mă aflam cu cursorul în coloana acestui câmp, am selectat butonul    și de acolo am ales valoarea 5. Acest buton oferă posibilitatea să alegem primele 5, 25, 100 articole sau 5%, 25% și All (toate înregistrările). Atenție! Sortarea trebuie făcută în concordanță cu ordinea cerută de acest tip de interogare, dacă sortarea s-a făcut după alte câmpuri sau în ordinr inversă decât trebuie rezultatele vor fi incorecte.

Expresii și câmpuri calculate

Dacă este cazul, în loc de câmpuri putem folosi expresii care implică câmpuri, iar dacă asupra acelor expresii trebuie să efectuăm operații cu funcții statistice ca Sum, Avg, etc. putem specifica funcția în linia Total. Dacă o astfel de funcție este cazul să fie implicată chiar în expresia câmpului calculat, atunci pe linia Total se specifică Expression.

Dacă totuși în privința câmpului calculat ar trebui specificat ceva în linia Total, atunci pe coloana următoare se ve relua câmpul calculat, de astă dată doar cu numele, iar în linia Total, putem pune funcția de care este nevoie.

Tipuri de interogări. După cum se știe în Access în afară de interogări de tip Select, mai există și alte tipuri de interogări. Tipul de interogare se poate alege de la începutul proiectării interogării sau se poate schimba ulterior. În ambele cazuri există două posibilități de alegere a tipului de interogare:

- prima din meniul Oeury, alegând opțiunea dorită;

- a doua alegând butonul Query Type de pe bara cu instrumente care vă oferă o listă ascunsă cu tipurile de interogări ca cea din imaginea alăturată.

Crearea și folosirea interogărilor Delete (de ștergere)

O interogare Delete elimină înregistrări din tabelul de bază. Este bine ca înainte de a rula o astfel de interogare să facem o salvare a tabelului de bază și să simulăm rularea interogării, afișând interogarea în modul de afișare Datasheet, pentru a anticipa efectele interogării. În modul de afișare Datasheet, se poate intra din modul Design, alegând meniul View și opțiunea Datasheet. Dacă suntem de acord să se șteargă articolele pe care le-am văzut acolo, vom reveni în modul Design și apoi vom alege butonul Run din bara de instrumente a Query Designer-ului. Urmează un mesaj de avertizare și dacă s-a selectat butonul Yes, înregistrările respective vor fi șterse.

În principiu, o astfel de interogare șterge numai înregistrările care îndeplinesc condițiile impuse asupra câmpurilor specificate în linia Field. Dacă se dorește ștergerea tuturor informațiilor dintr-un tabel, se va deschide interogarea în modul de afișare Design și în final, steluța din panoul Relationship, aflat deasupra grilei QBE, va fi trasă în linia Field, sau se va da pe ea dublu clic. Dacă nu am specificat de la început că dorim să creem o interogare de tip Delete, este timpul s-o facem acum, folosind opțiunea Delete din meniul Query sau butonul Query Type, prezentat pe pagina anterioară.

În acel moment liniile Sort și Show vor fi înlocuite cu linia Delete. Câmpurile asupra cărora vrem să impunem criterii vor fi plasate pe coloanele următoare, iar criteriile vor respecta regulile generale privitoare la criterii așa cum au fost ele prezentate la interogările de tip Select.

În afară de criteriile impuse asupra înregistrărilor ce vor fi șterse, mai trebuie să ținem seamă de relațiile dintre tabelul ale cărui înregistrări vor fi șterse și alte tabele.

Astfel, dacă acest tabel este implicat prin cheia sa primară, într-o relație cu alt tabel (copil, primul fiind tabel părinte) și opțiunea Cascade Delete Related Records a fost activată în fereastra Edit Relationship, încă de la proiectarea bazei de date sau ulterior, la ștergerea unui articol din tabelul părinte, toate articolele din tabelul copil, a căror cheie (considerată externă față de fișierul părinte), au aceeași valoare cu cheia primară a articolului șters, vor fi șterse. Dacă opțiunea Cascade Delete Related Records, nu este activată, Access nu va da voie să ștergem articole din tabelul părinte, până ce nu vom șterge articolele din tabelul copil, corespunzătoare articolelor ce vor fi șterse din tabelul părinte. Dacă am avut de gând să ștergem doar articole din tabelul părinte și opțiunea Cascade Delete Related Records era activată, am pierdut articolele din baza de date copil pe care nu voiam să le ștergem. Deci atenție, mare atenție!

Crearea și utilizarea interogărilor Update (de actualizare)

Acestea operează aproape în același mod ca și interogările de tip Delete, doar că efectul lor în ce privește relațiile între tabele va depinde de opțiunea Cascade Update Related Fields ce se poate activa tot din fereastra Edit Relationship. Practic cu această interogare se poate selecta un subset de înregistrări în vederea actualizării. De fapt interogarea aceasta face și modificările specificate, iar rezultatele interogării nu pot fi anulate decât restaurând întreaga bază de date, așa că și înainte de rularea unei astfel de interogări, va trebui să salvăm tabelul, sau după caz baza de date ce va fi afectată.

Ce fel de modificări face o astfel de interogare? Sunt modificări ce pot fi exprimate prin valori specifice câmpurilor. De exemplu putem înlocui un furnizor cu altul, selectând toate articolele referitoare la primul și apoi înlocuind codul primului cu al celui de al doilea.

Când alegem acest tip de interogare liniile Sort și Show vor fi înlocuite cu linia Update To, unde se va introduce noua valoare a câmpului ce urmează a fi modificat. Evident câmpul trebuie adus în linia Fields, iar în coloana lui sau după caz a altui câmp, trebuie să introducem criteriu de selectare pentru actualizare. Astfel în exemplul cu furnizorii în coloana cod_furnizor, vom introduce la criteriu codul vechiului furnizor, iar în linia Update To, vom introduce codul noului furnizor. Deoarece vom dori să previzualizăm efectul interogării în modul de afișare Datasheet, în exemplul de mai sus am vedea doar coduri de furnizori, ceea ce nu ne-ar edifica supra efectului actualizării. De aceea ar trebui să implicăm în query și unele câmpuri care nu trebuie modificate, cum ar fi nume_furnizor. Alt motiv pentru care putem introduce câmpuri ce nu fac obiectul modificării este nevoia uneori de a face selectări pe criterii ce se aplică altor câmpuri decât cele ce urmează a fi modificate. Indiferent de motiv, câmpurile care apar într-o astfel de interogare dar nu trebuie modificate, vor avea prevăzut, în coloana lor, pe linia Update To, denumirea câmpului pusă între paranteze drepte, adică le modificăm și pe ele, dar defapt nu le modificăm, în schimb vor apare și ele în Datasheet view, pentru ca noi să vedem mai clar cui se aplică modificările cerute. În exemplul cu furnizorii, dacă am renunțat la un furnizor, în afară de modificarea câmpului furnizor, din tabelul Produse, probabil că va trebui șters funizorul din tabelul Furnizori, lucru ce se poate face direct în modul DataSheet selectând înregistrarea și ștergând-o cu tasta Delete sau folosind o interogare de tip Delete, ceea ce ar fi exagerat!

Adăugarea unor înregistrări într-un tabel existent cu ajutorul interogărilor Append

La selectarea acestui tip de interogare, suntem solicitați să dăm numele tabelului la care se vor alipi înregistrările furnizate de interogare; linia Show este înlocuită cu Append To, iar linia Sort rămâne în grilă. Înterogare se traduce prin "Insert în tabelul .ceea ce vom selecta cu criteriile. din tabelul.doar de acolo unde este îndeplinită condiția."

Pe linia Append To se vor introduce numele câmpurilor care în procesul de adăugare de articole, vor primi valori, adică acolo se vor introduce câmpuri din tabelul destinație, în timp ce sus, pe linia Field, vom avea denumiri de câmpuri din tabele sursă.

Mare atenție trebuie acordată câmpurilor cheie primară (în cazul în care nu sunt de tipul Autonumber), în sensul că printre câmpurile din tabelul destinație, ce vor primi valori în procesul de adăugare de articole, trebuie să fie și câmpul cheie primară, dacă un asemenea câmp există. Evident că aceste valori nu vor fi acceptate decât dacă nu duplică cheile existente. La adăugare sunt verificate și regulile de validare în sensul că la câmpuri omoloage ele trebuie să fie aceleași.

Adăugarea unor înregistrări într-un tabel nou cu ajutorul interogărilor Make-Table

Acestea operează la fel ca și interogările Select, exceptând faptul că atunci când selectăm tipul Make-Table, ni se cere numele tabelului ce va fi creat. Grila QBE rămâne ca la interogările de tip Select. La rulări repetate, Access șterge tabelul din rularea precedentă și îl crează din nou, ceea ce impune atenție să nu dăm unui tabel astfel creat, numele unui tabel de bază din compunerea bazei de date pentru că vom rămâne fără el. Odată selectat tipul Make-Table, restul decurge ca la orice interogare de tip Select. Ceea ce este specific acestui tip de interogare este faptul că înserează înregistrările selectate într-un tabel nou.

Crearea și utilizarea interogărilor Crosstab (încrucișate)

Pentru a folosi o interogare Crosstab, datele din sursa de date trebuie să aibă următoarele caracteristici:

- să existe o valoare care se repetă în mai multe înregistrări, evident în același câmp;

- să existe un câmp care se pretează la centralizare (cantități vândute, sume încasate, etc.);

- să existe un câmp pentru care se poate face centralizarea ( de ex. o dată calendaristică sau o zonă geografică);

O interogare Crosstab, centralizează datele într-un tabel de bază, sortându-le pe linii și coloane unde:

- titlurile de linii conțin numele elementelor/datelor care trebuie centralizate (produse individuale, sau agenți de vânzări);

Termenul de linie derivă dintr-o structură arborescentă a unor date, care în final, după dezvoltarea arborelui se intersectează cu o coloană (una singură), dar care poate fi defalcată pe mai multe coloane (subcoloane). trim I trim II trim III trim IV

clienți anul

produs

De exemplu;

În acest exemplu , dezvoltând produsele după nume, apoi după clienți și în final după ani, va rezulta câte un rând pentru fiecare sfârșit de ramură a arborelui. Acel rând se va inter-secta cu o coloană, de exemplu cu total vânzări (defalcat pe trimestre). De fapt despre aceste rânduri este vorba, dar în tabel titlurile lor apar ca titluri de coloane, deoarece liniile sunt valori ale aceleeși coloane. Astfel din structura de mai sus, va rezulta tabelul:

- titlurile de coloane conțin o descriere a modului în care sunt centralizate datele (pe date calendaristice, sau pe regiuni);

- celulele de la intersecțiile liniilor și coloanelor conțin datele centralizate.

Interogarea Crosstab, Quarterly Orders byProduct, elaborată pentru trei tabele a căror machetă completă este vizibilă în imaginea din stânga, reflectă structura arborescentă de mai sus.

În acest tabel denumirea unui produs se repetă pentru fiecare client. Sunt selectate numai vânzările pe 1995, defalcate trimestrial.

Grila QBE, pentru această interogare este dată mai jos sub forma a două fragmente, deoarece s-a dorit să se prezinte expresiile complete care au stat la baza acestei interogări

Privind structura arborescentă de mai sus, se vede clar cum se justifică apartanența coloanelor ProductName, CustomerID și OrderYear la categoria denumiri de rânduri.

Echivalentul SQL al acestei interogări este următorul:

TRANSFORM Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount

SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS OrderYear

FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID

WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#))

GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])

PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");

Pașii pentru elaborarea grilei QBE a unei interogări Crosstab, după ce s-a selectat tipul interogării și s-au selectat tabelele și câmpurile implicate în interogare sunt următorii:

- Pentru câmpurile a căror valoare dorim să apară ca denumire de rând, vom da clic pe rândul Crosstab și apoi clic pe opțiunea Row Heading din lista ascunsă care apare. Pentru aceste câmpuri, în rândul Total vom lăsa opțiunea implicită Group By.

- Pentru câmpurile a căror valoare dorim să apară ca denumire de coloană, vom da clic pe rândul Crosstab și apoi clic pe opțiunea Column Heading. Vom putea alege Column Heading numai pentru un câmp, iar în rândul Total, pentru acel câmp, vom lăsa opțiunea implicită Group By. Implicit, denumirile de coloane sunt sortate în ordine alfabetică. Modificări se pot face cu proprietatea ColumnHeadings.

- Pentru câmpurile a căror valoare dorim s-o folosim în tabelare încrucisată, vom da clic în rândul Crosstab si apoi pe optiunea Value. numai un câmp poate fi pus pe optiunea Value.

In linia Total a acestui câmp vom selecta tipul de funcție agregat (Sum, Avg, sau Count) pe care o dorim pentru tabulare încrucișată.

- Pentru a specifica criteriile de limitare (filtre) pe denumiri de rânduri, înainte de a se efectua calculele, vom introduce expresii/filtre în linia Criteria pentru un câmp calificat în celula Crosstab drept Row Heading. De exemplu putem afișa total vânzări pentru produsele dintr-o anumită categorie, cum ar fi peste, carne, etc.

- Pentru a specifica criteriile de limitare a înregistrărilor, înainte ca denumirile de rânduri să fie grupate și înainte ca să se execute tabularea încrucișată, vom adăuga câmpul pentru care dorim să introducem criteriu în grilă, vom da clic pe linia Total în dreptul acelui rând, lăsăm celula Crosstab goală, și apoi vom introduce un criteriu (expresie) în rândul Criteria.

Tehnici avansate de extragere a datelor cu ajutorul interogărilor

Extragerea datelor din mai multe tabele

Valoarea reală a interogărilor constă în capacitatea acestora de a relaționa date din surse de date diferite și de a le returna sub forma unui tabel unitar. Să ne amintim de interogarea propusă pentru calculul adaosului la stoc, în cazul materialelor intrate în gestiunea unei magazii pe baza articolelor introduse de noi în tabelul Materiale Aprovizionate. Pentru a realiza acea interogare a fost nevoie de trei tabele: Materiale, Nir și Materiale Aprovizionate: se lua pe rând câte un material din nomenclator și se căuta prezenta sa în articolele din Materiale Aprovizionate. Când se găsea acolo un articol bun, se verifica în tabelul Nir, dacă Nir-ul cu care a venit trimitea materialul la gestiunea pentru care calculam adaosul la stoc. Dacă răspunsul era afirmativ, materialul se însuma la cantitatea rezultată din însumarea articolelor bune precedente, iar în final acela era surplusul de stoc pentru materialul curent.

Pentru a folosi mai multe tabele ca bază pentru o interogare, tabelele trebuie puse în relație sau asociate. Asocierea (Join) este fuzionarea a două tabele pe baza unei chei comune. De regulă este vorba de o cheie primară (ceea ce atribuie tabelului rolul de tabel părinte) și o cheie externă, care nu trebuie să fie cheie primară, dar să aibă aceeași semnificație și proprietăți ca și chie primară. Cheia externă conferă tabelului din care face parte, rolul de tabel copil, și definește o relație de tip una la mai-multe.

De regulă, în manualele de baze de date, acest tip de relație între bazele de date s-a descris cu tabela părinte în stânga și cea copil în dreapta, relația plecând de la tabelul părinte/sursă spre cel copil/destinație.

Între două tabele se pot defini două tipuri de relații: internă (Inner Join) și externă (Outer Join). Prin operația bazată pe Inner Join se extrag toate înregistrările din tabela sursă care au înregistrări echivalente în tabela de destinație (pentru care câmpurile de legătură sunt identice).

Prin operația bazată pe Outer Join se extrag toate înregistrările din tabela sursă, iar din tabelul destinație numai acele înregistrări a căror cheie este egală cu o cheie din tabelul sursă. Problema este că aici prin sursă și destinație nu mai înțelegem părinte și copil, ci tabela luată ca reper prin adăugarea termenului de stânga sau dreapta. Astfel există Left Outer Join, când sursa este tabelul părinte, dar există și Right Outer Join când sursa este tabelul copil. Remarcăm că alternativa de a pleca din tabelul părinte Left Outer Join cu un articol și de a găsi în tabelul copil cel puțin un articol cu cheia externă având aceeași valoare cu cheia primară aleasă, se poate încheia fără nici un articol. De exemplu avem un nomenclator cu toate materialele posibile, dar multe dintre ele încă nu au fost procurate niciodată. În acest caz, pentru astfel de materiale, în tabelul copil (Materiale aprovizionate), nu vom găsi nici un articol. În cazul articolelor din tabelul părinte care nu au corespondent în tabelul copil, câmpurile corespunzătoare tabelului copil, vor rămâne goale.

Mai există și categoria asocieri intrinseci (self joins), care se realizează pe câmpuri aparținând aceluiași tabel. Aceste câmpuri folosesc valori cu aceeași semnificație, dar cu rol diferit. De exemplu în tabelul Angajați avem un câmp numit cod_pers, dar și un câmp numit șef-direct, care nu poate lua valori decât din câmpul cod_pers. În aceste condiții între câmpul cod_pers și șef_direct se poate crea o relație intrinsecă sau self Join.

Concluzionăm că relațiile dintre tabele pot fi de trei tipuri și anume: inner join, outer join și self join. Când vrem să creem o interogare pe baza a mai multe tabele, din fereastra Data Base, vom selecta Queries/New/Design View/Ok. Apare caseta de dialog Show Table, alegem tipul de sursă de date pentru interogare (Tables, Queries sau Both)

și apoi selectăm tabelele sau interogările de care avem nevoie. Eliberând caseta Show Table, în partea de sus a grilei QBE, mai exact în panoul Relationship vom avea macheta fiecărui tabel sau interogare apelată. Dacă între aceste tabele sau interogări nu au fost stabilite relații permanente încă de când s-a terminat descrierea tabelelor, sau nu ne convin unele din aceste relații, le putem șterge pe acestea din urmă și putem crea alte relații dar temporare. Cu alte cuvinte nici ștergerile de relații permanente și nici crearea de relații în panoul Relationship din grila QBE, nu afectează relațiile permanente create între tabele cu opțiunea Relationship din meniul Tools. Același lucru este valabil și pentru cazul că am șters din panoul Relationship un tabel apărut accidental acolo (un tabel de care nu aveam nevoie). O astfel de operațiune se poate face dând clic dreapta pe tabelul respectiv și apoi selectând opțiunea Remove Table din meniul imediat care apare.

În aprecierea relațiilor permanente vom ține seamă dacă ele impun restricții de integritate referențială sau nu, ceea ce în caz afirmativ, se poate vedea prin îngroșarea capetelor liniilor de relație dintre tabele și prin marcarea lor cu 1 sau cu ¥

Pentru a schimba tipul de asociere selectați legătura printr-un clic de mouse apoi executați dublu clic pe legătură pentru a deschide caseta de dialog Join Properties.

Din această casetă se poate alege unul din tipurile Inner Join, Left Outer Join sau Right Outer Join.

Dacă între două tabele sau interogări nu există legături permanente și nu avem ce modifica, putem crea o relație temporară procedând ca și în caseta Relationship folosită la declararea relațiilor permanente, adică trăgând câmpul cheie primară spre câmpul cheie externă. Odată rezolvată problema unor legături adecvate între tabele sau interogări, putem trece la redactarea interogării după toate regulile specifice prezentate la tipul de relație pe care dorim să o creem. Reamarcăm că odată stabilite legăturile între tabele și aduse câmpurile în linia Field, ele pot fi folosite de programator (și vor fi tratate de Access) ca și cum ar aparține aceluiași tabel!

Utilizarea unor interogări în cadrul altor interogări

Există cazuri în care este necesară o interogare pentru a genera o valoare ce va fi folosită de altă interogare. Aceste interogări pot fi construite împreună sau separat.

Când sunt construite separat, după ce s-a elaborat și testat prima interogare, o vom aduce în panoul Relationship deschis pentru a doua interogare, în linia Table vom introduce numele interogării, iar în linia Fields vom introduce nume de câmpuri din interogare. Tabelele de care are nevoie prima interogare nu trebuie să fie aduse împreună cu ea, în panoul Relationship al celei de a doua interogări.

O altă modalitate de folosire a unei interogări vechi în una nouă este de a introduce în expresii criteriale sau de altă natură chiar textul SQL al vechii interogări. Acest text se obține având interogarea veche deschisă în mod Design și apoi alegând din meniul View, modul de vizualizare SQL. Textul poate fi copiat în clipboard și depus în grila QBE a noii interogări. În acest caz nu este necesară prezența tabelului produs de vechea interogare în panoul Relationship al grilei QBE a noii interogări.

În exemplul de mai jos avem o interogare care listează produsele a căror preț este peste prețul mediu. Pentru a calcula prețul mediu se folosește o interogare al cărei echivalent în SQL este folosit direct în linia Criteria pentru a genera limita pentru care un preț poate fi selectat ca fiind peste prețul mediu. În acest caz, prețul mediu putea fi calculat și direct cu funcția agregat Davg().

Utilizarea parametrilor în interogări

Parametri sunt folosiți pentru a obține seturi de criterii redactate ad-hoc de cel care a lansat interogarea. De exemplu, nu știm pentru ce țară va dori utilizatorul să obțină lista cu comenzi pentru luna curentă. Totul a fost prevăzut în grila QBE, mai puțin criteriul privitor la țară. Acolo, în loc să se pună o valoare concretă ca în exemplele anterioare când am avut "Canada" or "UK", se va pune între paranteze drepte mesajul prin care cerem numele tării. De exemplu vom pune =[Numele tării] sau într-o altă situație în care se cere un interval de timp vom pune: Between [Data de inceput]and [Data de sfarsit] .

Când se rulează interogarea, se afișează o casetă de dialog ce afișează textul pus între paranteze drepte ca o întrebare către utilizator și o casetă de text, în care utilizatorul poate completa răspunsul său la întrebare, respectiv poate completa criteriul care a cerut această

informație. Dacă ni se pare greu de utilizat caseta de text din caseta Enter Parameter Value, o putem mări cu Shift+F2, când va apare caseta Zoom. Remarcăm că un text pus între paranteze drepte pentru a fi apoi afisat ca intrebare, este limitat la 40 de caractere. Această limitare se poate ocoli cu un formular personalizat special destinat preluării parametrului respectiv.

Unele interogări, cum ar fi cele de tip CrossTab, cele care folosesc câmpuri de tip Yes/No drept criterii, interogările care depind de criterii preluate din surse de date ODBC (Open DataBase - Conectivity), precum și cele care crează diagrame, cer tipul de date al parametrilor. În acest caz, vom crea interogarea normal, după regulile de mai sus, dar în timp ce ne aflăm în modul Design, vom alege opțiunea Query de pe bara de meniuri, iar din submeniul afișat, vom alege Parameters. Va rezulta o casetă Query Parameters cu coloanele Parameter și Data Type care ne permite să specificăm tipul parametrului. Acolo la rubrica Parameter vom introduce numele câmpului pentru care se cere parametrul.

Înțelegerea și configurarea proprietăților interogării

Proprietățile interogărilor pot fi precizate și modificate din submeniul View, opțiunea Properties . Pentru a configura proprietățile unei interogări va trebui să selectăm interogarea dând clic pe fundalul panoului Relationship.

În fereatra cu proprietăți carea apare, cele mai reprezentative sunt:

- Output All Fields (implicit No): Yes determină returnarea tuturor câmpurilor din toate tabelele și interogările sursă ;

- Unique Value (implicit No): Yes determină ca toate câmpurile din toate înregistrările returnate să conțină valori unice;

- Unique records (implicit No): se referă la înregistrări identice. La Yes astfel de înregistrări sunt permise la iesirea din interogare;

- Sourse Database/source Connect Str (baza de date sursă/parametrii de conectare la sursă ; implicit=Current); are sens când interogăm baze de date ce nu sunt legate la baza de date curentă din Access, ci la baze de date externe;

- Recordset Type (implicit Dynaset); are sens când folosim o interogare ca sursă de date pentru un formular și vrem să împiedicăm utilizatorii formularului să modifice datele prin formular;

- ODBC Timeout (timp de așteptare pentru conectare la sursa ODBC; implicit 60 sec.);

- Filter. Filtrele sunt folosite pentru a limita numărul de înregistrări returnate de interogarea curentă, în funcție de situație. Concret este vorba de condițiile impuse pentru clauza Where.

- Order By; se enumeră aici numele câmpurilor (separate prin virgule), după care se vor ordona rezultatele interogării.

- Max Records (nr. max. de înreg.), reglementează numărul maxim de înregistrări care să fie preluate de la interogare pentru a fi trimise surselor ODBC.

- Column Headings: utilă într-o interogare Crosstab, pentru a specifica coloanele din tabelul de date returnat de interogare, prin intermediul programelor, deci automat după nevoi;

- Destination Table/DB/Connect Str (tabel de dest./bază de date/parametri de conectare): utilă în cazul interogărilor Make-Table; când nu se găsește un tabel destinație în Baza de date. curentă, se folosește calea și opțiunile necesare pentru a-l crea într-o bază de date externă;

- Use Tranzactions (implicit No): La opțiunea Yes nu scrie rezultatele până nu este completat tot tabelul de iesire; utilă atunci când acesta trebuie difuzat la mai mulți utilizatori și dacă ar fi No, ar ține ocupate toate liniile pe tot timpul cât se fac calcule pentru elaborarea rezultatelor.

- Fail On Error (implicit No). La Yes anulează operația dacă întâlnește o eroare. În cazul interogărilor de actualizare și de ștergere, și articolele procesate până la producerea erorii vor fi aduse la forma inițială, deci insuccesul să fie total.

Notă: valorile întroduse pentru proprietăți, se pierd la schimbarea tipului de interogare;