Typy kwerend programu Access

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 umieszczenie na początku jej nazwy liter USYS (np. USYS_myquery). Aby przeglądać takie obiekty, należy w menu Narzędzia otworzyć okno dialogowe Opcje i zaznaczyć pole wyboru Obiekty systemowe.

Microsoft również ukrywa różne obiekty, używając do tego celu przedrostka MSYS. Nie można dopuścić, by użytkownicy uzyskali dostęp do tych tabel systemowych, bo ich zmiana może doprowadzić do uszkodzenia 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żytkownika bezużyteczny. Aby to zmienić, Access umożliwia tworzenie podsumowania danych za pomocą kwerend podsumowujących.

Aby utworzyć podsumowanie klientów i ich zakupów, można w dołączonej do Accessa 2000 bazie Northwind utworzyć nową kwerendę. Kwerenda będzie wyświetlać listę klientów
i zakupionych przez nich produktów jako listę unikatowych kombinacji.
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ć kwerendę, 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 kwerendy z wybraną dla obu pól opcją Grupuj według. Oznacza to, że kwerenda zmniejszy rozmiar zestawu rekordów tak, by wyświetlał tylko jeden raz każdą kombinację wartości NazwaFirmy
i NazwaProduktu. Po ponownym uruchomieniu kwerendy, można zobaczyć, że nazwy firm
i produktów się nie powtarzają. Spowodowało to usunięcie z zestawu setek rekordów.
Można korzystać z opcji Grupuj według nawet dla dziesięciu pól, pamiętająć jednak, że użycie tej opcji dla każdego zbędnego pola spowalnia pracę kwerendy i powoduje wyświetlenie dodatkowych wierszy w zestawie rezultatów.
Opcja Sumy jest rozbudowana i posiada wiele innych możliwości. Wprowadzając do kwerendy 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ę. Zamiast pola NazwaProduktu wprowadzić z tabeli Zamówienia pole IDzamówienia i w wierszu Podsumowanie wybierać wartość Policz. Usunąć z panelu tabele Opisy zamó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 zamówione 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 danym 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 Zamówienia, połączonymi za pomocą pola IDklienta. Umieścić w siatce kwerendy pola NazwaFirmy 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 najwcześniejszego, zarejestrowanego zamówienia każdego z klientów. Można nawet wyświetlać 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 Maksimum,
w rzeczywistości jednak bardzo się od siebie różnią. Minimum i Maksimum oceniają przedstawiane dane. Pierwszy i Ostatni pobierają pierwszą lub ostatnią wartość napotkaną przez kwerendę, co czasami daje zaskakujące rezultaty. Użycie tych opcji na nieposortowanej lub posortowanej w niewłaściwy sposób tabeli da subiektywne wyniki. Stanie się tak dlatego, że dla funkcji Pierwszy i Ostatni 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. Lista ta będzie zawierać pola NazwaKategorii, NazwaProduktu i CenaJednostkowa. Trzeba
utworzyć nową kwerendę, opartą na tabelach Kategorie i Produkty. W siatce QBE umieścić pole NazwaKategorii z tabeli Kategorie oraz pola NazwaProduktu i CenaJednostkowa z tabeli Produkty. Ustawić kolejność sortowania pola NazwaKategorii na Rosnąco
i CenaJednostkowa na Malejąco.
Z menu Kwerenda wybierać Kwerenda tworząca tabele i nazwać nową tabelę Kategorie i Produkty Sortowane według Ceny. Uruchomić kwerendę. W nowej tabeli powinno znaleźć się ponad 70 nowych rekordów. Teraz można utworzyć kwerendę korzystającą z funkcji Pierwszy
i Ostatni.
Tworzymy nową kwerendę i wyświetlamy w panelu tabelę Kategorie i Produkty sortowane według ceny. Wszystkie jej pola umieszczamy w siatce kwerendy. Dodajemy wiersz Podsumowanie i ustawiamy funkcję agregującą w polu NazwaProduktu na Pierwszy,
a następnie w polu CenaJednostkowa również na Pierwszy. Po uruchomieniu kwerendy, zobaczymy najdroższy produkt w każdej kategorii. Jeśli funkcje Pierwszy zamienimy na Ostatni, zobaczymy najtańszy produkt w każdej kategorii. Aby funkcje Pierwszy i Ostatni działały efektywnie, sortowanie rekordów musi być wykonane świadomie.

Funkcje agregujące Odchylenie standardowe i Wariancja

Odchylenie standardowe (OdchStd) i Wariancja dokonują obliczeń na wartościach danego pola. Wystarczy utworzyć kwerendę grupującą dane w dowolny sposób. Zaznaczyć odpowiednie pole i ustawić opcję wiersza Podsumowanie na OdchStd lub Wariancja.

