Kreiranje upita

Kada otvorite neku tabelu baze podataka, u većini slučajeva će te videti samo cifre (osim u osnovnim tabelama gde se čuvaju podaci o robi, zaposlenima i slično). Ovo je zbog optimizacije. Kada jednom unesete neki proizvod u tabelu roba, u drugim tabelama koristite samo šifru.

Pošto baza Magacinsko poslovanje ima sva rešenja i nije pogodna za dalji rad sa upitima, novu bazu, u kojoj su samo tabele možete preuzeti ovde Querymag.

Bazu Magacinsko poslovanje će te nadalje koristiti samo za upoređivanje i proveru zadataka koje radite samostalno, kao i za probu drugih opcija koje učite u nastavku kursa (knjiženje faktura kod formi, provera ulaznih i izlaznih naloga, izrada izveštaja,…)

Zadatak. Otvorite tabelu Lager (baza Querymag) i proverite prethodnu tvrdnju. U ovu tabelu se, prilikom ažuriranja baze – nabavka i prodaja, unose količine i šifre proizvoda. Ovo je inače tabela koja nam je potrebna za proveru trenutnog lagera robe.

Da bi videli o kom proizvodu se radi, potrebno je da za neku šifru, iz tabele Roba, a preko relacija „pročitate“ naziv i opis proizvoda. Upravo za ovu namenu nam je potreban upit (Query). Pomoću upita možete da „izvučete“  tražene podatke iz jedne ili više tabela. Upite možete kreirati i na osnovu drugih upita. Možete čak i da menjate podatke u tabelama ili pak da pravite nove tabele sa samo potrebnim podacima. Upiti se takođe mogu koristiti i kao osnova za izadu izveštaja ili formi.

Vrste upita

• Select Query. Ovo je najčešće korišćen tip upita. Omogućava traženje određenih podataka, njihovo grupisanje i eventualno sumiranje. Kada pokrenete upit dobijate dinamički set podataka. To znači da su samo određeni podaci izvučeni iz tabela i prikazani u novoj tabeli. Kada zatvorite upit, podaci su i dalje u tabelama.
• Parameter Query. Ovaj tip upita, pri pokretanju, traži od vas da unesete parametar tj. kriterijum za filtriranje podataka koji će se prikazati. Dosta se primenjuju upravo zbog njihove fleksibilnosti. Npr. ako želite da vidite prodaju za zadati datum, onda datum stavljamo kao parametar i upit će iz tabela za prodaju izvući samo stavke koje zadovoljavaju taj kriterijum.
• Crosstab Query. Prikazuje podatke unakrsno, sa levim i gornjim zaglavljem. Dobri su za razna analiziranja (mesečna, kvartalna, …) jer omogućavaju da se podaci prebrojavaju, računaju prosečne vrednosti i slično.
• Action Query. Sam naziv kaže da ovi upiti izvode neke akcije. To znači da mogu da menjaju podatke u tabelama (Delete, Update, Insert).
• Union Query. Upiti koji od dva skupa podataka, iste strukture, prave uniju.

Kreiranje upita

Upite možete kreirati pomoću čarobnjaka ili u dizajn modu. Ja sam pristalica dizajn moda. Potrebno je da znate kako su povezane tabele i da imate zadatak tj. da znate šta se traži od podataka.

Zadatak. Prikažite trenutni lager u svim magacinima, tako da se vide sledeća polja: Nazivmagacina, Sifrarobe, Nazivrobe, Dimenzije, Dezen, Jedinicamere, Količina, Mpcena. Upit snimite kao Trenutni lager.

Upit kreirate na tabu Create izborom komande Query Design. Otvara se prozor Show Table gde birate potrebne tabele. U našem slušaju izaberite samo tabelu Lager. Tabelu Roba će mo dodati kasnije. Sada treba da prevučete imena polja (Siframagacina, Sifrarobe i Kolicina) levim tasterom miša u donju zonu tj. u Field (može i dupli klik na ime polja).

Da bi proverili, da li upit radi, pokrenite upit na dugme Wiew u gornjem levom uglu. To isto dugme sada postaje Design i pomoću njega se vraćate u dizajn mod. To znači da pomoću ovog komandnog dugmeta možete stalno da prelazite iz jednog moda u drugi (Wiew/Design) i tako pratite razvoj upita.

