Interogari in Access – laborator 4

Din fereastra Database putem alege dintre cele 2 optiuni de a crea interogari asupra bazei de date:

Create query in Design viewCreate query by using wizard Vom lucra cu fiecare dintre cele doua optiuni.

Create query by using wizard Asa cum “spune” si optiunea vom folosi un Expertul pentru a crea interogarile de care avem nevoie.La alegerea optiunii se va deschide o fereastra in care avem posibilitatea sa alegem din fiecare tabele ce campuri dorim a fi afisate in urma interogarii. Presupunem ca vom dori sa aflam numele agentilor, clientilor, si al produselor comandate:

- din fereastra Simple Query Wizard vom alege pe rand fiecare tabela in parte

- apoi aleg campul/rile dorite din acea tabela

Dup ace veti termina de ales campurile din tabele in urma finalizarii operatiei de interogare va rezulta o un table in care vor fi afisate valorile campurilor selectate.In acest mod nu se pot realize decat interogari foarte simple asupra tabelelor.Cu ajutorul acestei optiuni se pot face si cateva calcule foarte simple ca SUM, AVG, MAX,MIN si COUNT dupa anumite campuri numerice.Alegeti optiunea Summary din fereastra Simple Query Wizard inainte de a finalize interogarea si apoi pe Summary Options:

La pasul urmator alegeti operatia dorita:

Create query in design viewLa alegerea acestei optiuni se va deschide o fereastra de unde puteti alege tabelele pentru care veti efectua interogarile dumneavoastra:

Sa incercam sa realizam exemplele de mai inainte: sa alegem numele agentilor, al clientilor si al articolelor care au fost comandate.Pentru aceasta vom alege in campurile: - Fields - campul corespunzator din fiecare tabela in parte;- Table – tabela corespunzatoare;- Sort – dupa caz ascending sau descending pentru un anumit camp;- Show- stabileste aparitia sau nu a unui camp in tabela rezultata- Criteria – aici se va scrie un eventual criteriu de selectie

Pentru a rulaun query trebuie sa apasati pe Run query:

Rezultatul va fi o tabela cu campurile selectate.Pentru a va intoarce in modul design pentru a realize o alta interogare sau a o modifica pe cea realizata apasati View:

In campul Field putem de asemenea sa folosim si campuri calculate. De exemplu vrem sa vedem clientii de la ce agenti si ce produse au comandat impreuna cu valoarea lor finala care se obtine din pretul produsului * cantitatea comandata:

Rezultatul va fi acesta:

Daca analizam expresia: valoare finala: [articole].[art pret]*[comenzi].[cant] se poate deduce ca:- valoare finala reprezinta eticheta sau numele campului calculate care va apare in tabela rezultata;- fiecare camp este apelat prin sitanxa: <nume tabela> . <nume camp>. Folosinde-se operatorul “.” se arata

astfel apartenenta campului la acea tabela. Parantezele patrate sunt folosite pentru a grupa numele compuse asa cum avem in exemplul nostrum in cazul campului: art pret ce semnifica articol pret. Accessul va pune implicit “[ ]” pentru fiecare nume de table si camp folosit. In final inmultirea dintre pretul unui articol care se afla in tabela articole si cantitatea comadata, camp care se afla in tabela comenzi este:

[articole].[art pret] * [comenzi].[cant]

Interogari in Access – laborator 5 (continuare)

Pentru a realiza diferite criterii de selecţie se poate utiliza şi generatorul de expresii (Expression Builder) a cărui fereastră se deschide selectând opţiunea Build a meniului pe care îl activaţi printr-un click dreapta de mouse în rândul Criteria.

De exemplu vrem sa selectăm doar agentii care au primit comision. Asta înseamnă ca valoarea din comision să fie mai mare decât 0. Interogarea va fi creată cu ajutorul generatorului de expresii.Se face click dreapta sub câmpul comision in rândul Criteria si se allege Build:

La sfârşit se apasă butonul OK. Rulaţi interogarea.

Alegeţi acum toţi clienţii care sunt din oraşul Bacău sau Iaşi. Pentru a nu scrie în rânduri separate ale câmpului Criteria numele fiecărui oraş, se va scrie într-un singur rand Bacău şi Iaşi folosindu-se operatorul OR.

Utilizarea operatorilorPentru a construi expresii pe rândul Criteria se utilizează operatorii:

aritmetici: adunare (+); scădere (–), înmulţire (*), împărţire (/), ridicare la putere (^), împărţirea a două numere cu returnarea unui întreg (\), împărţirea a două numere cu returnarea restului împărţirii (MOD).

de comparaţie: <, >, =, <=, >=. Aceşti operatori returnează valorile logice True şi False.Excepţie reprezintă cazul în care unul dintre operatori are valoarea NULL şi deci orice comparare va returna valoarea NULL.

asociaţi operatorilor de comparare:– IS NULL, IS NOT NULL – o valoare NULL (câmp necompletat) nu este nici TRUE nici FALSE. Înregistrările care au valoarea NULL în câmpurile selectate nu apar ca rezultate ale interogării;– LIKE – se foloseşte împreună cu caracterele de înlocuire „* ” şi „?” pentru a stabili dacă o valoare începe cu unul sau mai multe caractere; caracterul „* ” poate înlocui orice număr de caractere; caracterul „? ” înlocuieşte numai un caracter;– IN – stabileşte dacă o valoare este cuprinsă într-o listă;– BETWEEN – stabileşte dacă o valoare aparţine unui interval specificat.

logici:– NOT – negaţia;– AND – pentru conjuncţia a două valori;– OR – pentru disjuncţia a două valori;– XOR – pentru disjuncţia exclusivă a două valori;– Eqv – verifică echivalenţa a două valori.

de concatenare a şirurilor de caractere: + şi &. constante: constantele utilizate în construirea expresiilor Access pot fi de natură

