ACCESS - Interogări cu Query Design

Acest seminar (exercițiu) folosește baza de date Bonuri.accdb, care are următoarele tabele:

Structura este după cum urmează:

Baza noastră de date conține Magazine; Magazinele au Bonuri; Bonurile conțin Produse. Deoarece un bon poate conține multe produse, iar un produs se

poate regăsi pe multe bonuri, avem tabela de legătură ContinutBon, care înregistrează produsele vândute pe fiecare bon și cantitatea în care s-a vândut fiecare produs.

Interogările sunt folosite pentru a extrage date din baza de date. Rezultatul unei interogări este întotdeauna o altă tabelă; de exemplu, o interogare care găsește lista bonurilor emise azi este tot o tabelă care conține detalii despre bonuri.

O intergoare se poate realiza fie:

Folosind interfața grafică -- Query Design; Folosing limbajul SQL -- Structured Query Language (despre care vom discuta mai târziu)

Cele două metode sunt echivalente (de fapt, metoda grafică folosește tot SQL).

Să analizăm următoarea interogare:

Obțineți lista cu numele și prețurile produselor care au prețul mai mic de 10 RON.

Pentru a realiza interogarea, trebuie să parcurgem următorii pași:

1. Să determinăm ce date trebuie să apară în rezultatul nostru. Mai precis, ce coloane trebuie afișate și din ce tabele provin aceste coloane?

2. Să determinăm condițiile pe care trebuie să le îndeplinească respectivele coloane.

Atenție: pașii de mai sus trebuie reținuți deoarece se aplică pentru orice interogare. Întotdeauna, când creați o interogare, întrebați-vă: ce coloane sunt necesare pentru a răspunde acestei interogări, și din ce tabele provin aceste coloane? Ce condiții trebuie să îndeplinească datele?

În cazul nostru, vrem să obținem:

1. Ca și coloane: Numele, Pretul – care provin din tabela Produs;2. Condiții: Pret < 10

Pentru implementare, din bara principală alegem CREATE Query Design. Apare fereastra cu tabelele disponibile, deoarece trebuie să specificăm din ce tabelă provin coloanele pe care dorim să le obținem în cadrul interogării.

Selectăm tabela Produs și apăsăm butonul OK.

În ecranul următor, dăm dublu-clic în tabelă pe coloanele Nume si Pret, pentru a le include ca rezultat al interogării. Ecranul trebuie să arate așa:

Oservăm următoarele:

În zona (1) se afișează tabelele din care extragem date pentru interogare; În zona (2) se afișează coloanele ce vor apărea în interogare și condițiile pe care trebuie să le

îndeplinească fiecare înregistrare pentru a fin inclusă în rezultat.

Acum trecem la Criteria, pe coloana Pret condiția <10, iar pe linia Sort alegem Descending pentru a ordona rezultatul descrescător (zona notata cu 2 în figura anterioară).

Pentru a rula interogarea apăsăm butonul ! Run (stânga-sus):

Acum se afișează rezultatul interogării:

Să observăm rezultatul interogării: o listă cu numele și prețurile produselor, ordonată descrescător după preț.

Acum, să presupunem că dorim să afișăm produsele cu prețul mai mare de 10 RON. Deci trebuie să edităm interogare, și navigăm din ecranul curent cu rezultatul interogării, în ecranul anterior, unde am definit interogarea. Avem ca de obicei mai multe posibilități:

1. Apăsăm View și alegem Design (Zona 1 din imaginea de mai sus) sau2. Folosim butoanele din dreapta-jos (Zona 2)

Intrați în modul design și modificați condiția astfel încât prețul >10 și rulați interogarea. Veți observa rezultatele aferente. Navigați înapoi în Design și schimbați la varianta inițială, <10.

Vom salva interogarea apăsând butonul de închidere X (1 în imaginea de mai jos), și trecem denumirea „01 – Produse cu pretul mai mic de 10” (2):

Interogarea trebuie să apară în zona unde se listează obiectele:

Dați dublu-clic pe interogare și aceasta se deschide, afișând rezultatele. Putem trece în modul de editare al interogării, salva modificările, etc. La final, închideți interogarea.

Acum dorim să creăm o nouă interogare, similară cu cea anterioară:

Să se afișeze codurile bonurilor care conțin produse cu prețul mai mic de 10, în cantitate mai mică de 5 bucăți.

Deci coloanele de care avem nevoie acum sunt:

CodBon | Nume | Pret | Cantitate

Observăm că interogarea este similară cu cea anterioară; în plus, trebuie să adăugăm coloanele CodBon și Cantitate care provin din tabela ContinutBon. Așa că vom crea o copie a interogării anterioare, și vom edita copia pentru a adăuga tabela ContinutBon din care vom preloa coloanele necesare. Urmăm pașii descriși în continuare:

1. Din lista de obiecte, clic dreapta pe interogarea anterioară (01 – Produse…) apoi Copy

2. Tot în lista de obiecte, clic dreapta și Paste. Modificăm denumirea astfel încât să fie „02 – Bonuri cu produse cu prețul mai mic de 10, sub 5 buc”. Am făcut acești pași pentru a duplica interogarea, deoarece este similară cu prima;

3. Intrăm în modul de editare al noii interogări, dând clic-dreapta pe noua interogare, apoi Design View:

1. Prin drag-drop tragem tabela ContinutBon în zona interogării (Zona 1 în imaginea de mai sus)

2. În tabela ContinutBon dăm dublu-clic pe CodBon și Cantitate pentru a le adăuga în zona de rezultate;

3. Selectăm coloana CodBon: clic pe zona gri din partea de sus a coloanei (3) și apoi o mutăm în stânga coloanei Nume;

4. La Cantitate, punem condiția < 5.

Rulam interogarea (butonul ! Run). Este posibil să avem coduri de bon care apar de mai multe ori, aceasta se întâmplă deoarece un bon poate conține mai multe produse cu pretul mai mic de 10 și în cantitate mai mică de 5. Pentru a obține valori unice pentru CodBon:

1. Clic în zona de tabele pentru a activa proprietățile interogării (Property Sheet)2. Alegem Unique Values3. Trebuie să debifăm Show pentru coloanele din tabela Produs (deoarece aceastea cauzează

duplicarea datelor) și să scoatem ordonarea după Pret (de la Sort).

Rulăm interogarea și observăm că acum se afișează valori unice pentru codurile de bonuri. Salvăm interogarea și închidem.

Felicitări! Acum este rândul Dvs.!

În continuare creați interogări pentru a afișa date conform cerințelor de mai jos. Salvați introgările folosind nume relevante, sub forma „Nr - Descriere interogare” Observație: Prefixarea interogărilor cu numere ne asigură că acestea se afișează în ordinea dorită de noi. În plus, este întotdeauna o idee bună să dăm nume relevante interogărilor, astfel încât colegii care lucrează la același proiect--și profesorii care evaluează exercițiile—să poată înțelege ușor menirea interogării respective! Și acum, interogările de rezolvat:

1. Numele magazinului unde s-a emis bonul 24 – folositi tabelele Magazin și Bon, condiție pe CodBon;

2. Numerele bonurilor emise în magazinul cu Cod Magazin 3;3. Numele mărfurilor vândute în anul curent--folosiți ca și criteriu Year([DataBon]) =

Year(Now()). Observație 1: am folosit paranteze pătrate pentru numele coloanei! Observație 2: de obicei în câmpul Criteria punem un operator de comparație (> < =) urmat de o valoare. În cazul de față am folosit o egalitate; să reținem această posibilitate!

4. Magazinele unde s-au vândut produse de panificație – folosiți criteriul LIKE "*Paine*". Explicație: dorim să găsim toate produsele al căror nume conține textul „Paine”, de aceea folosim LIKE și abrevierea *, care înlocuiește orice caracter. Alt exemplu: dacă dorim produse care încep cu textul „Paine…” vom folosi LIKE "Paine*" – acum caracterul * este doar după text.