Proverite koliko slogova ima upit. Dobićet nešto više od 5300 tj. isto koliko i tabela Lager. To je zato što nismo ubacili ni jedan kriterijum, ali o kriterijumima će mo kasnije. U upitu se još uvek ne zna o kojim proizvodima je reč. Potrebno je da u upit ubacite i tabelu Roba, i da iz nje dodate polja Nazivrobe, Dimenzije, Dezen, Jedinicamere, Mpcena. To će te uraditi pomoću desnog klika na pozadinu upita, pa Show Table i dodate tabelu Roba. Kada dodate potrebna polja potrebno je da ih premestite po redosledu traženom u zadatku tj. polje Kolicina ide ispred polja Mpcena. Kolona se premešta tako što je najpre selektujete klikom na vrh kolone (obeleženo crvenom linijom na slici dole) pa je onda ”uhvatite” levim tasterom miša za vrh i odvučete na željenu poziciju.

Pokrenite sada upit. Videćete da se pored šifre robe vide i ostala polja koja smo ”povukli” iz tabele Roba. Međutim, zadatak još uvek nije gotov. U zadatku se tražilo da se vidi naziv magacina a ne šifra magacina. To znači da treba da se u upit ubaci i tabela Magacini, i da iz nje ubacite polje Nazivmagacina a iz upita obrišete Siframagacina. Sortirajte podatke u rastućem redosledu, kao na slici dole, i pokrenite upit.

Ukoliko se, kada pokrenete upit, vide i polja koja niste ubacili u upit, potrebno je da uključite Property Sheet i da opciju Output All Fields postavite na No. Posle ovoga će se u upitu videti samo tražena polja. Ista opcija važi i za korišćenje dugmeta Totals (dobijate poruku Cannot group on fields selected with  *  ) koje obrađujemo u nastavku.

Kada uključite dugme Totals, dobijate mogućnost da grupišete podatke ili da primenite neku od ponuđenih funkcija (Sum, Avg, Min, Max, Count,…). U tabelu Lager, za svaku šifru robe su unete količine, i to pozitivne od nabavke i negativne od prodaje. Kada grupišemo slogove prema šifri i primenimo funkciju Sum na količinu, dobićemo sabrane količine tj. trenutni lager za pojedinačne šifre. Broj slogova u upitu nije više 5325 nego 1055.

Zadatak. Napravite upit o prodaji robe kao na slici dole i snimite ga kao Prodaja.

Upiti sa kriterijumima. Operatori. Rad sa izrazima.

Kriterijumi u upitima nam omogućavaju da dodatno filtriramo i izdvojimo samo podatke koji su nam potrebni. Kriterijumi se primenjuju na polje i zavise od tipova podataka. Mogu biti brojevi, konstante ili izrazi sa operatorima. Kriterijumi se unose u polje Criteria, i sintaksa je vrlo bitna.

Kriterijumi za polje tipa Text se unose između navodnika, npr. “Beograd” . U ovm slučaju se traži podudaranje. Suprotno ovome je Not “Beograd”. Ako koristite Like B* program izbacuje sva polja koja počinju slovom B. * je u stvari džoker znak i predstavlja bilo koji niz karaktera. Slično ovome, možete koristiti i Not Like.

Kriterijumi za polje tipa Number su najčešće kombinacije logičkih operatora i brojeva. Npr. >=100. Ukoliko stoji samo broj, traži se podudaranje. Pored ovoga, moguće su i kombinacije tipa >50 and <150 ili Between 50 And 150.

Za tip Date važe slična pravila kao za brojeve. Razlika je u tome što se datum izdvaja znakom #. Npr. #06.06.2012# ili prva polovina godine <=#06.06.2012# ili drugi kvartal Between #01.04.2012# And #30.06.2012#. Za datum se mogu koristiti i funkcije.

Zadatak. Otvorite upit Trenutni lager i podesite da se vidi lager robe samo u magacinu Beograd. Takođe, podesite da se ne prikazuje roba koje nema na lageru. Pomoću Save As snimite upit kao Lager Beograd.

Upit sada ima samo 325 slogova jer nisu povučeni slogovi koji ne zadovoljavaju kriterijume. Ako bi napravili upit i za magacin Niš, imali bi dva upita za lager robe. Šta bi bilo ako bi imali 10 ili više prodavnica? Odgovor na ovo pitanje jesu parametarski upiti koje obrađujemo kasnije u nastavku.

