Typy kwerend programu Access
Spis Treści
WSTĘP
Kwerendy w Accesie …………………...……………..…………………….…. 3
ROZDZIAŁ I
Tworzenie kwerend ……………………..………………..……….…………… 4
Użycie kwerendy ………………………………………………….………...…. 5
ROZDZIAŁ II
Kwerendy podsumowujące ……………. .……………………………………... 6
Funkcja agregująca Policz ……………………………...…………...….. 8
Funkcje agregujące Minimum i Maksimum …………………...……..… 9
Funkcje agregujące Pierwszy i Ostatni ………………………...………. 9
Funkcje agregujące Odchylenie standardowe i Wariancja …………..… 10
Funkcje agregujące Średnia ………………………………...…………. 10
Funkcja Wyrażenie ………………………………………...………..… 11
Funkcja Gdzie ……………………………………………...………….. 11
Kwerendy przekazujące ……………………………………………….……... 13
Kwerendy definiujące dane ………………………………………….....……. 13
Użycie kwerend krzyżowych ……………………………….………..……….. 14
Użycie kwerend parametrycznych ……………………….…………………... 14
Bibliografia ……………………………….………………………………….. 16
Spis ilustracji ………………………………………………………………… 16
WSTĘP
Kwerendy w Accesie
Kwerendy są elementami napędowymi baz danych Accessa. Występują w kilku formach i rodzajach. Mogą być zapisywane w bazie (jak tabele) lub działać tylko w pamięci komputera.
Do ich tworzenia można używać poleceń SQL lub graficznego interfejsu użytkownika nazywanego tabelą QBE. Kwerenda może zarówno przedstawiać dane w takiej postaci, w jakiej występują w bazie, jak również przed wyświetleniem grupować je i przeliczać. Kwerenda Accessa może już przed uruchomieniem wiedzieć, co powinna wykonać lub zadawać użytkownikowi dodatkowe pytania. Kwerendy potrafią także aktualizować dane, tworzyć
i usuwać rekordy, a nawet zmieniać strukturę bazy. Kwerendy w Accessie mogą pracować
z danymi przechowywanymi w plikach typu MDB, innych, przyłączonych do Accessa źródłach danych, a nawet bazach danych typu klient-serwer (np. Oracle i Microsoft SQL Server). Są one najszybszym i najpewniejszym sposobem efektywnej interakcji z danymi w relacyjnej bazie danych.
Dokładna konstrukcja bazy danych w połączeniu z możliwościami, jakie dają kwerendy, umożliwiają zaspokojenie części najprostszych, codziennych potrzeb w zarządzaniu danymi. Jednocześnie mogą pomóc w rozwiązaniu najbardziej złożonych problemów, gdyż relacyjna struktura pozwala na logiczne grupowanie danych. Dzięki temu, pomiędzy powiązanymi obiektami można tworzyć użyteczne połączenia. W relacyjnej bazie danych mamy wgląd w te dane, których w danym momencie potrzebujemy. Weźmy za przykład rachunek telefoniczny. Raz w miesiącu operator sieci telefonicznej przysyła rachunek za swoje usługi, a jeśli sobie tego zażyczyć – szczegółowy wykaz rozmów. Wykaz ten zawiera wszystkie informacje
o przeprowadzonych w poprzednim miesiącu rozmowach telefonicznych: datę, godzinę, numer telefonu osoby, do której się dzwoniło, czas trwania rozmowy, ilość naliczonych impulsów oraz całkowity koszt rozmowy. To bardzo dużo informacji, jednakże na podstawie samego wykazu trudno jest odpowiedzieć na najbardziej nawet podstawowe pytania. Dużo wygodniej byłoby móc połączyć go z książką adresową. Gdyby to było możliwe, mogłoby się dokładnie stwierdzić, do kogo dzwoniono, kiedy to było i ile ta rozmowa kosztowała. Można wówczas obliczyć, ile pieniędzy wydało się na rozmowy z pracownikami danego klienta i na jaką kwotę należy poszczególnych klientów obciążyć. Mogłoby się wydawać, że takiej obróbki danych można dokonać przy użyciu innych narzędzi niż relacyjna baza danych. Jednakże z prawidłowo zaprojektowaną bazą danych obróbka ta będzie banalnie łatwa, wiarygodna i szybka.
ROZDZIAŁ I
Tworzenie kwerend
Kwerendy, podobnie jak tabele i formularze, są obiektami bazy danych i przechowywane są
w znajdującej się w głównym oknie bazy grupie Kwerendy (skróty do nich mogą być przechowywane w dowolnej, stworzonej przez programistę grupie). Tworzenie kwerend wymaga ustalenia różnych właściwości, które nadzorują sposób, w jaki kwerenda się zachowuje podczas jej uruchamiania. Można zacząć od samej kwerendy. Wszystkie zapisane kwerendy można przeglądać, klikając obiekt Kwerendy znajdujący się w oknie Grupy głównego okna bazy danych. Kliknięcie prawym przyciskiem myszy którejś z pojedynczych kwerend powoduje wyświetlenie odpowiadającego jej okna dialogowego Właściwości (rysunek 1).
Rysunek 1. Okno dialogowe Właściwości wybranej kwerendy
Użycie kwerendy
Podobnie jak w przypadku wszystkich innych obiektów baz danych Accessa można stosować nazwy o długości nie przekraczającej 255 znaków. Również wszystkie konwencje nazewnictwa używane w przypadku pozostałych obiektów mają zastosowanie do kwerend. Okno Właściwości zawiera także informacje o typie danej kwerendy. Można tam również znaleźć jej opis. Jest to bardzo ważna właściwość. Staranny projektant baz danych powinien z niej korzystać tak często, jak tylko to możliwe. Porządny opis umożliwia stwierdzenie, jaką czynność dana kwerenda miała wykonać. Również inni programiści docenią wagę opisu, gdy przyjdzie im pracować z tak utworzoną kwerendą. Okno Właściwości zawiera także datę utworzenia kwerendy oraz datę ostatniej modyfikacji, automatycznie aktualizowaną przez Access. Zgodnie z domyślnym ustawieniem właścicielem kwerendy jest osoba, która ją utworzyła. Ma to znaczenie dla za-bezpieczeń bazy. Na dole okna znajdują się dwa pola wyboru. Zaznaczenie pola Ukryty spowoduje, iż obiekt ten nie będzie widoczny dla użytkowników w głównym oknie bazy danych. Dzięki temu będzie można zapobiec przypadkowym zmianom tego obiektu.
Innym sposobem na ukrycie kwerendy przed uczestnikami jest umie¬szcze¬nie na początku jej nazwy liter USYS (np. USYS_myquery). Aby prze¬glądać takie obiekty, należy w menu Narzędzia otworzyć okno dia¬lo¬gowe Opcje i zaznaczyć pole wyboru Obiekty systemowe.
Microsoft również ukrywa różne obiekty, używając do tego celu przed¬rost¬ka MSYS. Nie można dopuścić, by użytkownicy uzyskali dostęp do tych tabel systemowych, bo ich zmiana może doprowadzić do usz¬ko¬dzenia bazy. Również programiści powinni unikać używania ich, gdyż Microsoft nie gwarantuje umieszczenia ich w kolejnej wersji.
ROZDZIAŁ II
Kwerendy podsumowujące
Zazwyczaj zestaw rezultatów kwerendy zawiera tak wiele informacji, że jest dla użyt¬kow¬nika bezużyteczny. Aby to zmienić, Access umożliwia tworzenie podsumowania da¬nych za pomocą kwerend podsumowujących.
Aby utworzyć podsumowanie klientów i ich zakupów, można w dołączonej do Ac¬ces¬sa 2000 bazie Northwind utworzyć nową kwerendę. Kwerenda będzie wy¬świetlać listę klientów
i zakupionych przez nich produktów jako listę unikatowych kom¬binacji.
Aby rozpocząć pracę z tą kwerendą, należy umieścić w panelu tabel następujące tabele: Klienci, Zamówienia, Opisy zamówień i Produkty.
Z tabeli Klienci przenieść do siatki kwerendy pole NazwaFirmy, a z tabeli Produkty – pole NazwaProduktu. Posortować kwerendę według NazwaFirmy i NazwaProduktu. Uruchomić kwe¬rendę, klikając znajdujący się na pasku narzędzi symbol czerwonego wykrzyknika lub wybierając KwerendaÞUruchom. Na rysunku 2. można zauważyć , że niektórzy z klientów występują w zestawie rezultatów więcej niż raz.
Rysunek 2. Niektórzy klienci pojawiają się kilkakrotnie dla tego samego produktu, gdyż zamówili go więcej niż raz
W widoku Projekt kwerendy kliknąć znajdujący się na pasku narzędzi przycisk Sumy lub wybrać tę opcję z menu Widok. Wiersz podsumowania pojawi się w siatce kwe¬rendy z wybraną dla obu pól opcją Grupuj według. Oznacza to, że kwerenda zmniej¬szy rozmiar zestawu rekordów tak, by wyświetlał tylko jeden raz każdą kombinację war¬tości NazwaFirmy
i NazwaProduktu. Po ponownym uruchomieniu kwerendy, można zobaczyć, że naz¬wy firm
i produktów się nie powtarzają. Spowodowało to usunięcie z zestawu setek re¬kordów.
Można korzystać z opcji Grupuj według nawet dla dziesięciu pól, pamiętająć jednak, że uży¬cie tej opcji dla każdego zbędnego pola spowalnia pracę kwerendy i powoduje wy¬świe¬tlenie dodatkowych wierszy w zestawie rezultatów.
Opcja Sumy jest rozbudowana i posiada wiele innych możliwości. Wprowadzając do kwe¬rendy wiersz Podsumowanie, masz do dyspozycji dwanaście opcji. Dziewięć z nich to funkcje agregujące, co oznacza, że wykonują one na danych różne obliczenia.
Funkcja agregująca Policz
Aby zobaczyć, ile zamówień złożył każdy z klientów, należy zmodyfikować kwerendę. Za¬miast pola NazwaProduktu wprowadzić z tabeli Zamówienia pole IDzamówienia i w wier¬szu Podsumowanie wybierać wartość Policz. Usunąć z panelu tabele Opisy za¬mó¬wień
i Produkty.
Po uruchomieniu kwerendy agregującej ważne jest, by nie znajdowało się w niej nic zbędnego. Prawie wszystko co znajduje się w siatce QBE może mieć wpływ na dzia¬łanie funkcji agregującej. Jeśli zbędne tabele pozostaną ,spowodują one, że kwerenda będzie zliczać wszystkie za¬mó¬wio¬ne produkty, a nie zamówienia (rysunek 3.).
Rysunek 3. Zliczanie zamówień klientów
Funkcje agregujące Minimum i Maksimum
Funkcje Min/Maks mogą być przydatne, aby uzyskać największą wartość w da¬nym polu. Na przykład w sytuacji, gdy chcemy zobaczyć datę ostatniego zamówienia każ¬dego z klientów. Aby tego dokonać, trzeba utworzyć kwerendę z tabelami Klienci i Za¬mó¬wie¬nia, połączonymi za pomocą pola IDklienta. Umieścić w siatce kwerendy pola NazwaFir¬my i DataZamówienia. Dodać wiersz Podsumowanie i ustawić opcję podsumowania pola DataZamówienia na Maksimum. Uzyskamy w ten sposób datę ostatniego zamówienia każ¬dego z klientów. Ustawienie tej opcji na Minimum spowoduje wyświetlenie naj¬wcze¬ś¬niejszego, zarejestrowanego zamówienia każdego z klientów. Można nawet wy¬świe¬tlać je obok siebie, dodając ponownie pole DataZamówienia i wybierając Maksimum dla jednego i Minimum dla drugiego z nich.
Funkcje agregujące Pierwszy i Ostatni
Na pierwszy rzut oka, funkcje Pierwszy i Ostatni mogą przypominać Minimum i Mak¬si¬mum,
w rzeczywistości jednak bardzo się od siebie różnią. Minimum i Maksimum oce¬nia¬ją przedstawiane dane. Pierwszy i Ostatni pobierają pierwszą lub ostatnią war¬tość napotkaną przez kwerendę, co czasami daje zaskakujące rezultaty. Użycie tych opcji na nieposortowanej lub posortowanej w nie¬właś¬ci¬wy sposób tabeli da subiektywne wyniki. Stanie się tak dlatego, że dla funkcji Pierwszy i Os¬tatni najważniejsza jest kolejność przedstawienia pól, a powiązania danych z innymi polami są nieważne.
Funkcji tych użyjemy, aby uzyskać listę najdroższych produktów w każdej kategorii. Lis¬ta ta będzie zawierać pola NazwaKategorii, NazwaProduktu i CenaJednostkowa. Trzeba
ut¬worzyć nową kwerendę, opartą na tabelach Kategorie i Produkty. W siatce QBE umieścić po¬le NazwaKategorii z tabeli Kategorie oraz pola NazwaProduktu i CenaJednostkowa z ta¬beli Produkty. Ustawić kolejność sortowania pola NazwaKategorii na Rosnąco
i Ce¬naJednostkowa na Malejąco.
Z menu Kwerenda wybierać Kwerenda tworząca tabele i nazwać nową tabelę Kategorie i Pro¬dukty Sortowane według Ceny. Uruchomić kwerendę. W nowej tabeli powinno zna¬leźć się ponad 70 nowych rekordów. Teraz można utworzyć kwerendę korzystającą z funk¬cji Pierwszy
i Ostatni.
Tworzymy nową kwerendę i wyświetlamy w panelu tabelę Kategorie i Produkty sortowane we¬dług ceny. Wszystkie jej pola umieszczamy w siatce kwerendy. Dodajemy wiersz Podsumowanie i us¬tawiamy funkcję agregującą w polu NazwaProduktu na Pierwszy,
a następnie w polu CenaJednostkowa również na Pierwszy. Po uruchomieniu kwerendy, zobaczymy najdroższy pro¬dukt w każdej kategorii. Jeśli funkcje Pierwszy zamienimy na Ostatni, zobaczymy naj¬tań¬szy produkt w każdej kategorii. Aby funkcje Pierwszy i Ostatni działały efektywnie, sor¬towanie rekordów musi być wykonane świadomie.
Funkcje agregujące Odchylenie standardowe i Wariancja
Odchylenie standardowe (OdchStd) i Wariancja dokonują obliczeń na wartościach da¬ne¬go pola. Wystarczy utworzyć kwerendę grupującą dane w dowolny sposób. Zaznaczyć od¬po¬wied¬nie pole i ustawić opcję wiersza Podsumowanie na OdchStd lub Wariancja.
Odchy¬le¬nie standardowe i Wariancja wykorzystują metodę próbkowania opartą na mianowniku (n – 1), gdzie n równe jest liczbie rekordów w zestawie rezultatów. Metoda ta używana jest w analizie statystycznej.
Funkcje agregujące Średnia
Aby zobaczyć średnią cenę danej kategorii produktów, trzeba utworzyć kwerendę opartą jedynie na tabeli Produkty. W siatce kwerendy umieszczamy pola IDkategorii i CenaJednostkowa,
do¬dajemy wiersz Podsumowanie i ustawiamy opcję podsumowania w polu CenaJednostkowa na Śred¬nia (rysunek 4.).
Rysunek 4. Siatka QBE kwerendy obliczającej średnią
Funkcja Wyrażenie
Wyrażenie umożliwia przedstawienie obliczeń w rezultatach kwerendy, jednakże obli¬cze¬nia te muszą zawierać funkcję agregującą. Używamy tej opcji, gdy chcemy w kwe¬ren¬dzie przedstawić wynik wielu funkcji. Przykładowe wyrażenie znajduje się na rysunku 5.
Rysunek 5. Zestaw rezultatów z wyrażeniem
W tym przykładzie zliczamy produkty, a następnie podnosimy średnią cenę każdej ka¬te¬go¬rii
o 5%. Aby tego dokonać, ustawiamy wartość wiersza Podsumowanie danego pola na Wyrażenie,
a następnie zapisujemy wzór używający funkcji agregującej. Jeśli w za¬pi¬sa¬nym wyrażeniu brak będzie funkcji agregującej, Access wyświetli komunikat o błę¬dzie, a kwerenda nie zostanie wykonana.
Warunek Gdzie
Zastosowanie kryteriów w kwerendzie agregującej odbywa się przez użycie polecenia Gdzie lub przez wprowadzenie kryteriów do siatki QBE, jak to ma miejsce w przy¬pad¬ku zwykłej kwerendy wybierającej. Istnieją jednak różnice między tymi sposobami i wy¬bór ich może mieć wpływ na zachowanie kwerendy. Podczas użycia polecenia Gdzie kwerenda najpierw stosuje kryteria, dołączając lub wyłączając rekordy, zanim zos¬taną one pogrupowane.
Jeśli wprowadzimy kryteria w innym polu bez użycia polecenia Gdzie, kryteria będą
sto¬sowane dopiero po zakończeniu grupowania.
Jeśli z kwerendy agregującej chcemy usunąć sprze¬daż zagraniczną, używamy polecenia Gdzie. Jeśli chcemy jednocześnie zobaczyć je¬dy¬nie re¬zultaty, których wynik zliczania jest wyższy niż 1000, wyrażamy to w polu wykonującym po¬lecenie Policz. Kryterium wykluczające sprzedaż zagraniczną powinno być wpisane w siatce kwerendy dla pola KrajOdbiorcy i wyrażone w SQL za pomocą wyrażenia Having. Polecenie Having przydaje się przy stosowaniu kryteriów na dokonanych ob¬li¬czeniach. Przykłady użycia tego polecenia znajdują się na rysunku 6.
Rysunek 6. Kwerenda używająca pleceń Where i Having w siatce BE
Kwerendy przekazujące
Access posiada umiejętność łączenia tabel z innych źródeł danych i tworzenia opartych na nich kwerend tak, jakby były utworzone w Accessie. Oprócz tego, Access może rów¬nież przesyłać kwerendy do innych baz danych. W takiej sytuacji, obca baza sama prze¬twa¬rza swoje dane
i zwraca jedynie zestaw rezultatów.
Głównymi przyczynami, dla których warto używać kwerend przekazujących, są: wy¬daj¬ność
i przepustowość sieci. Kwerenda przekazująca jest jedną z kwerend charakterystycznych dla języka SQL, co oz¬nacza, że trzeba ją napisać w SQL.
Kwerendy definiujące dane
Poza umiejętnością manipulowania danym zawartymi w tabelach kwerendy Accessa mo¬gą również tworzyć, modyfikować i usuwać tabele w Accessie w taki sposób, by na¬dać ap¬likacji umiejętność inteligentnej pracy z danymi.
Kwerendy definiujące dane dokonują ciągłych modyfikacji w liczbie, struk¬turze
i właściwościach tabel, indeksów i relacji. Zanim zaczniemy z nimi pracować, należy sporządzić kopię zapasową swojej bazy i uruchomić je w kopii swojego projektu.
Kwerendy definiujące dane posiadają podstawowy schemat, którego zrozumienie znacz¬¬nie ułatwia korzystanie z nich. Zaczynają się słowem kluczowym Create Table, Alter Table lub Drop Table, po którym występuje nazwa tabeli. Kolejnym ele¬mentem jest umieszczona
w nawiasie sekcja definiująca pole. Po niej występuje naz¬wa pola, jego typ danych, a następnie rozmiar pola (w nawiasie). Sekcja definiująca da¬ne mo¬że być dowolnej długości. Po każdej definicji pola może występować warunek ograni¬cza¬ją¬cy, ustawiający indeksy, klucze i klucze: obcy lub nadający polu wartość NOT NULL. Wa¬runek ograniczający jest opcjonalny.
Użycie kwerend krzyżowych
Kwerendy krzyżowe i agregujące są do siebie bardzo zbliżone. Kwerenda krzyżowa po¬sia¬da wiele zdolności analitycznych. W typowej kwerendzie wybierającej pola wy¬świe¬tla¬ne są u góry zestawu rezultatów, a lista wartości poniżej, jako pojedyncze rekordy. W kwe¬rendzie agregującej pola również znajdują się u góry zestawu rezultatów, jed¬nak¬że po¬niżej wyświetlane są wartości będące podsumowanymi danymi. Kwerenda krzyżowa umo¬żliwia tak przekształcić podsumowania uzyskane dzięki kwerendzie agregującej, że wartości będące rezultatami jednej
z grup rozmieszczone są u góry zestawu rezul¬ta¬tów. Utworzona przez lewą kolumnę i górny wiersz zestawu rezultatów macierz wypeł¬nia¬na jest żądanymi przez użytkownika obliczeniami.
Prosta kwerenda krzyżowa składa się przynajmniej z trzech elementów: Nagłówka wier¬sza, Nagłówka kolumny i Wartości. Kwerenda ta może korzystać z zestawu rezultatów do¬wolnej kwerendy wybierającej.Aby utworzyć kwerendę krzyżową, w wyniku której podzielimy klientów według ka¬te¬go¬rii zamawianych produktów, trzeba utworzyć następujące tabele: Klienci, Ka¬te¬gorie, Produkty, Zamówienia i Opisy zamówień. Z tabeli Klienci wybieramy pole Naz¬waFirmy, z tabeli Kategorie pole NazwaKategorii, a z tabeli Opisy zamówień pole Ilość.
Aby kwerendę wybierającą przekształcić w krzyżową, wybieramy z menu Kwerenda opcję Kwe¬renda krzyżowa. Po przekształceniu w siatce kwerendy pojawi się nowy wiersz.Wiersz ten umożliwi przyporządkowanie pola do od¬po¬wied¬nich elementów kwerendy krzyżowej. Pole NazwaFirmy ustawiamy jako Nagłówek wier¬sza, pole NazwaKategorii jako Nagłówek kolumny (ta część będzie wyświetlana u gó¬ry zestawu rezultatów), pole Ilość jako Wartość,
a w wierszu Podsumowanie tego po¬la wybieramy Suma. Po uruchomieniu kwerendy Access automatycznie utworzy sumę na każdym przecięciu klienta i produktu.
Użycie kwerend parametrycznych
Kwerendy parametryczne nie są osobnym typem kwerend. Wymagają one po prostu in¬ter¬wencji użytkownika. Wszystkie kwerendy przed ich uruchomieniem mogą żądać po¬da¬nia określonych informacji. Jeśli podczas tworzenia kwerendy wprowadzimy niepoprawną nazwę pola lub wartość
i przy uruchamianiu zostaniemy poproszeni o zidentyfikowanie tych informacji to właśnie stworzyliśmy kwerendę parametryczną. Dobrym przykładem będzie kwerenda opar¬ta na tabeli Klienci i zawierająca pola NazwaFirmy, Przedstawiciel i Telefon. Kwe¬ren¬da ta przedstawiona jest na rysunku 7.
Rysunek 7. Podstawa dla prostej kwerendy parametrycznej
Gdy uruchomimy tę kwerendę, otrzymamy listę wszystkich klientów, ich przedstawicieli oraz wszystkich numerów telefonów.
Bibliografia:
Access 2000 - Księga eksperta, Rozdział 4 [www.osiolek.com]
Spis ilustracji:
Rysunek 1 ……………………………………………………………………. 4
Rysunek 2 ……………………………………………………………………. 6
Rysunek 3 ……………………………………………………………………. 8
Rysunek 4 ……………………………………………………..……………. 10
Rysunek 5 ……………………………………………………..……………. 11
Rysunek 6 ……………………………………………………..……………. 12
Rysunek 7 ……………………………………………………..……………. 15