5. Magazinele care au emis bonuri între iulie și septembrie 2013 – datele calendaristice se izolează folosind caracterul diez, în felul următor: BETWEEN #7/1/2013# AND #9/30/2013#

6. Produse cu prețul cuprins între 5 și 10 RON sau 20 și 30 RON. Explicație: aici avem două intervale, iar prețul produsului trebuie să se situeze în primul SAU al doile interval. Deci, la

Pret vom folosi criteriul BETWEEN 5 AND 10, și pe linia or: (imediar sub Criteria) adăugăm al doilea interval.

7. Aflați codurile bonurilor emise in ultimele doua luni. Atenție: acest gen de interogare se referă la ultimele două luni de la data execuției interogării! Deci trebuie să luăm ca punct de referință data curentă:

Așadar, datele pe care le căutăm sunt mai mari decât Now() – 2 luni. Totuși, nu putem pune condiția Month([DataBon]) > Month(Now())-2. Dacă suntem în luna Februarie, condiția va evalua dacă DataBon > 0 și va fi întotdeauna adevărată. În consecință toate bonurile vor satisface această condiție și vor apărea în lista de rezultate (incluzând bonurile emise după februarie!) Soluția este să folosim o funcție care returnează data calendaristică precisă a momentului cu 2 luni în urmă. Funcția este DateAdd(interval, numar, data) – uitați-vă în help sau Google la descrierea funcției, pentru a înțelege cum funcționează (important pt. examen). Condiția pe care o folosim pentru data bonului va fi: > DateAdd("m"; -2; Now()). Modificați apoi interogarea pentru a afișa bonurile din ultima lună, și observați că avem mai puține rezultate.

Observații:

Textele se includ în ghilimele Datele se includ folosind caracterul diez # Numerele se scriu direct, fără a folosi caractere pentru a le include Numele de coloane se izolează folosind paranteze pătrate: [Coloana]

Observație: în câmpul de criterii putem adăuga oricâte condiții legate prin operatorii logici AND și OR. Ordinea de verficare a condițiilor poate fi influențată prin folosirea parantezelor.

Operații și Coloane Sintetice

Dacă putem folosi funcții în zona de criterii, rezultă că vom putea folosi și operații matematice. Ne putem convinge creând o interoare simplă pe tabela Produs, cu condiția Pret > 5+5. Putem introduce operații oricât de complexe, influențând ordinea evaluării prin paranteze.

Operațiile pot fi folosite și pentru a adăuga în rezultatul interogării coloane noi, care nu există în tabelele originale. De pildă, vrem să afișam produsele și o coloană care să conțină prețul produsului majorat cu 10%. Creăm o interogare bazată pe tabela Produs, după cum urmează:

Am introdus coloanele Nume și Pret, apoi în a treia coloana am intrdous formula:

PretMajorat: [Pret]*1,1

Cuvântul PretMajorat reprezintă numele noii coloane, și este despărțit de formula propriu-zisă prin simbolul „:” (două puncte). Putem folosi orice denumiri dorim pentru noua coloană, dar în general este de preferat să folosim „notația cămilă”, ca și în cazul celorlalte nume de coloane sau variabile.

Rulăm interogarea și rezultatul este:

Observați a treia colană, care are ca rezultat operația [Pret] * 1.1. Dorim să formatăm această coloană ca și valută, astfel încân valorile să fie prefixate cu simbolul aferent (În cazul de față „$”, dar acesta poate fi determinat de setările regionale ale calculatorului). Ne întoarcem în Design View și:

1. Activăm Property Sheet (este posibil să fi fost deja activat).2. Selectăm coloana PretMajorat, pentru a-i modifica proprietățile.3. La Format alegem valuta relevantă. De asemenea, putem modifica celelalte proprietăți

pentru a modifica numărul de decimale etc.

Evident, în expresiile de calcul ale coloanelor putem combina mai multe coloane; vom exemplifica mai târziu când vom înmulți prețul cu cantitatea produselor pentru a calcula totalul unui bon.

