fisa disciplinei 3 access.pdf
TRANSCRIPT
-
Utilizarea TIC pentru servicii de calitate n domeniul financiar contabil
POSDRU/81/3.2/S/59664
Proiect cofinanat din Fondul Social European prin Programul Operaional Sectorial Dezvoltarea Resurselor Umane 2007-2013
FONDUL SOCIAL EUROPEAN
Programul Operaional Sectorial Dezvoltarea Resurselor Umane 2007 2013
Axa prioritar 3 Creterea adaptabilitii lucrtorilor i a ntreprinderilor
Domeniul major de intervenie 3.2 Formare i sprijin pentru ntreprinderi i angajai pentru
promovarea adaptabilitii
Titlul proiectului: Utilizarea TIC pentru servicii de calitate n domeniul financiar contabil
Cod contract: POSDRU/81/3.2/S/59664
Proiect cofinanat din Fondul Social European prin Programul Operaional Sectorial Dezvoltarea
Resurselor Umane 2007-2013 Investete n oameni
Tehnologii Office cu aplicabilitate n
domeniul financiar-contabil
- Microsoft Access -
-
Utilizarea TIC pentru servicii de calitate n domeniul financiar contabil
POSDRU/81/3.2/S/59664
Proiect cofinanat din Fondul Social European prin Programul Operaional Sectorial Dezvoltarea Resurselor Umane 2007-2013
-
Baze de date Microsoft Access
Pagina 3 din 29
1 Interogarea datelor i extragerea informaiilor din cadrul
unei baze de date Access
Structurarea unei baze de date relaionale este un proces ghidat de reguli stricte
privind construirea tabelelor care s respecte modelul relaional. Restriciile impuse
de acest model implic obligativitatea stocrii informaiei n structura din care aceasta
face parte i nu n locul unde apare. Dei o informaie poate s apar n mai multe
locaii, totui ea va fi stocat ntr-un singur tabel, potrivit principiului non-redundanei
datelor.
Interogarea unei baze de date este un proces foarte important pentru utilizatorii
acestora, ntruct presupune regsirea i extragerea informaiilor dorite de ctre
utilizator. Pentru realizarea interogrilor, Access pune la dispoziia utilizatorilor
obiectele de tip Cerere (Query). Rezultatul cererii se prezint sub forma unei foi de
rspuns dynamic (ceea ce nseamn c la fiecare apelare sunt afiate datele care
ndeplinesc criteriile i structura interogrii), foaie care nu mai exist fizic dup
nchiderea cererii. Aadar, o cerere de interogare a bazei de date nu mrete spaiul
ocupat n cadrul calculatorului de ctre baza de date. O cerere poate interoga date
din cadrul unuia sau mai multor tabele, sau chiar din cadrul altor cereri.
O cerere reprezint, de fapt, o modalitate de a oferi rspunsuri la diverse ntrebri
adresate bazei de date. Aceste rspunsuri pot fi formulate utiliznd datele stocate
ntr-unul sau mai multe tabele. n procesul de interogare a mai multor tabele, cererile
utilizeaz un concept relativ simplu, denumit compunere. La baz, acest concept
const n punerea laolalt a datelor din mai multe tabele prin intercorelarea lor,
pornind de la egalitatea valorilor unor cmpuri cu aceeai semnificaie din tabele
diferite. De exemplu, avnd structura tabelelor din Error! Reference source not
found., rspunsul la ntrebarea Care sunt denumirile produselor din categoria
sosuri? va fi obinut prin compunerea tabelelor Produse i CategoriiProduse, pentru
care condiia de compunere va fi egalitatea dintre valorile cmpului CodCategorie din
tabelul CategoriiProduse i cele ale cmpului cheie extern CodCategorie din
Produse. Deci se va selecta categoria cu denumirea Sosuri pentru care se va afla
care este codul acesteia i se vor afia denumirile acelor produse pentru care codul
categoriei este cel aflat n tabelul CategoriiProduse (Figura nr. 1.1).
-
Baze de date Microsoft Access
Pagina 4 din 29
Figura nr. 1.1 Compunerea a dou tabele pentru obinerea informaiilor cerute prin impunerea
unui criteriu de filtrare a datelor
Cteva dintre destinaiile care se pot da cererilor de interogare a bazelor de date pot
fi:
Regsirea informaiei n baza de date;
Cutarea datelor n tabele legate;
Afiarea unui subset de date prin impunerea anumitor condiii;
Sortarea informaiilor obinute din mai multe tabele;
Efectuarea de calcule folosind date din tabelele bazei de date;
Actualizarea bazei de date;
Pregtirea datelor n vederea afirii lor n cadrul unor formulare sau
rapoarte.
n Access, interogrile pe tabele multiple leag date stocate n dou sau mai multe
tabele legate prin una sau mai multe asocieri, astfel nct aceste date s devin
informaii importante pentru analiz.
1.1 Tipuri de cerere pentru interogarea bazelor de date
Access ofer posibilitatea construirii urmtoarelor tipuri de cerere:
Cereri de selecie (Select Query), formeaz cel mai utilizat tip de
cerere de interogare, care d posibilitatea stabilirii de criterii de
cutare, regsirii informaiei dorite i afirii acestei informaii pentru a
fi accesibil utilizatorului. Tot n aceast categorie se nscriu i
cererile prin care se afieaz rezultatul unor calcule dintre valorile
unor anumite cmpuri. La rndul lor, cererile de selecie se mpart n
alte categorii mai mici:
-
Baze de date Microsoft Access
Pagina 5 din 29
o Cereri de selecie propriu-zis: selecteaz date din unul sau
mai multe tabele i/sau interogri, pe baza unor criterii stabilite
iniial;
o Cereri parametrizate: presupune filtrarea datelor din tabele
sau interogri pe baza unor criterii precizate printr-un
parametru, adic, o valoarea care se va stabili de ctre
utilizator la executarea cererii;
o Cereri cu cmpuri calculate: afieaz, pe lng datele din
tabele sau interogri, i rezultatul unor calcule efectuate ntre
valorile a dou sau mai multe cmpuri, sau ntre acestea i
valori scalare;
o Cereri de agregare a datelor: sintetizeaz date din tabele,
utiliznd funcii de agregare a datelor dintr-unul sau mai multe
cmpuri pentru mai multe nregistrri.
Cereri de analiz ncruciat, care permit sintetizarea datelor i a
rezultatelor obinute din calcule, sub forma unui tabel pivot, fcnd
informaia mai uor de citit i de neles.
Cereri de aciune, care modific date din cadrul tabelelor sau
creeaz noi tabele, pe baza unor criterii. Cererile de aciune se mpart
n urmtoarele subcategorii:
o Cereri pentru crearea de noi tabele (Make Table Query):
creeaz un nou tabel, cu toate nregistrrile sau doar o parte a
acestora, din unul sau mai multe tabele asociate;
o Cereri pentru adugarea de date n tabele (Append Query):
adaug anumite nregistrri dintr-un tabel n altul;
o Cereri pentru modificarea datelor din tabele (Update
Query): Modific date dintr-un anumit tabel, n funcie de
anumite criterii precizate;
o Cereri pentru tergerea datelor din tabele (Delete Query):
pe baza unui anumit criteriu, terg nregistrrile dintr-un anumit
tabel.
Oricare ar fi tipul cererii de interogare, orice obiect de tip cerere este supus
urmtoarelor operaii:
Operaia de construire sau de creare a cererii;
Operaia de utilizare, deschidere sau afiare a datelor interogate;
Operaia de modificare a definiiei cererii realizate prin operaia de creare.
-
Baze de date Microsoft Access
Pagina 6 din 29
1.2 Crearea obiectelor de tip cerere
Un obiect de tip cerere poate fi creat utiliznd dou modaliti principale: prin
proiectare grafic a cererii (cunoscut i sub denumirea de Modul Design), i prin
utilizarea limbajului standard de interogare a bazelor de date (cunoscut sub
denumirea de limbajul SQL Structured Query Language). n prezentul capitol ne
vom ocupa doar de prima modalitate, folosind interfaa grafic Access.
Figura nr. 1.2 Regsirea elementelor de tip interogare n cadrul unei baze de date
Toate obiectele de tip cerere sunt afiate n cadrul grupului de obiecte Queries din
panoul de navigare printre obiectele bazei de date. Efectuarea unui dublu click pe
numele unui asemenea obiect va deschide rspunsul la cererea pentru care obiectul
de tip interogare a fost creat. Pentru modificarea definiiei unei interogri se va
efectua click dreapta pe numele acesteia i se va apela opiunea Design View din
cadrul meniului contextual care apare.
Pentru a facilita modul de definire a structurii unei interogri, Microsoft Access
propune utilizarea unui set de programe ajuttoare, numite asisteni, care au menirea
de a conduce utilizatorul la o anumit finalitate cu privire la scopul interogrii create
prin parcurgerea succesiv a anumitor pai. Apelarea acestor asisteni poate fi
efectuat prin opiunea CreateQueriesQuery Design.
-
Baze de date Microsoft Access
Pagina 7 din 29
Figura nr. 1.3 Accesarea asistenilor pentru crearea interogrilor
Caseta de dialog afieaz urmtoarele moduri de creare a unei noi cereri:
Asistentul pentru cereri simple (Simple Query Wizard);
Asistentul pentru crearea tabelelor ncruciate (Crosstab Query
Wizard);
Asistentul pentru cutarea nregistrrilor duplicat (Fiind Duplicates
Query Wizard);
Asistentul pentru cutarea nregistrrilor care nu au corespondent n
cele dou tabele surs (Fiind Unmatched Query Wizard).
Dei asistentul poate uura munca celui care l utilizeaz, n practic, cel mai des se
utilizeaz prima modalitate de creare a cererilor, i anume modul grafic de proiectare,
din considerentul c permite personalizarea modului de definire a obiectelor de tip
interogare.
1.2.1 Crearea cererilor de selecie
Cererile de selecie a datelor formeaz categoria celor mai utilizate tipuri de
interogare a unei baze de date, ntruct sunt cele care permit aflarea rspunsurilor la
cele mai frecvente ntrebri care intereseaz utilizatorul final. Construirea unei cereri
de selecie a datelor pornete de la analiza cerinei pe care utilizatorul o adreseaz
bazei de date. Astfel, trebuie identificate, pe rnd, ce informaii trebuie afiate, care
sunt datele ce trebuie apelate pentru afiarea acestora, din ce tabele fac parte
acestea i cum se asociaz aceste tabele. Foarte important este, n egal msur,
identificarea tipului de interogare ce trebuie folosit pentru aflarea rezultatului final.
Pentru cererile de selecie a datelor, se alege opiunea Design View din caseta New
Query. Access va afia fereastra de interogarea a tabelelor dup exemplu, cunoscut
sub denumirea Query By Example (prescurtat Q.B.E.). Alturi de aceast fereastr,
-
Baze de date Microsoft Access
Pagina 8 din 29
se va afia i caseta de selectare a tabelelor care urmeaz a fi interogate, denumit
Show Table.
Figura nr. 1.4 Structura ferestrei de interogare QBE
Pentru a rspunde unei cereri de selecie utiliznd metoda de proiectare se apeleaz
opiunea CreateQueriesQuery Design. Pe rnd, trebuie specificate tabelele care
ofer sursa informaional pentru cererea specificat, alturi de cmpurile ale cror
valori se vor afia sau pentru care se impun condiii. Panoul tabelelor va fi completat
prin selectarea acestora din fereastra Show Table (Figura nr. 1.4). Atunci cnd
aceste tabele sunt asociate, grila de tabele va afia inclusiv legturile dintre acestea,
fie c ele provin din fereastra Relationship, fie c sunt rezultatul utilizrii asistentului
Lookup Wizard.
Specificarea cmpurilor interogrii
Grila de cmpuri conine mai multe rnduri care au destinaii diferite. Fiecare coloan
din cadrul grilei este destinat unui anumit cmp din cadrul tabelelor aduse pentru
interogare. O scurt descriere a rndurilor aferente grilei Query Design este
necesar pentru a identifica rolul acestora n procesul de proiectare a modului de
interogare a bazei de date:
Rndul Field din cadrul grilei se completeaz automat sau de la tastatur cu
denumirea unui cmp din cadrul tabelelor prezente.
-
Baze de date Microsoft Access
Pagina 9 din 29
Rndul Table este destinat precizrii tabelului din care face parte cmpul
introdus n cadrul primului rnd din gril, avnd n vedere c aceeai denumire
o pot lua dou sau mai multe cmpuri, dar din tabele diferite.
Rndul Sort ofer posibilitatea sortrii datelor afiate la executarea interogrii
prin ordonarea cresctoare sau descresctoare a celor din cmpul sau
cmpurile unde se precizeaz sortarea. Valorile pentru acest rnd pot fi alese
dintr-o list derulant.
Rndul Show este destinat posibilitii de afiare sau ascundere la executarea
interogrii a datelor din cmpul pentru care se selecteaz sau deselecteaz.
Rndul Criteria prezint importan prin prisma oportunitilor oferite pentru
filtrarea datelor din tabele. Aici i n cadrul rndurilor care urmeaz vor fi
introduse expresiile valide pe baza crora se va efectua selectarea doar a
acelor nregistrri care ndeplinesc criteriile precizate. Criteriile vor fi precizate
utiliznd anumii operatori de comparaie sau logici.
Grila de cmpuri ale ferestrei de proiectare poate fi completat prin aducerea
cmpurilor pe coloane diferite ale rndului Field. Pentru completarea acestui rnd pot
fi utilizate mai multe metode: prin efectuarea unui dublu click pe denumirea cmpului
selectat din cadrul tabelelor prezente n panoul de tabele, prin metoda glisarea
denumiri cmpului din tabelul din care acesta face parte situat n panoul de tabele
pn pe rndul Field aferent unei coloane noi din grila de cmpuri, sau prin
selectarea din caseta derulant aferent rndului Field din grila de cmpuri.
Alturi de aceste modaliti de completare a rndului Field exist posibilitatea
completrii manuale a acestuia prin tastarea denumirii cmpului ale crui valori
urmeaz a fi afiate la vizualizarea rezultatului interogrii. Coloanele definite n acest
sens poart denumirea de cmpuri cu expresii. Pentru situaii speciale n care
valorile solicitate spre a fi afiate nu pot fi aflate printr-o simpl consultare a
cmpurilor din tabele ci necesit ajustri prin aplicarea de diverse operaii de
prelucrare, Access permite tastarea modului de afiare a datelor direct n rndul
Field. Spre exemplu, dac se cere doar o parte din informaia deinut de un cmp,
formula pentru extragerea valorii cerute este tastat sub forma:
Denumire cmp: Formul de calcul
De exemplu, afiarea lunii n care a fost emis fiecare bon de cas presupune, pe
lng afiarea valorilor cmpurilor care identific fiecare bon de cas (regsite n
cmpuri din tabelul BonCas), i definirea modului de extragere a lunii din data
bonului de consum. n acest sens, pe rndul Field, se va tasta formula de calcul
pentru afiarea lunii n care a fost emis fiecare bon de cas:
LunaEmitereBonCasa:Month([DataBonCasa])
Exist, de asemenea, situaii speciale n care, alturi de valori ale unor cmpuri din
cadrul bazei de date, un anumit cmp din cadrul unei interogri necesit doar
afiarea unor valori scalare. n asemenea situaie, pe rndul Field al grilei de cmpuri
-
Baze de date Microsoft Access
Pagina 10 din 29
va fi tastat valoarea scalar care trebuie afiat fiind precedat de denumirea
atribuit acestei coloane.
Access ofer, de asemenea, posibilitatea de a aduce n grila de cmpuri a tuturor
atributelor unui tabel, prin efectuarea unui dublu clic pe semnul * care apare n
tabelul respectiv. Acelai lucru se poate obine i dac se efectueaz un dublu clic pe
denumirea tabelului din panoul superior, pentru a selecta toate cmpurile, i
utilizarea metodei Drag and Drop pentru a aduce toate aceste atribute n cadrul
grilei.
Exist posibilitatea tergerii anumitor cmpuri din cadrul grilei de cmpuri, n situaia
n care nu le mai considerm necesare pentru interogare. Acest lucru se realizeaz
prin poziionarea cursorului deasupra denumirii cmpului n cauz, i efectuarea unui
clic cnd cursorul capt forma unei sgei negre cu vrful n jos (). Apoi, pentru
tergere, se apas tasta Delete.
Precizarea criteriilor de filtrare
Criteriile se precizeaz pe rndul Criteria sau Or utiliznd expresii valide, care s
poat fi nelese de Microsoft Access.
n continuare, vom prezenta cteva modaliti de specificare a condiiilor de filtrare a
datelor introduse cu ajutorul unor operatori. Aceste condiii vor fi introduse pe rndul
Criteria pe aceeai coloan cu denumirea cmpului asupra crora acioneaz.
Pe rndul Criteria pot fi utilizai diferii operatori prin care pot fi construite condiii
complexe. Vom enumera cele mai importante categorii de astfel de operatori, oferind
o scurt descriere de utilizare a lor n criteriul de filtrare:
operatorii de comparaie: (>, >=,
-
Baze de date Microsoft Access
Pagina 11 din 29
IS NULL IS NULL Caut nregistrrile al cu valori nule
IS NOT
NULL
IS NOT NULL Caut nregistrri care conin valori
Operatori de
identificare
-
Baze de date Microsoft Access
Pagina 12 din 29
care se impun condiii. Urmrind structura bazei de date (a se vedea Error!
Reference source not found.) vom observa c informaiile necesare sunt plasate n
dou tabele: denumirea categoriei produselor pentru care se impune condiia s fie
conserve din fructe i legume se gsete n tabelul CategoriiProduse, n timp ce
denumirea produselor care se cere a fi afiat i termenul de valabilitate sunt
cmpuri ale tabelului Produse.
Figura nr. 1.5 Modalitatea de rezolvare a unei cereri de interogare utiliznd fereastra Query
Design
Cele dou tabele sunt asociate prin intermediul perechii de cmpuri CodCategorie
(cheia primar) din CategoriiProduse i CodCategorie (cheie extern) din Produse.
Proiectarea obiectului de tip interogare care va oferi rspunsul pentru aceast cerin
este explicat n Figura nr. 1.5.
Aadar, toate condiiile precizate pe acelai rnd Criteria sunt combinate utiliznd
operatorul AND. Dac dou condiii sunt precizate pe rnduri Criteria diferite atunci
ntre cele dou condiii se interpune operatorul OR.
Compunerea tabelelor n vederea interogrii lor
n vederea interogrii datelor din mai multe tabele surs, fereastra QBE permite
1. Echicompunere (Inner Join) se vor selecta nregistrrile care au
corespondent n ambele tabele.
-
Baze de date Microsoft Access
Pagina 13 din 29
2. Compunere extern (OUTER JOIN) se vor selecta toate nregistrrile din
una din tabele, indiferent dac au sau nu corespondent n cealalt tabel. n
funcie de direcia selectrii datelor, aceasta poate fi compunere extern de
stnga (LEFT JOIN) sau de dreapta (RIGHT JOIN).
n privina cererilor de selecie care au ca surs mai multe tabele trebuie precizat c,
n mod implicit, Access utilizeaz operaia de Echicompunere, fapt ce conduce la
selectarea nregistrrilor ce au corespondent n ambele tabele.
Not:
Dac o cerere de interogare presupune selectarea datelor din dou tabele cu un
numr de x i respectiv y nregistrri, i ntre cele dou tabele nu este implicit sau
explicit definite nici o asociere, atunci rezultatul va conine un set de x * y nregistrri,
adic se formeaz produsul cartezian ntre cele dou seturi de date.
Exemplu de echicompunere ntre dou tabele: S se afieze toate denumirile
produselor care au fost vndute. Se vor afia, prin urmare, toate denumirile acelor
produse pentru care valorile cmpului IdProdus se regsesc i n tabelul
ProduseVndute.
Figura nr. 1.6 Exemplu de interogare a dou tabele prin echicompunere
-
Baze de date Microsoft Access
Pagina 14 din 29
Figura nr. 1.7 Rezultatul interogrii a dou tabele compuse prin operaiunea de echicompunere
Exemplu de compunere extern: S se afieze toate denumirile produselor, indiferent
dac au fost sau nu vndute. Se vor afia toate denumirile produselor chiar dac
valorile cmpului IdProdus nu se regsesc i n tabelul ProduseVndute.
Figura nr. 1.8 Selectarea datelor din dou tabele prin compunere extern
Rezultatul afiat este cel prezentat n Figura nr. 1.9.
-
Baze de date Microsoft Access
Pagina 15 din 29
Figura nr. 1.9 Afiarea rezultatului unei interogri avnd ca surs dou tabele compuse prin
compunere extern
Se observ, prin urmare, c pentru acele valori care nu au corespondent n cea de-
a doua tabel, cmpul corespondent va prezenta valoarea NULL.
Dac ntre dou tabele nu exist asocieri predefinite, acestea se pot stabili ntre dou
cmpuri fcnd parte din cele dou tabele, prin glisarea mouse-ului de la cmpul de
legtur din primul tabel ctre cmpul de legtur din cel de-al doilea. Ulterior, i
aceast asociere, reprezentat printr-o linie ce va uni cele dou cmpuri ntre care se
realizeaz legtura, poate fi modificat n sensul schimbrii tipului de compunere
ntre tabelele n cauz.
Lansarea n execuie a cererii se realizeaz prin urmtoarele procedee: Utiliznd butonul View, , primul din grupul Result al meniului Design (acest
buton poate permite comutarea ntre diversele tipuri de afiare a interogrii); Utiliznd butonul Run, din acelai grup Result; Efectund dublu click pe numele interogrii din panoul de navigare al bazei de
date.
1.2.2 Crearea cererilor parametrizate
Cererile parametrizate presupun introducerea unei valori pentru criteriul unui cmp
de ctre utilizator, n momentul n care interogarea este lansat n execuie. Pentru
aceasta, se procedeaz la crearea unei cereri de interogare de selecie, parcurgnd
paii pe care i-am prezentat, urmnd s stabilim, n final, crui cmp i se va atribui
parametrul pentru filtrarea datelor.
Un parametru const, de fapt, dintr-un text pe care calculatorul l va afia n
momentul n care cererea este lansat n aciune, text care va invita utilizatorul s
introduc valoarea n urma creia se va face filtrarea. Acest text va trebui,
-
Baze de date Microsoft Access
Pagina 16 din 29
ntotdeauna, introdus ntre paranteze drepte, pentru ca Access s-l recunoasc drept
parametru i nu drept un criteriu stabilit anterior. De exemplu, se dorete afiarea
tuturor produselor dintr-o categorie specificat de utilizator n momentul execuiei.
Fereastra QBE va arta astfel:
Figura nr. 1.10 Proiectarea unei interogri parametrizate
La executarea interogrii nu sunt afiate direct rezultatele sub forma unui set de
nregistrri, aa cum se ntmpl n situaia unei interogri simple, ci se va afia mai
nti mesajul prin care se cere valoarea parametrului care va completa condiia
impus, urmnd ca filtrarea s se realizeze n funcie de aceast valoare.
n stabilirea mesajului parametrului unei interogri, cteva observaii se impun a fi
precizate:
1. Mesajul se va introduce ntotdeauna ntre paranteze drepte, pentru a fi tratat
ca parametru i nu ca o valoare de tip text ce urmeaz a fi comparat cu
valorile cmpului n cauz;
2. Textul definit ca mesaj al parametrului trebuie s fie diferit de denumirea
oricrui cmp din cadrul tabelelor selectate, n caz contrar fcndu-se
comparaia ntre valoarea cmpului pentru care se impune condiia i valoarea
cmpului a crui denumire apare ntre paranteze drepte pe rndul Criteria.
-
Baze de date Microsoft Access
Pagina 17 din 29
3. Parametrul face obiectul unei comparaii, prin urmare orice parametru va fi
trecut pe rndul Criteria, precedat de operatori de comparaie sau fcnd
parte dintr-o formul al crei rezultat va fi comparat cu valoarea cmpului
pentru care s-a impus condiia.
Figura nr. 1.11 Etapele executrii unei interogri parametrizate
2.2.1.2 Crearea cererilor cu cmpuri calculate Crearea unei interogri nu se limiteaz numai la cmpurile tabelei sau tabelelor bazei
de date. Se pot utiliza expresii pentru a crea cmpuri calculate. Aceste cmpuri pot fi
rezultatul unor operaii ntre dou sau mai multe cmpuri, sau ntre unul sau mai
multe cmpuri i anumite valori scalare. Pentru a efectua operaii cu aceste cmpuri,
sunt folosii operatorii aritmetici (+, -, *, /) i parantezele rotunde. Parantezele drepte
sunt utilizate pentru desemnarea unui anumit cmp (cu alte cuvinte, cmpurile
trebuie trecute ntre paranteze drepte). Exemple de cmpuri calculate:
Se cunoate c Valoare produs vndut=Cantitate*Pre Vnzare. Aceast formul
se exprim, n limbajul Access, n urmtoarea form:
[Valoare produs]: [Cantitate] * [PretVanzare]
Totodat Access dispune i de o bibliotec de funcii predefinite la care utilizatorul
poate face apel n definirea unui cmp calculat. Spre exemplu, dac se dorete
afiarea vechimii, n zile, de la emiterea unui bon de cas i pn n prezent poate fi
formulat urmtorul cmp calculat:
-
Baze de date Microsoft Access
Pagina 18 din 29
VechimeBonCasa: Date()-DataBonCasa
unde Date() este funcia Access care returneaz data curent.
Aceste formule se introduc n grila de cmpuri, n urmtoarea coloan liber, pe
rndul Field. Eticheta cmpului calculat va fi dat de partea din stnga semnului :.
n exemplul nostru, cmpul se va numi VechimeBonCasa.
Pentru exemplificare explicm modul de proiectare a interogrii pentru urmtoarea
cerin: Care sunt denumirile produselor i valoarea vndut din fiecare prin bonurile
de cas din ultimele 30 de zile.
Figura nr. 2.8 Exemplu de proiectare a unei interogri cu cmp calculat
Observaie: Aa cum s-a putut vedea i n cazul interogrilor parametrizate,
ncadrarea ntre paranteze drepte este specific i modului de precizare a mesajului
unui parametru. De aceea, cnd denumirea unui cmp ce face parte dintr-o formul
nu este corect tastat, ceea ce apare ntre paranteze drepte este considerat de
Access ca fiind un parametru i la executarea interogrii va fi afiat caseta Enter
Parameter Value specific interogrilor parametrizate.
-
Baze de date Microsoft Access
Pagina 19 din 29
1.2.1 Agregarea datelor din mai multe nregistrri ale unei interogri
Agregarea datelor definete o modalitate de calcul a unor valori statistice aplicnd
diverse funcii de sintetizare unui grup de valori ale aceluiai cmp provenind din mai
multe nregistrri ale unui set de date. Astfel, Access permite numrarea elementelor
unui anumit grup (funcia COUNT), nsumarea acestora (funcia SUM), valoarea cea
mai mare (MAX), valoarea cea mai mic (funcia MIN), deviaia standard (STDEV),
variana (VAR), prima valoare a cmpului dintr-un set de nregistrri (FIRST) sau
ultima valoare (LAST). Toate aceste funcii sunt numite funcii de agregare, acionnd
doar la nivel de grup de nregistrri. Gruparea nregistrrilor se realizeaz dup valori
egale ale unui anumit cmp al setului de date supus agregrii. Pentru nelegerea
agregrii datelor n Access vom considera urmtoarea cerin:
Care este cantitatea cea mai mare vndut din fiecare produs?
Sintetizarea presupune afiarea a dou informaii pentru fiecare produs din cadrul
nomenclatorului: denumirea produsului i cantitatea maxim comandat din acesta.
Considernd, pentru exemplificare, urmtorul set de nregistrri aferent comenzilor
emise de clieni pentru produsele firmei, vom detalia modul n care Access va realiza
gruparea datelor i apoi agregarea cantitilor comandate:
Figura nr. 1.12 Modalitatea de sintetizare a informaiei prin gruparea nregistrrilor dup
valorile unui anumit cmp
n situaia existenei unei condiii de filtrare n cerina interogrii, aceast condiie este
prioritar, Access realiznd mai nti filtrarea datelor, urmnd a stabili cmpul de
-
Baze de date Microsoft Access
Pagina 20 din 29
grupare i agregarea pe setul de nregistrri care va rmne n urma eliminrii acelor
nregistrri care nu respect criteriul de filtrare impus.
Pentru exemplificare vom completa cerina anterioar cu o condiie suplimentar
impus asupra datelor: Care este cantitatea cea mai mare vndut din fiecare
produs dup data de 1.1.2012?
Se observ c este necesar eliminarea unor nregistrri din setul produselor
vndute, astfel nct s rmn doar cele vndute dup data impus prin cerin.
Considerm data vnzrii ca fiind data bonului de cas. Atunci cnd filtrarea se
impune asupra unui alt cmp dect cele de grupare i de sintetizat (a se vedea
Figura nr. 1.12) acest cmp va prezenta clauza WHERE.
Figura nr. 1.13 Exemplificarea modului de proiectare a interogrii de sintetizare propuse spre
rezolvare
Acest tip de interogare se va rezolva ca o interogare obinuit (prin precizarea
tabelelor surs, a cmpurilor selectate i a criteriului de filtrare) urmnd ca
sintetizarea datelor s fie stabilit prin selectarea unor valori din lista de valori
aferent fiecrui cmp pe rndul Total. Acest rnd devine vizibil odat cu apsarea
butonului Total situat pe bara de meniuri a grupului DesignShow/Hide (Figura nr.
1.13). Pentru cmpurile de grupare se va alege ntotdeauna Group By, pentru
cmpurile de filtrare opiunea Where, iar sintetizarea datelor aferente cmpului
-
Baze de date Microsoft Access
Pagina 21 din 29
calculat se va realiza prin selectarea unei funcii de sintetizare din lista aferent
aceluiai rnd Total.
1.2.2 Cereri de tip tabel ncruciat (CrossTab)
O categorie aparte de cereri care au la baz agregarea i sintetizarea informaiei pe
mai multe grupri de date o reprezint interogrile de tip analiz ncruciat. Aceste
interogri sunt utile n scopul analizei datelor permind obinerea unor situaii
sintetice asemntoare tabelelor pivot realizate prin procesoarele de tabele (ca de
exemplu Microsoft Excel). Practic, este posibil elaborarea unor tabele n care
gruparea i ordonarea datelor se realizeaz att pe linii ct i pe coloane la
intersecia crora se pot efectua calcule complexe.
Etapele ce trebuie urmate pentru realizarea unei astfel de cereri de interogare sunt
urmtoarele:
Se va crea o interogare de selecie n modul Design View, pe baza tabelelor
ce conin datele necesare i se vor selecta cmpurile dorite pentru afiare i
eventualele cmpuri pentru care se vor impune restricii.
Din grupul DesignQueryType se va selecta opiunea CrossTab ce va avea
ca efect imediat afiarea rndurilor Total i Crosstab n grila interogrii.
Se va specifica dup care cmpuri se va realiza gruparea datelor, respectiv
funciile totalizatoare pe rndul Total, i clauza Where pentru cmpurile de
filtrare suplimentar.
Rndul CrossTab permite declararea modului de dispunere a informaiei n
cadrul rezultatului: Row Heading n cazul cmpurile ce vor fi afiate pe liniile
tabelului, Column Heading pentru cmpul ce va fi afiat pe vertical, i Value
pentru valorile ce vor fi afiate la intersecia liniilor cu coloanele. Este permis
existena mai multor cmpuri ordonate pe orizontal (Row Heading), dar a
unui singur cmp Column Heading i a unui singur cmp Value.
Pentru exemplificare vom parcurge etapele prezentate anterior pentru a rspunde
cerinei: S se afieze pentru fiecare produs care este valoare total vndut de
fiecare vnztor pe anul 2011.
Vom conveni plasarea cmpurilor Nume, respectiv, Prenume din tabelul Vanzatori pe
liniile situaiei centralizatoare, cmpul Denumire din tabelul Produse pe coloana
acesteia, iar la intersecia lor se va plasa valoarea total, calculat ca sum a
produsului dintre cmpul Cantitate i Pre din tabelul ProduseVandute. Filtrarea
datelor se va realiza utiliznd cmpul DataOraBonCasa din tabelul BonCasa din care
se va extrage anul utiliznd funcia Year. Modul de proiectare a interogrii potrivit
cerinei enunate este prezentat n Figura nr. 1.14.
-
Baze de date Microsoft Access
Pagina 22 din 29
Figura nr. 1.14 Modalitatea de rezolvare a interogrilor de tip CrossTab
1.2.3 Crearea cererilor de aciune
Aa cum am putut observa, la nivel de tabel sunt permise operaiunile de actualizare
a datelor (adugri, modificri sau tergeri ale nregistrrilor din tabele). Exist, ns,
situaii cnd actualizrile individuale ale anumitor nregistrri din cadrul tabelelor nu
sunt eficiente, atunci cnd numrul nregistrrilor care ar trebui actualizate n acelai
mod este mare. n asemenea cazuri, se justific actualizarea n mas a
nregistrrilor (desemnnd adugarea, modificarea sau tergerea mai multor
nregistrri din acelai tabel printr-o singur operaie, specificnd tiparul de
actualizare).
Cererile de aciune sunt o categorie aparte a cererilor de interogare a bazelor de
date, pentru c efectueaz schimbri la nivelul bazei de date, aceste schimbri
afectnd att structura tabelelor (interogri pentru crearea de noi tabele), ct i
datele coninute de acestea (celelalte interogri de aciune). De aceea, se impune s
manifestm o anumit pruden n lucrul cu aceste tipuri de cereri, ntruct rezultatul
lor este iremediabil (de exemplu, odat terse anumite nregistrri, ele nu mai pot fi
recuperate).
Specific acestor tipuri de interogri este faptul c ele trebuie lansate n execuie doar
prin comanda Run din meniul Query sau prin apsarea butonului de pe bara de
instrumente.
-
Baze de date Microsoft Access
Pagina 23 din 29
1.2.3.1 Cereri pentru modificarea datelor din nregistrrile unui tabel (Update
Query)
Sunt interogri utilizate pentru actualizarea nregistrrilor dintr-o tabel, care
corespund unui anumit criteriu. Pentru a se efectua actualizarea, trebuie s
precizm, la nivelul unei astfel de cereri, care sunt cmpurile asupra crora se aplic
criteriul de modificare, care sunt cmpurile ale cror date vor fi modificate i care
este formula de modificare a acestor date, sau valoarea care se va stoca pentru
cmpurile de modificat. La fel ca i n cazul celorlalte tipuri de interogri, mai nti are
loc filtrarea nregistrrilor urmnd ca setul de nregistrri rezultat n urma filtrrii s fie
supus mai departe operaiunii de modificare a datelor din cmpul sau cmpurile
actualizabile. Dac nu exist un criteriu de filtrare a nregistrrilor atunci modificarea
va afecta toate nregistrrile din tabelul respectiv.
Actualizarea cmpului modificat se poate efectua fie pe baza unei valori scalare
(exemplu: Procentul de comision din vnzri ale casierilor angajai nainte de anul
2008 s fie 6%), fie prin implicarea valorilor existente ale cmpului modificabil
(exemplu: s se majoreze cu 2 procente comisionul casierilor angajai n anul 2008),
fie prin implicarea valorilor altor cmpuri (exemplu: dac casierul a fost angajat n
anul 2008, procentul de comision este de 5%, altfel este de 6%).
Pentru exemplificare, vom enuna o cerere de modificare prin majorarea valorilor
existente ale unui cmp dintr-un tabel: s se majoreze cu 30 zile termenele de
valabilitate n zile ale produselor din categoria cu codul 1. Cmpul de modificat, n
aceast situaie, este Termenul de valabilitate din tabelul Produs. Actualizarea se va
realiza doar pentru produsele care fac parte din categoria cu codul 1, deci cmpul de
filtrare va fi CodCategorie din cadrul aceluiai tabel. Rezolvarea presupune crearea
unei interogri de selecie avnd cele dou cmpuri specificate mai sus, i criteriul de
filtrare CodCategorie=1. Pasul urmtor definete tipul interogrii: din Fila Design
grupul Query Type se selecteaz tipul de interogare Update care va schimba grila
de interogare potrivit capturii din Figura nr. 1.15.
-
Baze de date Microsoft Access
Pagina 24 din 29
Figura nr. 1.15 Proiectarea unei interogri de modificare a datelor pe baza unei formule
Executarea interogrii prin apsarea butonului din grupul DesignResult va
aduce modificrile solicitate la nivelul tuturor nregistrrilor din tabelul Produse
implicate potrivit criteriului de filtrare. Pentru a putea vizualiza rezultatele, se va
deschide tabelul Produse din grupul de obiecte Tables.
Exemplificarea modului de proiectare a interogrilor de modificare a datelor care in
cont i de valoarea altor cmpuri n operaiunea de actualizare, vom enuna i explica
modul de rezolvare a urmtoarei cerine: S se majoreze termenul de valabilitate al
produselor din categoria cu codul 1 cu 5 zile, dac acestea sunt perisabile i cu 30
de zile, dac nu sunt perisabile.
Se observ, prin urmare, c alturi de cmpul CodCategorie, considerat cmp de
filtrare pentru interogarea noastr, modificarea se va realiza difereniat, n funcie de
valoarea unui alt cmp din tabelul de actualizat, cmpul Perisabil. Acest cmp ia
-
Baze de date Microsoft Access
Pagina 25 din 29
valori logice (True sau False), n funcie de care depinde cu cte zile se va majora
termenul de valabilitate a acelor produse vizate. Rezolvarea presupune o formul de
modificare a cmpului TermenValabilitate(zile) care va verifica valoarea cmpului
Perisabil prin intermediul funciei IIF. Aceast funcie permite evaluarea unei condiii
i returnarea a cte unui rezultat n funcie de rezultatul condiiei. Se va utiliza, prin
urmare, formula de actualizare:
IIF([Perisabil]=False;[TermenValabilitate(zile)]+30; [TermenValabilitate(zile)]+5)
Modul de proiectare al interogrii este explicat n Figura nr. 1.16.
Figura nr. 1.16 Rezolvarea interogrii de modificare a datelor utiliznd o formula de actualizare
complex
Se observ c pe rndul Update To toate denumirile de cmpuri trebuie ncadrate
ntre paranteze drepte.
1.2.3.2 Cereri pentru tergerea nregistrrilor din tabele
Pentru a terge dintr-o tabel anumite nregistrri care ndeplinesc un anumit criteriu
de selecie, se pot defini interogri de aciune. Cum tergerea se realizeaz
ntotdeauna la nivel de nregistrare (i nu la nivel de cmp), important n aceast
situaie este criteriul prin care se realizeaz filtrarea nregistrrilor care urmeaz a fi
terse (dac nici un criteriu nu se precizeaz, se vor terge toate nregistrrile din
cadrul tabelului respectiv).
Pentru efectuarea unei tergeri n mas a nregistrrilor potrivit unui criteriu stabilit,
trebuie procedat astfel:
Se va crea o interogare nou pe baza tabelei n care se va efectua tergerea
nregistrrilor;
-
Baze de date Microsoft Access
Pagina 26 din 29
Se aduce n cadrul grilei cmpul sau cmpurile pentru care se vor defini
criteriile de selecie i se vor preciza aceste criterii pe rndul Criteria;
Se va schimba tipul cererii din Select Query n Delete Query, utiliznd
opiunea Query Type->Delete Query;
Se lanseaz cererea n execuie prin apelarea opiunii Run.
Vom exemplifica prin crearea unei cereri de tergere a produselor vndute prin
bonurile de cas cu numrul 2 i 3. Se observ c tabelul din care se solicit
tergerea este ProduseVndute. Se cere tergerea acelor nregistrri pentru care
numrul bonului de cas este 2 i 3. Prin urmare, cmpul de filtrare este NrBonCas
situat n cadrul aceluiai tabel.
Fereastra QBE va conine urmtoarele elemente:
Figura nr. 1.17 Definirea unei interogri de tergere a datelor dintr-un tabel
Not: tergerea nregistrrilor, la fel ca i modificarea acestora, trebuie s respecte
restriciile impuse la nivel de tabel i asociere ntre tabele. Astfel, actualizrile asupra
nregistrrilor unui tabel vor ine cont de restriciile asociate integritii refereniale
(modificarea n cascad a cmpurilor legate, respectiv, tergerea n cascad a
nregistrrilor asociate). Dac aceste opiuni nu sunt bifate la nivelul proprietilor
asocierii tabelului respectiv cu alte tabele care dein o cheie extern pentru acesta,
atunci actualizarea acestuia nu va fi posibil.
1.2.3.3 Interogrile pentru crearea de noi tabele (Make Table Query)
Acest tip de interogare permite generarea unei noi tabele plecnd de la datele
stocate ntr-una sau mai multe tabele, sau de la rezultatul unei interogri. La baz,
aceast interogare se va construi pe structura unei interogri de selecie normale, la
finalul creia se va schimba tipul de interogare ntr-una de creare de tabel pe baza
rezultatului acesteia. Prin urmare, scopul acestui tip de interogare rezid n a stoca
-
Baze de date Microsoft Access
Pagina 27 din 29
rezultatul unei foi de rspuns dinamic sub forma unui tabel pentru a surprinde o
situaie dintr-un anumit moment dat.
Acest tip de interogare se poate crea urmnd o anumit succesiune de pai:
a. crearea unei interogri de selecie cu toate elementele ei (cmpuri sortate,
criterii de filtrare, cmpuri calculate, sintetizri de date,etc.).
b. transformarea acestei interogri n tipul de creare a unui nou tabel. Acest
pas const n alegerea tipului de interogare Make Table Query din grupul
DesignQuery Type.
c. precizarea n cadrul casetei de dialog care apare a numelui tabelului nou
creat, tabel care se va regsi dup executarea cererii de interogare n
grupul de obiecte Tables.
d. executarea cererii de interogare prin apelarea meniului Query- Run.
Rezolvarea unei cerine exemplificative: S se creeze un tabel nou cu toi vnztorii
care au vndut produse n valoare total mai mare de 3000 lei n anul 2012.
Pasul 1: crearea interogrii ce va afia numele i prenumele vnztorilor care n anul
2012 au vndut produse n valoare mai mare de 3000 lei (interogare pentru
sintetizarea datelor aferente cmpului calculat valoare). Modul de proiectare a
acestei interogri este prezentat n Figura nr. 1.18.
Figura nr. 1.18 Crearea unei interogri pentru generarea unui tabel nou
Pasul 2: Schimbarea tipului interogrii din grupul Query Type al meniului Design din
Select n Make Table.
Pasul 3: Precizarea numelui tabelului ce va fi creat: CasieriVanzri (Figura nr. 1.19)
-
Baze de date Microsoft Access
Pagina 28 din 29
Figura nr. 1.19 Modificarea tipului de interogare pentru crearea unui nou tabel
Pasul 4: Executarea interogrii de creare a tabelului (prin apsarea butonului Run) i
vizualizarea informaiilor din cadrul tabelului nou creat.
n fereastra de navigare a bazei de date va fi prezent un nou tabel cu numele atribuit
la pasul anterior care va conine toate informaiile pe care interogarea le-a specificat
prin fereastra de proiectare QBE.
1.2.3.4 Interogri pentru adugarea de nregistrri (Append Query)
Interogrile pentru adugare ofer utilizatorilor posibilitatea inserrii de noi
nregistrri ntr-un tabel existent n baza de date pornind de la nregistrrile existente.
Pentru a realiza o interogare de tip Append este necesar parcurgerea urmtoarelor
etape:
1. Elaborarea unei interogri de selecie n modul Design View. Se va alege tabela ce
conine datele i vor fi luate n calcul doar cmpurile ce conin valorile ce vor fi
adugate, iar, n cazul n care se dorete aplicarea unor criterii de selecie a
acestora, se vor aduga cmpurile de restricionat.
2. Se vor tasta eventualele condiii n rndul de criterii al interogrii.
3. Din grupul DesignQuery Type se va selecta opiunea Append Query ce va
conduce la afiarea casetei Append n care se va selecta numele tabelei n care
dorim adugarea.
-
Baze de date Microsoft Access
Pagina 29 din 29
4. Transformarea interogrii de selecie ntr-o interogare de adugare are ca efect
imediat apariia rndului Append To n fereastra de proiectare a interogrii. n acest
rnd se va specifica pentru fiecare cmp din tabela ce conine datele denumirea
cmpului corespondent din tabela unde vor fi adugate.
Pentru exemplificare propunem spre rezolvare urmtorul exemplu: S se adauge n
tabelul generat anterior prin interogare de tip creare tabel i vnztorii care n anul
2011 au vndut produse n valoare mai mic de 3000 lei.
Modul de rezolvare, respectnd paii expui anterior, este prezentat n Figura nr.
1.20.
Figura nr. 1.20 Proiectarea unei interogri pentru adugarea datelor ntr-un tabel