Unakrsni upit

Unakrsni upit (Crosstab Query) je najsličniji pivot tabeli koje smo obrađivali u Excelu. To je tabela koja ima levo i gornje zaglavlje i kao takva, pogodna je za različite vrste poslovnih analiza. U ovoj lekciji, obradićemo mesečnu analizu prodaje robe (grupisane po dobavljačima) u maloprodaji. Koristićemo bazu Querymag. Pošto se radi o prodaji, koristićemo tabele Izlazninalog i Stavkein. Krećemo najpre od običnog upita, koga u toku rada prepravljamo na unakrsni. Zadatak je prilično složen.

Zadatak. Napraviti select query Prodaja po indeksu kao na slici dole. Indeks je u stvari grupa proizvoda, odnosno u našem slučaju dobavljač. Slogovi treba da se odnose na maloprodaju.

Proveriti rezultat upita na dugme View. Rezultat je 2480 slogova. Uključite sada dugme Totals, podesite da se sabira količina (funkcija SUM na liniji Totals), i proverite rezultat upita na View. Rezultat je, 40 slogova jer se za svaki magacin određeni index (dobavljač) javlja samo jednom. Broj slogova je značajno smanjen jer je program najpre grupisao šifru magacina, pa je za svakog dobavljača sabrao količinu. Za magacin broj 3, prodavana je roba od 21 dobavljača, a za magacin 4 od 19 dobavljača. Da smo u zadatku, umesto Index koristili šifru proizvoda ili naziv robe, broj slogova bi zavisio od šifre robe odnosno od naziva tj. broj slogova bi bio mnogo veći.

Zadatak. Pošto se u zadatku traži mesečna analiza prodaje, potrebno je da u upit dodate polje Datum i to tako da se prikazuje samo mesec.