Funcții de AgregareFuncțiile de agregare sunt folosite pentru a sumariza informațiile dintr-un set de rezultate. În esență, informația disponibilă pe mai multe linii ale rezultatului este sumarizată în mai puține linii.

Creați acum o interogare simplă, care afișează codurile tuturor produselor, și rulați-o. Rezultatul constă într-o singură coloană:

În cazul de mai sus aveam în baza de date 5 produse, și au fost afișate codurile lor. Acum întoarceți-vă în Design View și dați click pe Totals (1)

Observați că în zona coloanelor (2) apare linia Total, care ne permite să adăugăm funcții de agregare pentru coloane. Din lista derulantă, alegeți Count. Rulați apoi interogarea. În imaginea de mai jos am afișat în stânga rezultatul anterior, fără Count, iar în dreapta am afișat rezultatul nou:

Observăm că funcția Count, aplicată coloanei CodProdus, a sintetizat informația returnând o singură linie cu numărul total de produse. De asemenea, observați că numele coloanei a fost schimbat la CountOfCodProdus. Salvați acum interogarea folosind o denumire relevantă.

Exercițiu: creați o nouă interogare în care să găsiți suma prețurilor tuturor produselor. Folosim coloana Pret din tabela Produs și aplicăm funcția de agregare Sum. În mod similar putem găsi valorile medii, minime, maxime, deviația standard etc.

Agregare cu GrupareFuncțiile de agregare sunt deosebit de folositoare pentru sinteza datelor, atunci când le folosim pentru a afla valoarea unei proprietăți pentru diferite grupuri. În exemplul următor, vom găsi numărul total de produse de pe fiecare bon. Informația de care avem nevoie se găsește în tabela ContinutBon. Dați dublu-clic pe această tabelă pentru a observa înregistrările:

Vom folosi coloanele:

CodBon, pe care aplicăm funcția Group, pentru a crea „grupuri” în cadrul rezultatului. Pentru fiecare CodBon se crează câte un grup. În imaginea de mai sus avem 3 grupuri, corespunzător celor 3 bonuri diferite (24, 25, și 26).

Cantitate, pe care aplicăm funcția Sum, pentru a însuma numărul de produse din cadrul fiecărui grup creat pe CodBon. De exemplu, pentru bonul 24, numărul total de produse este 10 + 8 = 18.

Acum creați o nouă interogare bazată pe tabela ConținutBon, cuprinzând câmpurile CodBon și Cantitate, și activați butonul Totals.

Alegeți Group By pentru CodBon și Sum pentru Cantitate. Rulați interogarea și observăm rezultatul. În imaginea de mai jos am inclus și rezultatul anterior, în scop demonstrativ:

Observăm că pentru fiecare CodBon s-au însumat cantitățile de produse aferente. Salvați interogarea cu un nume relevant, și creați noi interogări pentru următoarele cerințe:

Observație: în interogările de mai jos, folosiți doar două coloane: una pentru grupare, și cealaltă pentru funcții agregate—chiar dacă aveți nevoie de mai multe tabele. Începeți prin a determina tabelele și coloanele necesare. Salvați fiecare interogare folosind nume relevante.

1. Numărul de bonuri emis de fiecare magazin.2. Numărul total de produse vândute în fiecare oraș.3. Numărul total de unități vândut din fiecare produs. Ordonați descrescător după numărul

total de unități, pentru a obține topul celor mai bine vândute produse.4. Similar cu interogarea de mai sus, ordonați crescător pentru a afla cele mai slab vândute

produse.5. Numărul total de produse vândute în fiecare lună calendaristică (grupați după

Month([DataBon]))

Agregare cu CriteriiSă revenim asupra interogării în care calculam numărul total de produse vândut pe fiecare bon. Dorim să găsim doar bonurile care conțin mai mult de 20 de produse, deci vom adăuga această condiție la Criteria:

Rezultatul va conține bonurile care îndeplinesc această condiție.

