Projektowanie kwerend w MS ACCESS 2007
Katedra Zastosowań Informatyki
Projektowanie kwerend
Kwerendy (ang.query) wykorzystuje się do efektywnego wyszukiwania informacji. W MS Access
istnieją dwa języki definiowania kwerend, język SQL (ang. Structured Query Language) oraz język QBE (ang.
Query By Example). Kwerenda jest narzędziem, która zbiera dane z różnych tabel, aby odpowiedzieć na
pytanie zadane przez użytkownika. Ze względu na przeznaczenie kwerendy dzieli się na wybierające ,
krzyżowe i funkcjonalne.
Tworzenie kwerendy
Kwerendy wybierające umożliwiają wyświetlanie wybranych rekordów tabeli, tworzenie nowych pól
obliczeniowych, podsumowywanie danych, grupowanie danych, wyznaczanie pól do ukrycia lub pokazania.
Kwerendy tego typu są podobne do filtrów, jednak umożliwiają również przeprowadzanie zapytań w więcej
niż jednej tabeli. Kwerendy wybierające w MS Access tworzy się z wykorzystaniem Kreatora kweren d
lub w oknie Widok projektu.
Tworzenie kwerendy w Widoku projektu kwerend:
1. W karcie Tworzenie wybierz opcję Projekt kwerendy.
2. Pojawi się poniższy rysunek, w którym należy wybrać tabele, których pola będą tworzyć kwerendę.
2/8
Tworzenie kwerendy wybierającej za pomocą Kreatora kwerend
Tworzenie kwerendy z wykorzystaniem Kreator kwerend pozwala skorzystać z narzędzia, które ułatwia
proces tworzenia kwerendy. W tym kroku należy postępować zgodnie ze wskazówkami podanymi w
kolejnych oknach dialogowych.
Etapy tworzenia:
1. Utwórz kwerendę za pomocą opcji Kreator kwerend i postępuj zgodnie z zaleceniami kreatora:
a) W pierwszym oknie wybierz rodzaj tworzonej kwerendy (np. kreator prostych kwerend).
b) W kolejnym oknie kreatora określ z
ródło danych(tabela, kwerenda) skąd zostaną wzięte wartości do
zapytania (strzałka czerwona), następnie określ jakie pola maja być umieszczone w kwerendzie
(strzałka zielona i niebieska). W zapytaniu można korzystać z pól z kilku tabel lub kwerend.
Tworzenie kwerendy z wykorzystaniem Kreatora kwerend  okno I kreatora
3/8
c) W trzecim oknie kreatora określ nazwę kwerendy i wciśnij przycisk Zakończ.
Tworzenie kwerendy wybierającej z wykorzystaniem Widoku projekt u
1. Utwórz kwerendę za pomocą okna Widoku projektu.
2. W oknie Pokazywanie tabeli zaznacz wszystkie obiekty wykorzystywane w kwerendzie (potrzebne tabele) i
dodaj do siatki kwerendy wybierając przycisk Dodaj. Należy dodać również wszystkie tabele, które są
pomiędzy tabelami z których pobieramy pola. Aby zaznaczyć więcej niż jeden obiekt wciśnij klawisz CTRL i
zaznacz obiekty. (okno dialogowe pokazywanie tabel można uaktywnić Widoku Projektu za pomocą
polecenia na pasku narzędzi Projektowanie).
4/8
3. Dodać wszystkie pola do siatki kwerendy i określić dla nich kryteria. Przykładowe kryteria wykorzystywane w
zapytaniach pokazano w poniższej tabeli. Aby dodać pole do siatki kwerendy należy dwukrotnie kliknąć
na nazwę w tabeli, lub przeciągnąć nazwę pola do siatki kwerendy. Przykład kwerendy wyświetlającej
tytuły książek poszczególnych aktorów pokazano na rysunku poniżej.
Rys. 7. Przykład kwerendy wyświetlającej tytuły książek poszczególnych autorów
Przykładowe kryteria wykorzystywane w kwerendach
Wyrażenia W yn i k
Nowak
Wszystkie rekordy z nazwiskiem Nowak. Nie ma znaczenia wielkość liter.
Like N* Wszystkie rekordy, których wartość pola zaczyna się na literę N. Znak * jest symbolem
wieloznacznym i zastępuje dowolną liczbę znaków.
Wszystkie rekordy, których pole nazwisko ma wartość Kowalski lub Kowalska. Znak ? jest
Like Kowalsk?
symbolem wieloznacznym i zastępuje dowolny pojedynczy znak.
Wszystkie rekordy, które nie zaczynają się na literę N. Operator NOT używa się, aby znalez
ć
NOT N*
rekordy niepasujące do zawartości.
Like N* AND
Wszystkie rekordy zaczynające sie na literę N i Z. Operator logiczny AND służy do łączeni a
like Z*
kryteriów dla jednego pola.
Wszystkie rekordy, które mają wartość USA lub Canada. Operator logiczny OR służy do łączenia
USA OR Canada
kryteriów dla jednego pola.
Is not Null
Wszystkie rekordy, które mają wprowadzoną wartość.
Is Null Wszystkie rekordy, które nie mają wprowadzonej wartości.
Date() Wszystkie rekordy, dla których pole data ma wartość równą bieżąca data.
>=Date()-5 Wszystkie rekordy, dla których data zawiera się w ciągu ostatnich pięciu dni.
DateAdd
Wszystkie rekordy, dla których data zawiera się w ciągu ostatnich trzech miesięcy.
( m ,-3, Date())
Wszystkie rekordy z przedziału od 1 do 10 i jest równoważne wyrażeniu: >=1 and <=10.
Between 1 and 10
Wyrażenie Between...And... można stosować zarówno dla danych tekstowych, liczbowych i dat.
Zapytanie z parametrem. Wyświetlone zostanie okno dialogowe z tekstem żądania Podaj nazwisko.
[Podaj nazwisko]
Po wprowadzeniu wyświetlone zostaną wszystkie rekordy o podanym nazwisku. Tekst żądania
musi być inny niż nazwy pól.
[podaj pierwsza Zapytanie z parametrem. Wyświetlone zostanie okno dialogowe z tekstem żądania Podaj pierwszą
literę imienia nazwisko. Po wprowadzeniu wyświetlone zostaną wszystkie rekordy, których imię
literę imienia]&*
zaczyna się na podaną literę.
Funkcje agregujące dostępne w MS Access
Nazwa Wynik funkcji
funkcji
Suma Oblicza sumę wartości w polu.
Zlicz Podaje liczbę wartości w polu. Wartości puste, takie jak napisy o dłu-gości zero znaków są liczone;
wartości oznaczające brakujące lub nieznane dane nie są liczone.
Min Daje w wyniku najmniejszą wartość z podsumowywanego pola.
Max Daje w wyniku największą wartość z podsumowywanego pola.
5/8
Średnia Oblicza średnią wartości w polu.
OdchStd Oblicza odchylenie standardowe w polu.
Wariancja Oblicza wariancję wartości w polu.
Pierwszy Zwraca wartość pola z pierwszego rekordu tabeli i lub zapytania.
Ostatni Zwraca wartość z ostatniego rekordu tabeli i lub zapytania.
Kwerendy Funkcjonalne
Kwerendy funkcjonalne umożliwiają dokonanie zmian w wielu rekordach. Zmiany te są nieodwracalne ,
dlatego kwerendy te należy stosować ostrożnie. Do kwerend funkcjonalnych zalicza się kwerendy :
usuwającą, aktualizującą, dołączającą i tworzącą tabele. Tworzenie kwerend funkcjonalnych poleg a
na utworzeniu kwerendy w Widoku projektu i wybraniu odpowiedniego typu z Karty Projektowanie.
Kwerendy tworzące tabele
Kwerenda tworząca tabelę buduje nową tabelę z wszystkich lub części danych znajdujących się w
jednej lub kilku tabelach. Kwerendy tworzące tabelę wykorzystywane są w sytuacjach, tj. podczas tworzenia
kopii zapisowej tabeli lub tworzenia tabeli archiwalnej zawierającej nieaktualne rekordy. Tworzenie
zapytania tworzącego nową tabelę polega na zaznaczeniu wszystkich pól, które mają być włączone do nowej
tabeli. Następnie należy określić nazwę i lokalizację tabeli (bieżąca czy inna baza danych). Poniżej na rysunk u
pokazano przykładową kwerendę tworzącą nową tabelę, zawierającą informacje o klientach z Warszawy. Ab y
uruchomić proces tworzenia kwerendy należy wybrać opcję wskazaną czerwoną strzałką.
Rys. 6. Kwerenda tworząca tabelę Klientzwarszawy zawierającą informacje o klientach z miasta
Warszawa
Kwerendy usuwające
Kwerenda usuwająca
eliminuje grupę rekordów z jednej/kilku tabel. Tworzenie zapytania
usuwającego rekordy polega na wybraniu w Widoku projektu z karty Projektowanie opcję wskazaną zieloną
strzałką. W kolejnym kroku należy przeciągnąć pole gwiazdki tabeli skąd będą usuwane rekordy (w wierszu
usuwa-nie powinno pojawić się słowo Skąd) i dodać pola, dla których należy określić kryteria usuwania (w
wierszu usuwanie powinno pojawić się słowo Gdzie). Ponadto należy pamiętać, że zapytanie to usunie całe
rekordy, a nie wybrane pola rekordów. W większości przypadków można usunąć rekordy tylko z jednej tabeli .
Jeżeli zapytanie usuwające zawiera dwie tabele połączone relacją typu jeden do wielu, to zapytanie usunie
rekordy z tabeli po stronie wiele. Jeżeli podczas tworzenia relacji między tabelami zaznaczona została opcja
kaskadowe usuwanie rekordów, wówczas jeżeli usuwamy rekordy z tabeli po stronie jeden, to automatycznie
6/8
usunięte zostaną rekordy po stronie wiele. Poniżej przedstawiono kwerendę usuwającą osoby z Warszawy z
tabeli Klient.
Kwerendy dołączające
Kwerenda dołączająca dodaje grupę rekordów z jednej lub kilku tabel na końcu innej tabeli.
Kwerendę dołączającą tworzy się wybierając w oknie Widok projektu z karty Projektowanie opcję wskazaną
zieloną strzałką. Wówczas w oknie dialogowym Dołączanie należy wskazać z listy nazwę tabeli do której
dołączamy i w siatce kwerendy dodać wszystkie pola, które mają być dołączone i ustalić kryteriu m
dołączania. Jeżeli dołączamy wszystkie pola, można zastosować symbol gwiazdki. Przykład kwerendy dołączającej
rekordy tabeli Książka do tabeli Klient przedstawiono poniższy rysunek.
7/8
Kwerendy aktualizujące
Kwerenda aktualizująca dokonuje globalnych zmian w grupie rekordów w tabeli. Za pomocą kwerend
aktualizujących można zmodyfikować dane w tabelach. Na przykład można podwyższyć o 10% cen ę
wszystkich produktów lub dać pięcioprocentową podwyżkę wszystkim pracownikom. Przykład kwerendy,
która podwyższa cenę Książki o 15% pokazano na poniższym rysunku.
Kwerendy krzyżowe
Kwerenda krzyżowa wyświetla zliczone wartości z pola i porządkuje wartości w wiersze i kolumny.
A
ączy analizy zliczania oraz sumowania.
8/8