– numerică (ex: 1200,5,0);– text (“123”, “Toma Ion”, ’str. Viilor 15’);– dată calendaristică (ex: #12.31.01# ceea ce indică data de 31 decembrie 2001). Funcţiile: pot fi de natură:

– Dată calendaristică : Date(), Month(), Year (), etc.Exemple:

1. Date() – returnează data curentă;2. Month(Date()) returnează numărul lunii calendaristice curente.3. Year(Date()) returnează anul curent.

– De tip text:1. Len() – returnează lungimea unui şir; 2. Trim() – elimină spaţiile de la începutul şi de la sfârşitul unui şir; 3. Left() – returnează primele n caractere de la începutul unui şir, etc.

– Matematice şi trigonometrice: 1. ABS() – returnează valoarea absolută a unui număr;2. INT() – returnează partea întreagă dintr-o valoare numerică, ROUND() – rotunjeşte

o valoarecu un anumit număr de zecimale;

3. SUM() – calculează suma; 4. AVG() – calculează media, etc.

– Financiare: 1. PV() returnează valoarea actuală a unei unităţi plătite în rate periodice egale;2. SLN() returnează valoarea amortizării unui mijloc fix după o anumită perioadă

(amortizareliniară) etc.

– Funcţii diverse: ISNUMERIC(), ISNULL(), etc

Reguli de formare a expresiilor introduse pe câmpul Criteria:– datele de tip Text se tastează ca atare, iar Access adaugă automat ghilimele;– pentru datele de tip Number şi Currency se tastează cifrele şi eventual simbolul zecimal, fără

simbolul monetar sau separatorul de mii;– referirile la numele de câmpuri trebuie incluse între paranteze drepte, altfel se adaugă automatghilimele, considerându-se text;– formatul internaţional de dată calendaristică este mm/dd/yy. Access adaugă automat delimitatorul # ;

Exerciţii:Creaţi trei interogări în care să folosiţi la alegere unul din operatorii descrişi mai sus.

Exerciţii:1. Realizarea unei interogări pentru obţinerea unei liste cu clienţii a căror numele începe cu A.

Sub câmpul [cl nume] pe rândul Criteria se scrie expresia: LIKE “ A* ”2. Realizarea unei interogări pentru obţinerea unei liste cu clienţii a căror numele NU începe cu A.

Sub câmpul [cl nume] pe rândul Criteria se scrie expresia: NOT LIKE “ A* ”3. Realizarea unei interogări pentru obţinerea unei liste cu clienţii care nu au reducere (nu aţi scris

nimic în acest cîmp nici măcar valoare 0).Sub câmpul reducere pe rândul Criteria se scrie expresia: IS NULL

4. Realizarea unei interogări pentru obţinerea unei liste cu clienţii din oraşul Bacău care nu au reducere sau dacă au să fie mai mare ca 10 şi a căror nume începe cu litera A.

Pe rândul Criteria sub câmpul [cl oraş] se scrie “Bacau”, iar sub câmpul reducere se scrie expresia: IS NULL OR [reducere] > 2 şi sub câmpul [cl nume] se scrie Like “A* ”.

5. Interogarea pentru obţinerea listei clienţilor cărora li s-a acordat o reducere între valorile 10 şi 15. Sub câmpul reducere în rândul Criteria se scrie expresia: >="2" And <="5". Pentru date calendaristice se va folosi expresia: >= # 01.12.2004 # And <= # 31.12.2004 # ce reprezinta perioada cuprinsă între 1 decembrie 2005 şi 31 decembrie 2004.

6. Pentru a obţine o listă a clienţilor cu [cl Id] dintre numerele 1, 2,3, se va realiza interogarea: Sub câmpul [cl Id] se scrie expresia: IN (1 ; 2 ; 3).

7. Pentru a obţine o listă cu clienţii cu reducere intre valorile 5 şi 10 se scrie: Between 1 And 3.8. Mergeţi în structura tabelei Clienţi şi adăugaţi câmpul: data de tipul Date/Time şi daţi apoi valori

acestui camp. Aflaţi apoi clienţii care au făcut comenzi între perioada 01.01.2006 şi 31.12.2007.Sub câmpul data pe rândul Criteria veţi scrie: Between #01.01.2006# And #31.12.2007#.

Interogări în Access – laborator 6 (continuare)

Revenire asupra câmpurilor calculate din interogările de selecţie.se introduce în celula Field a grilei de interogare un nume de coloană (dacă nu sespecifică se atribuie numele implicit Expr l, Expr2, ...), urmat de semnul “ : ” şi formulade calcul, astfel:

stoc_final: [stoc_initial] + [Cant_intrata] - [Cant_iesita](cu precizările: au fost folosite doar numele de câmpuri şi nu s-a mai scris în faţa lor şi numele tabelei urmată de semnul „ .” sau semnul „ ! ”).

• câmpuri calculate pot fi create şi pentru text (concatenarea câmpurilor):Numepren: [Nume] & “ ” & [ Prenume]

• câmpurile calculate pot fi sortate, li se pot aplica criterii de selecţie sau se pot totaliza.• în câmpurile calculate se poate utiliza funcţia IIF cu următoarea sintaxă:

IIF ( <expresie>, valoare 1, valoare2)

unde:– <expresie> – este o expresie a cărei valoare de adevăr este evaluată pentru fiecare înregistrare;– valoare 1 – este valoarea returnată dacă expresie este adevărată;– valoare2 – este valoarea returnată dacă expresie este falsă.

Exemple pentru fiecare:1. Calculaţi TVA-ul pentru fiecare articol în parte:

Rezultatul este:

2. Să realizăm o interogare în care să calculam pentru fiecare agent in parte, şi fiecărui client căruia s-a furnizat un articol, valoarea articolului furnizat (din pret * cantitate), TVA-ul, valoarea finală (din valoare + TVA) şi apoi să verificăm cât anume a plătit clientul scăzând din suma finala (câmpul din comenzi) valoarea finală calculată.

Rezultatul interogării este unul de forma:

După cum se vede numele de coloană sunt corespunzătoare cu identificatorii scrişi pentru fiecare expresie calculată:Valoare, TVA, valoare finala şi Suma platita. Se poate observa cala Suma platita clinetul este pe minus, adică mai are de plată respectiva suma din valoarea finală.

3. Afişaţi pentru fiecare comandanda in parte următorul text:comanda a fost realizata intre: agentul: [numele agentului din baza de date] furnizează clientului: [numele clientului din baza de date] articolul: [numele articolului din baza de date]

4. Afişaţi pentru fiecare comandă în parte, după ce calculaţi cât mai are de dat clientul, dacă are restanţă sau a plătit în plus.

Pentru a rezolva această interogare vă veţi folosi de interogarea cu numărul doi de mai sus.

Rezultatul este următorul:

Expresia IIF se scrie în rândul FIELD al grilei de interogare.

Interogări de sintetizare a datelorPe lângă posibilitatea de a utiliza expresii pentru a adăuga câmpuri calculate în interogări, Access permite utilizatorilor realizarea unor cereri prin intermediul cărora se pot aplica funcţii unor înregistrări grupate după criterii prestabilite. Prin intermediul acestor query cu rol de sintetizare a datelor se pot construi cu uşurinţă cereri care să returneze totalul, media, minimul sau maximul unor grupuri de tupluri. Pentru a realiza o cerere de sintetizare a datelor se va elabora interogarea de selecţie aducându-se în grila Query Design câmpurile necesare şi adăugându-se eventualele câmpuri calculate, după care se va apăsa butonul Totals din bara de instrumente. Ca rezultat al acestei operaţii, în grila Query Design, va fi disponibil un nou rând intitulatTotal. În mod implicit, gruparea datelor după valorile câmpului toate coloanele vor avea selectată în linia totalopţiunea Group By (în cazul în care mai multe coloane au selectată în linia Total opţiunea Group By, gruparea se va face întâi după primul câmp din stânga, apoi după al doilea, ş.a.m.d.). Se va păstra opţiunea Group By în dreptul câmpurilor de grupare, urmând ca pentru celelalte câmpuri să fie selectată din lista de selecţie funcţia dorită.

S-a realizat o interogare realizându-se o grupare după fiecare agent, ce produs şi cantitatea comandată.TEMARealizaţi o interogare de grupare după fiecare produs şi cantitatea comandată.

Putem de asemenea să realizăm sumă de valoare produs comandată pentru fiecare articol:

Interogări de acţiuneInterogările de acţiune pot avea ca rezultat:• Crearea de noi tabele (Make Table Query)• Actualizarea datelor (Update Query)• Adăugarea de noi înregistrări (Append Query)• Ştergerea înregistrărilor (Delete Query)

Interogările de acţiune se realizează similar interogărilor de selecţie de până acuma, specificându-se în plus prin meniul Query tipul de cerere dorit.

1. Interogări pentru crearea de noi tabele (Make Table Query)Crearea unei tabele noi în urma interogării tabelelor deja existente, presupune realizarea următorilor paşi:

Elaborarea unei interogări de selecţie în modul Design View (vor fi luate în calcul doar câmpurile ce dorim să facă parte din structura noii tabele, iar, în cazul în care sunt necesare şi alte câmpuri pentru aplicarea unor criterii, acestea nu vor fi marcate în linia Show a grilei de interogare.

Apelarea din meniul Query a opţiunii Make Table Query şi specificarea în casete Make Table a numelui noii tabele (se poate opta pentru crearea noii tabele într-o altă bază de date bifând opţiunea Another Database şi specificând numele fişierului).

Lansarea în execuţie a interogării.

Exemplu:Crearea unei tabele ce va conţine numele clienţilor şi pentru fiecare client cantitatea totală de articole comandate. Vom face o grupare după numele clienţilor, iar ca expresie calculată vom face sumă după cantităţile de produse comandate. După ce am ales cîmpurile care vor forma noua tabelă, vom alege din meniul Query opţiunea Make-Table Query. După care vom lansa în execuţie interogarea.

Am ales câmpurile care vor apărea în noua tabelă.

Din meniul Query alegem Make-Table query. Va apărea o fereastră unde vi se cere un nume pentru tabela nou creată.

După ce veţi scrie numele tabelei veţi rula interogarea apăsând butonul: . Apăsaţi Yes pentru ferestrele de atenţionare care vor apărea.Apoi mergeţi în fereastra Database, pe tabul Tables şi verificaţi dacă a fost creată noua tabelă. Daţi Open să vedeţi rezultatul interogării.

Exemplul 2:

Crearea unei tabele care să conţină aceleaşi câmpuri ca tabela comenzi însă, câmpul suma finală va fi redenumit în valoare finală care va fi calculat după formula cant * pret, iar comenzile vor fi doar pentru clienţii din oraşul Bacău.

De asemenea dvs. puteţi să bifaţi caseta de pe rândul Show, şi astfel va apare şi oraşul unde au avut loc comezile.Apoi alegeţi opţiunea Make- Table Query şi ca titlu pentru tabel puteţi scrie „comenzi dupa localitate”.

Rulaţi interogarea şi vedeţi tabela nou opţinută.

2. Interogări pentru actualizarea datelor (Update Query).Acest tip de înregistrare permite modificarea valorilor pentru înregistrările care satisfac condiţiile impuse la crearea interogării.

Exemplu:Pentru clienţii din oraşul Bacau vom pune o reducere de valoarea 10.Pentru a realiza o interogare de tip Update, se vor realiza următorii paşi:

a) În modul Design View se vor selecta câmpurile pentru care aplicăm modificarea:

Câmpurile folosite sunt:Nume client, client oraş cu criteriul „bacau”, şi reducere, pentru care vom face modificarea.

b) Din meniul Query se va selecta opţiunea Update Query:

c) În linia Update To se va completa formula de calcul sau valoarea dorită pentru actualizare:

Mergeţi apoi şi verificaţi în tabela clienţi daca valorile dorite au fost modificate.În cadrul interogărilor Update nu sunt admise modificări asupra câmpurilor cheie primară sau modificări ce ar putea conduce la nerespectarea restricţiilor de integritate.

3. Interogări pentru adăugarea de înregistrări (Append Query).Interogările pentru adăugare oferă utilizatorilor posibilitatea inserării de noi înregistrări în tabel pornind de la înregistrările existente.

Pentru exemplificare vom presupune că dorim adăugarea în tabela comenzi după localitate (ce conţine în momentul actual doar comenzile pentru clienţii din Bacău) a comenzilor pentru clienţii din Iaşi (existenţi în tabela comenzi).Pentru a realiza o interogare de tip Append este necesară parcurgerea următoarelor etape:

a) Elaborarea unei interogări de selecţie în modul Design View. Se va alege tabela/tabelele ce conţine datele şi vor fi luate în calcul doar câmpurile ce conţin valorile ce vor fi adăugate, iar, în cazul în care se doreşte aplicarea unor criterii de selecţie a acestora, se vor adăuga câmpurile de restricţionat.

b) Din meniul Query sau din bara de unelte de sub meniu, se va selecta opţiunea Append Query ce va conduce la afişarea ferestrei Append în care se va selecta numele tabelei în care dorim adăugarea.