Acum dorim să modificăm interogarea noastră pentru a găsi pe fiecare bon, numărul total de produse care au prețul mai mare de 7. Intrăm în DesignView și:

1. Ștergem condiția >20 de la Cantitate.2. Adăugăm tabela Produs (drag-drop din lista de obiecte din stânga).3. În tabela Produs, dăm dublu-clic pe Pret pentru a-l adăuga în rezultat.

4. Pe linia Total, în dreptul coloanei Produs, alegem din lista derulantă Where. Astfel, specificăm că această coloană este folosită pentru o comparație (filtrare) și deci nu va apărea în rezultate. Observați că Access de-bifează automat Show din dreptul coloanei.

5. La Criteria, introduceți condiția >7.

Rulăm interogarea și rezultatul interogării trebuie să arate așa:

Observăm că acum avem mai puține bonuri, cu un număr mai mic de produse, deoarece prin filtrare am redus numărul de produse însumat pe fiecare bon.

În concluzie: cerințele interogărilor trebuie citite cu atenție, pentru a determina coloanele pe care punem condițiile. Dacă aceste coloane nu sunt cuprinse în rezultat, trebuie să alegem din funcțiile de agregare opțiunea Where.

Observație: Ca regulă generală, terbuie să avem o coloana cu funcția Group By, o altă coloană cu un rezultat agregat (Sum, Count, Average) și eventual mai multe coloane cu condiții.

Acum rezolvați următoarele interogări. (Observație: deoarece tabelele conțin—în mod intenționat—puține date, puteți vereifica manual rezultatele interogărilor).

1. Numărul total de bonuri emis după 1 noiembrie 2013. Observație: nu avem grupare! În zona de rezultat vom avea Count pe CodBon, și condiția aferentă datei calendaristice, cu Where.

2. Afișați prețul celui mai scump produs.3. Afișați prețul mediu al produselor care conțin textul „Paine”.4. Afișați numărul total de produse vândut în fiecare oraș, pentru produsele cu prețul cuprins

între 5 și 10 lei.5. Afișați magazinele cu cele mai mare număr de produse vândut în luna crentă.

Parametru de la TastaturaOdată salvate, interogările trebuie editate pentru a schimba valorile crtieriilor. Un mod mult mai efeicient de a face interogările mai flexibile este să solicităm utilizatorului valoarea unui parametru la momentul când se rulează interogarea. Pentru aceasta, introducem între paranteze pătrate textul care va fi afișat la momentul rulării. De exemplu, vrem să arătăm numele magazinului în funcție de un CodMagazin specificat de utilizator:

La Criteria în dreptul coloanei CodMagazin introduceți expresia:

[Introduceti codul magazinului]

Aceast text va apărea pe ecran la memntul rulării, iar valoarea introdusă de utilizator va fi folosită în cadrul interogării. Realizați acum interogarea de mai sus, rulați-o și apoi salvați-o.

Parametrii pot fi folosiți și pentru calcule în cadrul coloanelor. Realizați o interogare conform imaginii de mai jos, pentru a afișa prețurile majorate cu un procent introdus de utilizator:

Salvați interogarea și închideți-o.

Interogări Bazate pe Alte InterogăriSă presupunem că dorim să găsim produsele al căror preț este mai mare decât prețul mediu al tuturor produselor. Este evident că va trebui să adăugăm un criteriu pentru preț. Dar prețul mediu este obținut în urma agregării mai multor coloane, iar noi trebuie să comparăm prețul fiecărui produs cu pretul mediu. Deci nu putem să rezolvăm cerința introducând criteriul > Avg(Pret), deoarece Avg este calculat la finalul interogării, după ce rezultatele au fost extrase din baza de date.

Soluția este să realizăm mai întâi o interogare care calculează prețul mediu al produselor, apoi interogarea finală, care compară prețul produselor cu prețul mediu obținut în pasul anterior.

1. Realizați o interogare pentru a afișa prețul mediu al produselor, ca în imaginea de mai jos.