Odchylenie 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,
dodajemy wiersz Podsumowanie i ustawiamy opcję podsumowania w polu CenaJednostkowa na Średnia (rysunek 4.).

Rysunek 4. Siatka QBE kwerendy obliczającej średnią

Funkcja Wyrażenie

Wyrażenie umożliwia przedstawienie obliczeń w rezultatach kwerendy, jednakże obliczenia te muszą zawierać funkcję agregującą. Używamy tej opcji, gdy chcemy w kwerendzie 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 kategorii
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 zapisanym 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 przypadku zwykłej kwerendy wybierającej. Istnieją jednak różnice między tymi sposobami i wybó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 zostaną one pogrupowane.
Jeśli wprowadzimy kryteria w innym polu bez użycia polecenia Gdzie, kryteria będą
stosowane dopiero po zakończeniu grupowania.
Jeśli z kwerendy agregującej chcemy usunąć sprzedaż zagraniczną, używamy polecenia Gdzie. Jeśli chcemy jednocześnie zobaczyć jedynie rezultaty, których wynik zliczania jest wyższy niż 1000, wyrażamy to w polu wykonującym polecenie 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 obliczeniach. 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ównież przesyłać kwerendy do innych baz danych. W takiej sytuacji, obca baza sama przetwarza swoje dane
i zwraca jedynie zestaw rezultatów.
Głównymi przyczynami, dla których warto używać kwerend przekazujących, są: wydajność
i przepustowość sieci. Kwerenda przekazująca jest jedną z kwerend charakterystycznych dla języka SQL, co oznacza, że trzeba ją napisać w SQL.

Kwerendy definiujące dane
Poza umiejętnością manipulowania danym zawartymi w tabelach kwerendy Accessa mogą również tworzyć, modyfikować i usuwać tabele w Accessie w taki sposób, by nadać aplikacji umiejętność inteligentnej pracy z danymi.
Kwerendy definiujące dane dokonują ciągłych modyfikacji w liczbie, strukturze
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 znacznie 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 elementem jest umieszczona
w nawiasie sekcja definiująca pole. Po niej występuje nazwa pola, jego typ danych, a następnie rozmiar pola (w nawiasie). Sekcja definiująca dane może być dowolnej długości. Po każdej definicji pola może występować warunek ograniczający, ustawiający indeksy, klucze i klucze: obcy lub nadający polu wartość NOT NULL. Warunek ograniczający jest opcjonalny.

Użycie kwerend krzyżowych
Kwerendy krzyżowe i agregujące są do siebie bardzo zbliżone. Kwerenda krzyżowa posiada wiele zdolności analitycznych. W typowej kwerendzie wybierającej pola wyświetlane są u góry zestawu rezultatów, a lista wartości poniżej, jako pojedyncze rekordy. W kwerendzie agregującej pola również znajdują się u góry zestawu rezultatów, jednakże poniż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 rezultatów. Utworzona przez lewą kolumnę i górny wiersz zestawu rezultatów macierz wypełniana jest żądanymi przez użytkownika obliczeniami.
Prosta kwerenda krzyżowa składa się przynajmniej z trzech elementów: Nagłówka wiersza, Nagłówka kolumny i Wartości. Kwerenda ta może korzystać z zestawu rezultatów dowolnej kwerendy wybierającej.Aby utworzyć kwerendę krzyżową, w wyniku której podzielimy klientów według kategorii zamawianych produktów, trzeba utworzyć następujące tabele: Klienci, Kategorie, Produkty, Zamówienia i Opisy zamówień. Z tabeli Klienci wybieramy pole NazwaFirmy, 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ę Kwerenda krzyżowa. Po przekształceniu w siatce kwerendy pojawi się nowy wiersz.Wiersz ten umożliwi przyporządkowanie pola do odpowiednich elementów kwerendy krzyżowej. Pole NazwaFirmy ustawiamy jako Nagłówek wiersza, 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 pola 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 interwencji użytkownika. Wszystkie kwerendy przed ich uruchomieniem mogą żądać podania 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 oparta na tabeli Klienci i zawierająca pola NazwaFirmy, Przedstawiciel i Telefon. Kwerenda 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.

Spis ilustracji:
Rysunek 1 ……………………………………………………………………. 4
Rysunek 2 ……………………………………………………………………. 6
Rysunek 3 ……………………………………………………………………. 8
Rysunek 4 ……………………………………………………..……………. 10
Rysunek 5 ……………………………………………………..……………. 11
Rysunek 6 ……………………………………………………..……………. 12
Rysunek 7 ……………………………………………………..……………. 15

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