c) Transformarea interogării de selecţie într-o interogare de adăugare are ca efect apariţia rândului Append To în fereastra de proiectare a interogării. În acest rând se va specifica pentru fiecare câmp din tabela ce conţine datele, denumirea câmpului corespondent din tabela unde vor fi adăugate.

Rulaţi interogarea şi vedeţi dacă au fost adăugate înregistrări în tabela comenzi după localitate.

4. Interogări pentru ştergerea de înregistrări (Delete Query).

Interogările de ştergere deînregistrări - Delete oferă posibilitatea eliminării mai multor înregistrări din tabele pe baza unor criterii impuse de utilizator. Atenţie însă, ştergerea de înregistrări poate fi realizată doar atunci când nu sunt încălcate reguli de integritate ale bazei de date. Prin urmare nu putem şterge înregistrări dintr-o tabelă (părinte) dacă de acele înregistrări depinde înregistrările din altă tabelă (copil).Pentru a realiza o interogare de ştergere se elaborează în mod normal o cerere selectându-se câmpurile dorite şi impunându-se restricţiile necesare după care se apelează din meniul Query sau folosindu-se shortcut-ul respectiv, opţiunea Delete.Exemplul următor prezintă modalitatea în care se pot şterge din baza de date toate comenzile efectuate de clienţii ce au codurile 2 sau 3.

