Microsoft Office Access
uređuje Branislav Mihaljev, MVP

Druga najveća vrednost

Nivo:
NIVO 3 - Prilozi na ovom nivou su namenjeni naprednim korisnicima, onima koji se već dobro snalaze u programima i onima koji već imaju iskustva u naprednim tehnikama. Ovom nivou pripadaju preporuke i trikovi koje bi trebalo da savlada svako ko teži dugotrajnom i efikasnom radu u Office programima.

Malo gimnastike sa upitima: ostvarite željeni pregled rang liste na način koji nije dostupan u uobičajenom pregledu tabele.

Najmanje ili najveće vrednosti možete videti direktno u tabeli, kada sortirate određenu kolonu po rastućim ili opadajućim vrednostima. Ovakve vrednosti se ipak ne mogu koristiti za dalju automatsku obradu, pa se one zahtevaju upitima. Upitom lako dolazite do jedne ili više najvećih, odnosno najmanjih vrednosti: napravite zbirni upit (Total query), potom nad kolonom iz koje tražite najveće vrednosti postavite osobinu Total na Max, pa je najzad sortirajte po opadajućem nizu (Descending). Dalje sužavanje liste na određeni broj vrednosti možete obaviti unošenjem proizvoljnog broja u padajuću listu Top Values i to onoliko koliko najvećih/najmanjih vrednosti želite da vidite.

Mana ove procedure je da za svaki zapis nećete videti njegovu najveću vrednost. Recimo, ako u jednoj tabeli držite zapise studenata i njihove bodove postignute na testu i zahtevate najveće ocene za svakog od njih, rezultat nećete dobiti ovim upitom - možete prikazati samo određeni broj najvećih ocena bilo kog studenta.

Rešenje je jednostavno i sastoji se od dva upita. U prvom upitu izdvojite nazive studenata bez ponavljanja i zatim ovaj upit zajedno sa tabelom sa istom tabelom studenata i bodova upotrebite za izradu drugog upita.

Proširićemo zadatak: umesto da tražimo najveći broj bodova za svakog studenta pojedinačno, locirajmo drugu najveću vrednost, odnosno pretposlednju najmanju vrednost. Situacije kada će vam zatrebati druga najveća vrednost su zaista retke, ali će vam opisana tehnika pomoći kod sličnih zahteva, kao i u situacijama kada vam zatreba jednostavnije rešenje koje je sadržano u ovom postupku.

Polazna tabela je jednostavna i sadrži dve kolone: IDStudent i Bod, obe numeričkog tipa. Upišite u nju brojeve od jedan do tri, za identifikatore studenata, pa dodajte i nekoliko vrednosti bodova između nula i 100. Potražimo prvo najveće vrednosti za svakog studenta. Kreirajte zbirni upit i grupišite studente kriterijumom GroupBy. U koloni Total za bodove studenata postavite kriterijum Max. SQL struktura upita glasi:

SELECT tblBodovi.IDStudent,
Max(tblBodovi.Bod) AS MaxOfBod
FROM tblBodovi
GROUP BY tblBodovi.IDStudent;

Snimite upit pod imenom qryPrviKorak. Drugi upit je mnogo kompleksniji, ali jednostavan za kreiranje. Dodajte u njega tabelu tblBodovi i prvi upit. Kreirajte dvostruku relaciju između tabele i upita prevlačenjem objekata IDStudent sa tabele na upit, a zatim prevlačenjem objekta Bod na MaxOfBod. Obe relacije su tipa INNER, što znači da će rezultat upita vraćati samo zapise koji su u obe tabele jednaki. Kliknite dva puta na svaku od relacija i odaberite tip relacije pod brojem 2. Sada ste kreirali dvostruku LEFT relaciju, koja će za rezultat vraćati sve zapise iz tabele i samo one zapise iz upita qryPrviKorak koji postoje u tabeli.

Dvostruka LEFT relacija

Pogledajte rezultat upita: prikazane su obe kolone tabele sa svim vrednostima, a u pridodatoj srednjoj koloni se nalaze ID brojevi studenata i to samo za zapise gde se pojavljuju najveće vrednosti bodova. Kako su ostala polja prazna, a polja sa zapisima sadrže brojeve, upotrebićemo filter nad kolonom qryPrviKorak.IDStudent tako da filtriramo prikaz i izbegnemo prikazivanje zapisa sa najvećim brojem bodova po studentu. Vratite se u režim izmene dizajna upita i u polju Criteria kolone IDStudent upita qryPrviKorak dodajte kriterijum Is Null.

Sada upit vraća sve vrednosti osim najvećih i možete ga zamisliti kao "običan" upit kojim tražite najveće vrednosti. U režimu izmene dizajna uključite Totals iz menija View i izmenite vrednost operatora Total polja Bod sa GroupBy na Max.

Upit radi sjajno i pronašli smo druge najveće vrednosti bodova. Pretpostavimo da se grupi priključio novi student i da on ima samo jedan zapis u tabeli bodova. Ovakav upit će ga odstraniti iz konačnog izveštaja jer druga najveća vrednost njegovih bodova ne postoji. Ako ovo nije ono što želite, izmenićete prvi upit. Otvorite upit qryPrviKorak u režimu izmene dizajna, prevucite još jednom polje IDStudent na mrežu upita i vrednost Total postavite na Count, tako da SQL struktura prvog upita sada izgleda ovako:

SELECT tblBodovi.IDStudent,
Max(tblBodovi.Bod) AS MaxOfBod,
Count(tblBodovi.IDStudent) AS
CountOfIDStudent
FROM tblBodovi
GROUP BY tblBodovi.IDStudent;

Snimite prvi upit i otvorite drugi za izmenu dizajna. Dodajte u mrežu upita novo-kreirano polje prvog upita CountOfIDStudent i u Criteria koloni Or ukucajte broj 1, tako da se broj 1 ne nalazi u istom redu gde i Is Null. Pošto se bodovi novog studenta pojavljuju samo jednom u tabeli, agregatna funkcija Count prvog upita će za njega vratiti rezultat 1, dok će za ostale studente iznositi 2 ili više. Prvom filteru za odstranjivanje najvećih vrednosti pridružili smo drugi sa logičkim operatorom ILI i konačno dobili upit koji vraća sve druge najveće vrednosti uključujući jedinstvene zapise tabele, za studente koji imaju samo jednu vrednost bodova.

Druga najmanja vrednost se dobija izmenom oba upita tako da agregatnu funkciju Max izmenite u Min. Najjednostavnije je da kopirate oba upita pod novim imenima i ponovo kreirate relaciju LEFT između polja Bod i MinOfBod. Na kraju, ako svi studenti imaju tačno tri vrednosti bodova, oba upita će vratiti isti rezultat!