Fisa disciplinei 3 Access.pdf

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).


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:


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.


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.


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,


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.


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


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: (>, >=,


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


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.


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


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.


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,


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.


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:


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.


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


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


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.


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.


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.


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


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;


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


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)


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.


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

Fisa disciplinei 3 Access.pdf

Documents

Transcript of Fisa disciplinei 3 Access.pdf