Schimbați denumirea coloanei astfel încât să fie PretMediu, și pe linia Total alegeți funcția Avg. Rulați interogarea și observați valoarea rezultatului, precum și numele coloanei:

2. Salvați interogarea, cu numele „Media Preturilor Produselor”.3. Creați o nouă interogare și alegeți ca surse de date tabela Produs și intergoarea Media

Preturilor Produselor (salvată anterior), conform imaginii de mai jos:

Pentru coloana Pret am introdus la Criteria condiția > [PretMediu], care provine din interogarea anterioară. (Să observăm că între tabelă și interogare nu există nicio relație, deoarece nu au chei comune). Rulați interogarea, obsevați rezultatul și apoi salvați.

Observație: când realizăm interogări bazate pe alte interogări, este foarte important:

Să comparăm aceleași tipuri de date, adică prețuri cu prețuri, coduri cu coduri etc; Să fim atenți la numărul de rezultate (linii) pe care le returnează sub-interogarea. În cazul de

mai sus, comparam valoarea prețului cu media prețurilor—adică verificăm dacă o singură valoare este mai mare decât o altă valoare. Dacă sub-interogarea ar fi returnat mai mult de o valoare ca rezultat, atunci comparația nu putea fi realizată (deoarece am fi comparat o

valoare cu mai multe valori) și interogarea nu se executa. Pentru a vă convinge, modificați interogarea cu prețul mediu și scoateți funcția Avg, apoi rulați din nou interogarea principală.

De acum devine evident că interogările pot fi deosebit de complexe, combinând criterii, grupări, sub-interogări etc. Vom reveni asupra sub-interogărilor în capitolul despre SQL.

Interogări de Modificare a DatelorÎn jargonul profesional, operațiile pe tabele se abreviază RCUD – Read, Create, Update, Delete. Până acum am discutat despre interogări de extragere a datelor (read); în continuare vom prezenta ștergerea și modificarea datelor.

Actualizare (Modificare) a Datelor (Update)În următorul exemplu vom mări cu 10% prețurile tuturor produselor care au TVA-ul mai mici de 20%. Înainte de a rula interogarea, să observăm produsele al căror preț va fi modificat:

1. Creați o nouă interogare bazată pe tabela Produs.2. Activați butonul Update, și completați zona de detalii conform cu imaginea de mai jos:

Atenție: numele coloanelor trebuie întotdeauna inclus între paranteze pătrate!

3. Rulați interogarea și confirmați modificarea datelor. După execuție, interogarea rămâne în același ecran. Pentru a observa datele modificate, trebuie să deschideți tabela Produs:

Acum închideți și salvați interogarea, apoi modificați prețurile la valoarea inițială (pentru a menține consecvența rezultatelor pe parcursul exercițiului).

Deoarece următoarele cerințe modifică ireversibil datele, creați o copie a bazei de date, închizând-o și apoi duplicând fișierul.

Realizați interogări pentru următoarele cerințe, pe fișierul de rezervă:

1. Să se modifice prețurile produselor cu un procent introdus ca parametru de la tastatură.2. Să se reducă cu 15% prețul celor mai slab vândute produse în anul curent (ultimele două

produse). Aici avem nevoie de o sub-interogare care să găsească CodProdus pentru cele mai slab vândute produse. Rezultatul trebuie limitat la 2 produse, modificând proprietățile interogării (Top Values). Experimentați până obțineți codurile de produs corecte. Apoi, interogarea principală (care modifică prețurile) trebuie să pună condiția CodProdus IN (rezultatul sub-interogării).

Interogări de ȘtergereAtenție: nu există Undo pentru ștergerea datelor din tabele! Citiți cu atenție alertele emise de Access la rularea interogărilor de ștergere! O interogare fără niciun fel de restricție poate șterge deoadată câteva tabele fără posibilitatea de a le recupera. De aceea e esențial să testăm interogările de ștergere pe copii de siguranță (backup) ale bazei de date. Atenție cu interogările de ștergere în special pe bazele de date din sistemele de producție!