Se selectează câmpurile după care aplicăm criteriul dorit. Apoi se allege opţiunea Delete Query.

Interogarea va arăta astfel:

Rulaţi apoi interogarea.

Rapoarte şi Formulare – laborator 7

a) RapoarteDin fereastra Database alegeţi Reports şi „Create report by using wizard”.

Vom crea raportul cu numele Raport comenzi. Introducem toate cîmpurile din tabela Comezi, clic pe Next, şi în continuare vom grupa pe nivele cîmpurile.

Pe primul nivel vor fi Id-urile. Dacă apare alt cîmp ca fiind pe primul nivel (cele scrise cu albastru), îl trecem în partea stângă prin clic pe butonul „ < ” şi trecem în partea dreaptă cîmpul care ne interesează (prin clic pe butonul „ > ”). De asemenea ne putem folosi de opţiunea Priority pentru a trece un cîmp la un nivel mai sus.

Clic Next. Putem sorta câmpurile ascendent dupa nume. La opţiunea Summary Options se va deschide o fereastră unde puteţi să alegeţi una dintre funcţiile de calcul, astfel încât să aveţi rapoarte cu cîmpuri grupare.

În continuare alegeţi opţiunile pe care le doriţi, daţi numele Raport comenzi şi la urmă clic Finish.

Un alt exemplu de raport complex este cel în care vom afisa numele agenţilor, numele clienţilor, denumirea articolelor, cantitatea comandată şi valoarea finală.Atunci când fereastra Report Wizard se va deschide, alegeţi la câmpul Tables/Queries pe rând tabelele agenţi, si de acolo câmpul agent nume, apoi tabela clienţi si de acolo câmpul nume client, tabela articole şi de acolo câmpul articol nume, etc. Rezultatul este:

b) Formulare

Vom crea un fomular cu numele Date Angenţi, pentru introducerea datelor în altă formă decât cea tabelară avâd în plus şi indicaţii pentru cel ce introduce datele.

Din fereastra Database faceţi clic pe Forms şi apoi alegeţi opţiunea „Create form by using wizard”. În fereastra nou apărută alegeţi tabela Angenţi. Veţi adăuga câmpurile ce trebuie sa apară în formular. Acest lucru este realizat astfel: din Available fields selectaţi pe rând fiecare câmp dorit şi apoi apăsaţi butonul „ > ” pentru a le trece în Selected fields.

Alegeţi apoi modul de afişare al câmpurilor.

Apoi modul de prezentare al formularuli şi la urmă denumiţi formularul. Rezultatul poate fi de forma:

Sau de forma:

Proiectul trebuie sa contina:1. o BD de 4 tabele2. minim 10 inregistrari de tabela3. 10 interogari impartite astfel: - 2 interogari simple cu maxim 2 tabele (una dintre interogari sa faca si ordonare)- 3 interogari cu campuri calculate- 1 interogare cu group by- 4 interogari de actiune cate una din fiecare categorie (make table, update, append si delete)4. 2 rapoarte5. 1 formular