Da bi ovo rešili, potrebno je da u novo polje primenite funkciju Format([Izlazninalog].[Datum];'mmm’). Ova funkcija konvertuje pojedinačne datume u mesece. Ako sada pogledate reultat upita, videćete da imamo 254 slogova. To je zato što sada, za svaku šifru mgacina imate po 12 meseci, pa puta za svaki mesec broj dobavljača.

Zadatak. (Nije obavezan.) Ovde može da se napravi presek jer mogu da se iskopiraju podaci iz upita u Excel i da se napravi pivot tabela. Kada pokrenete upit, primenite Ctrl+A, pa Ctrl+C, otvorite novu Excel datoteku, pa Ctrl+V. Na osnovu dobijenog, uradite pivot tabelu. Na slici dole je broj prodatih komada po magacinima i mesecima od dobavljača 2, 3, 4 i 5 (filtriranje).

Vraćamo se na naš upit u Accessu.

U dizajn modu, na tabu Design uključite dugme Crosstab. Pojaviće se još jedna linija dole gde piše Crostab:. Ovde, kao i u Excelu, treba da odredite koja polja su zaglavlja kolona a koja zaglavlja vrsta. Takođe, određuju se i podaci za prikaz. To su polja Row Heading, Column Heading i Value.

Za šifru magacina i indeks stavite Row Heading, za mesece Column Heading i za količinu Value. Pokrenite sada upit i videćete rezultat. Dobili smo unakrsnu tabelu sa 40 slogova kao na početku, ali sa više kolona za mesece. Meseci nisu sortirani, pa će mo to uraditi u narednom koraku.

Desni klik na polje za mesece, biramo Properties…, pa u Column Headings unosimo sledeće: “jan”;”feb”;”mar”;”apr”;”maj”;”jun”;”jul”;”avg”;”sep”;”okt”;”nov”;”dec”. Rezultat upita je sada isti kao kod pivot tabele u Excelu  (slika gore).

Funkcije za rad sa tekstom i datumima

U prethodnom primeru smo primenili funkciju Format, kako bi datum predstavili na drugi način. Međutim, većina funkcija u Accessu se koristi u modulima VBA (Visual Basic for Applications), tako da za učenike srednjih ekonomskih škola ovo predstavlja viši nivo i ja se ovde ne bih previše zadržavao. Bitno je da znate da ove funkcije postoje, tako da, ako vam zatrebaju možete da ih primenite. Informativno, pogledajte sledeći link:

https://support.office.com

Evo jednog primera sa funkcijama DatePart i Date. Treba prikazati robu koja je stigla juna meseca u svim magacinima, od dobavljača sa šifrom 3. Proveriti i broj dana od kada je stigla roba, a zbog valute plaćanja.

DatePart(“m”;[ulazninalog.Datum]) sa kriterijumom 6 ukazuje na to da će se izvući datumi iz šestog meseca. Slovo “m” je povezano sa rednim brojem meseca i može biti od 1 do 12. Ako bi umesto slova “m” upotrebili slovo “q” ono bi se odnosilo na kvartale i vrednost bi bila od 1 do 4.

Broj dana: Date()-[Datum] izračunava broj dana od dana nabavke, tj. funkcija Date() predstavlja tekući datum. U našem slučaju, ako uradite zadatak, vrednost Broj dana će biti velika jer je baza iz 2012. godine.

NA NAREDNOM ČASU JE UTVRĐIVANJE, PA EVO ZADATAKA ZA VEŽBANJE.

Zadatak. Napravite upit za “kalkulaciju”, za robu koja je stigla 06.06.2012. u magacinu 3 od dobavljača 3. Izračunajte maloprodajnu cenu, ako je poreska stopa robe 20% a razlika u ceni 30%. Upit snimite kao KALKULACIJA.

Promenljive su:

• NBVR:Kolicina*Ncena (Nabavna vrednost = Količina * Nabavna cena)
• RUC:NBVR*0,3 (Razlika u ceni (Marža) = Nabavna vrednost * 30%)
• POREZ:(NBVR+RUC)*0,2 (Porez = (Nabavna vrednost + Marža) * 20%)
• MPVR:NBVR+RUC+POREZ (Maloprodajna vrednost je zbir predhodnih)
• MPCk:MPVR/Kolicina (Maloprodajna cena po jedinici za ovu kalkulaciju se dobija kada se maloprodajna vrednost podeli sa ukupno prispelom količinom.)
• NBVR jeste novac za dobavljača, RUC za vas a POREZ za državu.

Proveru možete da uradite na trgovački način pomoću kalkulatora i dobićete istu MPCk:

MPCk = Ncena*1,3*1,2 (Npr. za Proizvod 641 je 2309*1,3*1,2=3602,04)

Zadatak. Prethodni upit “prepravite” u parametarski, tako da radi kalkulacije sa 25% marže za bilo koji datum, odnosno magacin ili dobavljača, koje unosite kao parametre. Upit snimite kao KALKULACIJA XXX.

Podaci za testiranje su:  Šifra dobavljača – 2, Šira magacina – 3, Datum – 06.08.2012.

SLEDI OCENJIVANJE. Na ovom linku su zadaci za ocenu, za modul sa upitima, za dve grupe.

Akcioni upiti

• Make Table Query

Upiti za pravljenje tabela se koriste kada želite da napravite novu tabelu. Npr. iz velikih tabela ste, pomoću različitih kriterijuma izvukli određene podatke, onda te podatke možete da spakujete u novu tabelu.

• Append Query

Upit za dodavanje slogova služi za dopunjavanje nekih tabela sa podacima iz drugih tabela.

• Update Query

Upit za ažuriranje se koristi za promenu podataka u tabelama. Pre toga, neophodno je da se pažljivo odrede kriterijumi za slogove koji se ažuriraju, jer ako se predomislite, nema povratka.

• Delete Query

Upit za brisanje se koristi za brisanje pojedinih slogova iz tabele, a na osnovu kriterijuma koje definišete. Kao i kod prethodnog upita, i ovde vam se pruža mogućnost da vidite slogove koje brišete odnosno menjate.

 Napomena. Ova lekcija (akcioni upiti) je više informativnog karaktera, i ja ovaj dvočas koristim za sistematizaciju i utvrđivanje gradiva  jer prelazimo na nov modul – izveštaje. Korišćenje akcionih upita je nešto složeniji posao, i zahteva izvesno iskustvo. Dobar deo radnji se ne može opozvati. Ukoliko ipak rešite da probate neki od ovih upita, obavezno napravite rezervnu kopiju baze.