Zadatak. Potrebno je da se uradi akcija sniženja cena artikala kojih je na stanju više od 5. Napravite upit koji prikazuje robu iz prodavnica u Nišu i Beogradu, a koje je na lageru više od 5. Upit snimite kao Lager NB. Usput, ne morate da krećete od nule. Možete da otvorite upit Trenutni lager, da ubacite kriterijume i snimite ga pod traženim imenom. Pored ovoga, iskopirajte rezultat upita u Excel i izračunajte maloprodajnu vrednost robe koja treba da se snizi (Totale u upitima radimo u sledećem zadatku).

U ovom slučaju smo koristili OR tj. kombinaciju dva uslova. Upit je sada iz tabela povukao samo 53 slogova koji zadovoljavaju tražene kriterijume.

Zadatak. Napravite upit Dnevna prodaja koji prikazuje šta je sve prodato u Beogradu (mag. sa šifrom 3) 10.12.2012. godine. Izračunajte Iznos=Kolicina*Pcena i podesite da se prikazuje sa dve decimale i sa odvajanjem hiljadarki. Pokušajte da ”krenete od nule” tj. da sami napravite upit kako bi se uvežbali. Ukoliko ne uspete, možete da otvorite upit Prodaja i da ga modifikujete. Logika je ista. Novina u ovom zadatku jeste polje za računanje tj. Iznos. Na kraju, pomoću dugmeta Totals, u  View modu, izračunajte promet za zadati datum.

Kod polja za računanje, važi se matematika koju znate. Operatori su isti. Poštujte prioritete računskih operacija i zagrade, i nećete da pogrešite. Jedino se umesto znaka jednako koriste dve tačke (operator dodeljivanja). To znači da smo promenljivoj Iznos dodelili vrednost proizvoda količine i prodajne cene. Da bi podesili format brojeva, ide desni klik na polje Iznos, pa Properties… Dalje je sve jasno. Rešenje je na slici dole.

Možda ste primetili da smo koristili Pcena iz tabele Stavkein a ne Mpcena iz tabele Roba. To je zato što se u tabelu Roba upisuje maloprodajna cena iz kalkulacije, a u trgovini nije baš sve idealno. Kupci se pogađaju, trgovci daju popuste, menjaju cenu i rade nivelaciju.

Zadatak. Napravite upit koji prikazuje šta je sve od robe stiglo juna meseca 2012. u obe prodavnice od dobavljača sa šifrom 3. Izračunajte i nabavnu vrednost robe.

Rešenje za ovaj zadatak nećete dobiti. Može da bude i domaći zadatak. Pomoć: za datum koristite Between #1.6.2012# And #30.6.2012#. Nabavna vrednost je Nabvr: Kolicina*Ncena.

Parametarski upiti

U prethodnom delu sam istakao jedan problem sa gomilanjem upita u bazi. Ako imamo više prodavnica, traba nam više upita za trenutni lager. Za prodaju, tj. dnevni izveštaj prodate robe ne možemo da pravimo upite za svaki dan pojedinačno. Umesto toga, napravićemo samo jedan upit za trenutni lager i samo jedan upit za dnevni pregled prodate robe.

Npr. umesto konkretnog datuma, u kriterijum unosimo parametar [Unesite datum]. Ovo je inače sintaksa za parametarske upite. U srednjim zagradama se unosi poruka koju operater vidi. Kada pokrenete upit program od vas traži da unesete datum. To znači da imamo samo jedan upit za dnevnu prodaju i da, kada ga pokrenemo, možemo da ukucamo bilo koji datum.

Zadatak. Otvorite upit Trenutni lager i podesite ga da, prilikom pokretanja, od vas traži da unesete naziv magcina. Pomoću Save As snimite upit kao Trenutni lager X. Probajte da li radi. Prilikom prvog pokretanja unesite Beograd, a pri ponovnom pokretanju, unesite Niš.

Zadatak. Napravite nov upit Dnevna prodaja XX koji prilikom pokretanja od vas traži da unesete dva parametra, naziv magacina i datum. Na ovaj način pravite univerzalni upit za analizu dnevne prodaje tj. možete da izvučete podatke o prodaji za bilo koji datum u bilo kom magacinu. Izračunajte Iznos=Kolicina*Pcena i podesite da se prikazuje sa dve decimale i sa odvajanjem hiljadarki. Test za upit: Naziv magacina – BEOGRAD; Datum – 10.12.2012. (ili bilo koji drugi datum; magacin može da bude i Niš).

Zadatak. Napravite upit o analizi prodaje za određeni period (bilo koji – mesečni, kvartalni,…) u oba magacina. Vremenski period se bira preko dva parametra početni i krajnji datum. Uradite sami. Pomoć: Between [Unesite početni datum] And [Unesite krajnji datum].