În continuare vom adăuga o înregistrare în tabela Produs, după care o vom șterge.

1. Deschideți tabela Produs și adăugați un nou produs. Notați-vă valoarea lui CodProdus, deoarece îl vom folosi pentru ștergere.

2. Închideți tabela Produs și creați o nouă interogare, bazată pe tabela Produs. Apăsați din bara de instrumente butonul Delete și configurați interogarea ca în ecranul de mai jos, astfel încât să ștergem produsul cu CodProdus = 7.

3. Rulați interogarea. Apare următorul mesaj. Citiți-l cu atenție!

Access ne atenționează că vom șterge o înregistrare, și că nu avem opțiunea Undo.

4. Dați clic pe Yes pentru a confirma ștergerea. Mesajul de atenționare dispare și revenim în ecranul Design View al interogării. Deși nu este aparent, ștergerea s-a realizat. Pentru a vă convinge, deschideți tabela Produs și observați că produsul a fost șters.

5. Salvați și închideți interogarea.

Acum vom aborda un caz mai complex. Să presupunem că (după ce am efectuat o copie de rezervă a bazei de date) vrem să ștergem toate bonurile mai vechi de o lună, pentru a preveni umplerea discului cu date vechi.

1. Închideți baza de date și creați o copie, apoi deschideți copia.2. Creați o nouă interogare bazată pe tabela Bon.3. Nu apăsăm încă butonul Delete din bara de opțiuni. Pornim prin a crea mai întâi o interogare

de selecție, pentru a ne asigura că obținem doar înregistrările care trebuie șterse. Configurați interogarea ca în imaginea de mai jos:

Funcția DateAdd returnează momentul în timp situat la o lună înaintea datei curente, deci comparația cu acest moment ne asigură că vom obține bonurile mai vechi de o lună.

4. Rulați interogarea și observați că datele calendaristice ale bonurilor rezultate sunt mai vechi de o lună. Notați-vă și numărul de înregistrări returnate de această interogare. Apoi întoarceți-vă în Design View.

5. Acum activați butonul Delete din bara de opțiuni, și rulați interogarea. Apare fereastra de confirmare, care ne anunță numărul de înregistrări ce vor fi șterse. Acest număr trebuie să fie același cu numărul de înregistrări returnat la pasul anterior (3).

6. Apăsați Yes pentru a confirma ștergerea. Apare un nouă fereastră:

Mesajul ne anunță că interogarea nu poate fi executată din cauză de încălcare a regulilor referitoare la chei, și din zero motive legate de încălcarea regulilor de încuiere (blocare) a scrierii în tabele (vom explica mai târziu despre regulile de blocare). Deci, problema este cauzată de încălcarea regulilor referitoare la chei. Dar care sunt aceste reguli? Dacă apăsați Help e posibil să le găsiți. Mai simplu este să căutați în Google textul „access delete key violations”. Pentru a economisi timp, însă, iată explicația:

7. Apăsați No pentru a renunța la execuția interogării.8. Salvați interogarea și închideți-o, pentru a putea reveni mai târziu.9. Din bara de opțiuni, accesați DATABASE TOOLS > Relationships. În imaginea de mai jos avem

relațiile:

Să ne aducem aminte că fiecărei înregistrări din tabela Bon îi corespund mai multe înregistrări în tabela ContinutBon, pentru a marca produsele și cantitățile vândute pe bonul respectiv. Dacă ștergem un bon, atunci în tabela ContinutBon bon vor rămâne înregistrări orfane—adică fără corespondent în tabela Bon. În imaginea de mai jos am afișat în stânga tabela de bonuri. Cele tăiate cu linie roșie vor fi șterse (CodBon 24 și 25). În dreapta avem tabela ConținutBon și observăm produsele vândute pe bonurile 24 ș 25.

Dacă s-ar efectua ștergerea, ar rămâne în baza de date vânzări despre care pierdem informația despre data calendaristică a vânzării (DataBon) și nu vom ști nici în ce magazin au fost vândute respectivele mărfuri (dacă ștergem bonul pierdem informația despre CodMagazin).

Access însă refuză să șteargă bonurile din cauza modului în care am definit relația între tabele:

10. Dați clic-dreapta pe relația dintre tabelele Bon și ConținutBon, apoi Edit Relationship... Apare ecranul de editare al relației:

Când am creat relația între tabele, am bifat Enforce Referential Integrity. Această opțiune asigură că pentru o cheie externă, există o valoare corespondentă a cheii primare. În alte cuvinte, pentru orice înregistrare din ConținutBon există un Bon valid. Ori dacă am fi șters bonuri, am fi încălcat această regulă. Din acest motiv, Access nu a permis ștergerea. Soluția este:

11. Bifați opțiunea Cascade Delete Related Records. Această opțiune va șterge înregistrările aflate în relație cu bonurile. Adică, la ștergerea unui bon se vor șterge automat și înregistrările corespondente în tabela ConținutBon, astfel încât baza de date să rămână într-o stare consistentă, fără înregistrări orfane.

12. Apăsați OK pentru a edita relația, apoi închideți ecranul Relationships. 13. Rulați din nou interogarea de ștergere, apoi deschideți tabela Bon pentru a verifica dacă s-au

șters bonurile.

Acum este rândul Dvs:

Ștergeți magazinele din București. Observație: trebuie modificată relația dintre Magazin și Bon.

Pentru a continua, închideți baza de date pe care ați exersat ștergerile, și deschideți baza de date care conține toate înregistrările.

Observație: întotdeauna când ștergem înregistrări dintr-o bază de date aflată într-un sistem de producție (live) există consecințe! În general, datele din sistemele live se arhivează, fără a se șterge definitiv. În cazul nostru:

Dacă ștergem un produs, îi pierdem prețul și nu vom mai putea calcula corect valoarea bonurilor

Dacă ștergem un bon, rămân înregistrări „orfane” în ContinutBon, etc.

De aceea, este necesar să planificăm impactul ștergerii înregistrărilor.

Interogări CrosstabÎn continuare dorim să aflăm cantitatea totală de vânzări pentru fiecare produs, în funcție de zona geografică (oraș). Observați că avem grupare în funcție de două criterii (Produs, Oraș) și funcția de agregare Sum(Cantitate). Acest tip de cerință se realizează cu o interogare Crosstab.

1. Creați o nouă interogare în care preluați toate tabelele. 2. Dați clic pe butonul Crosstab din bara de meniuri, și configurați interogarea ca în imaginea

de mai jos:

3. Rulați interogarea. Rezultatul trebuie să arate așa:

Rezultaul arată totalul cantității vândute din fiecare produs, în fiecare oraș. Acum să analizăm modul în care am definit cele 3 elemente din interogare (antetul liniei, antetul coloanei și valorile din tabel):

Pentru Row Heading (antet linie) am ales Oras, folosind funcția de agregare Group By. Pentru Column Heading (antet coloană) am ales Produs, folosind funcția de agregare Group

By. Pentru Value (valoarea efectivă prezentată în tabel) am ales Cantitate și funcția de agregare

Sum.

Interogările Crosstab sunt deosebit de importante pentru analiza datelor. De obicei, aceste interogări sunt incluse în rapoartele pentru management și pot folosi ca punct de plecare pentru fundamentarea deciziilor tactice și strategice, cum ar fi introducerea sau excluderea unei linii de produse, crearea unei politici de prețuri, extinderea geografică etc. De obicei, acest tip de interogări se realizează pe bazele de date agregate la nivelul unui teritoriu geografic extins și pe o perioadă de timp semnificativă, astfel încât concluziile să fie relevante.

Realizați interogări Crosstab pentru următoarele cerințe:

1. Cantitatea totală de produse vândute în fiecare lună (coloane), în funcție de Oraș (linii).2. Prețul mediu al produselor vândute în fiecare lună (coloane), pe fiecare magazin (linii).