Laboratorium Inżynierskich Baz Danych

1

Projektowanie kwerend w programie Microsoft Access

Materiały pomocnicze dla studentów specjalności Komputerowe

Wspomaganie Projektowania na wydziale SiMR PW

Opracował: Witold Marowski

Warszawa, kwiecień 2002

1. Kwerendy wybierające

Przykład 1
Zbudować kwerendę wybierającą z tabeli TYPY bazy danych Komis (ta baza będzie też używana w następnych
przykładach) wszystkie modele Volkswagenów i porządkującą je alfabetycznie według nazwy modelu. Warunek
wyszukiwania należy określić dla niewidocznego pola MARKA. Widok siatki QBE dla tej kwerendy pokazano
poniżej.

Przykład 2
Uzupełnić źródło danych poprzedniej kwerendy o tabele SILNIK oraz SKRZ_B, tak aby w wyświetlanych przez nią
danych modeli Volkswagena mogły się znaleźć pola pojemności i mocy silnika, ilości biegów i sposobu
sterowania skrzynią biegów. Należy w tym celu otworzyć tę kwerendę w widoku Projekt, kliknięciem przycisku
Pokaż tabelę wyświetlić okno pozwalające na dodanie wyżej wymienionych tabel do jej źródła danych i
przeciągnąć do siatki QBE odpowiednie pola, tak aby otrzymać wiersz o schemacie przedstawionym poniżej.

Następnie należy zapamiętać zmodyfikowaną kwerendę pod nową nazwą, używając polecenia menu Plik |
Zapisz jako

.

Przykład 3 (kilka kryteriów wyszukiwania, lista wyjścia zawierająca wyrażenie)
Z tabel SAMOCHÓD, TYPY i SILNIK bazy Komis wybrać dane modeli Forda i Opla, dla których pojemność silnika
jest większa od 1500 cm

3

lub moc jest większa od 50 KM, a rok produkcji jest zawarty w przedziale <1985,1990>.

Dla każdego z samochodów zwracanych przez kwerendę należy też wyliczyć i wyświetlać w zbiorze wyników
moc jednostkową silnika (tj. moc z 1 litra pojemności skokowej). Uporządkować dane pojazdów alfabetycznie
według marki i modelu oraz malejąco według roku produkcji. Należy przy tym zwrócić uwagę na właściwe
wykorzystanie kolejnych wierszy kryteriów siatki QBE. Powtarzające się kryteria można kopiować z
wykorzystaniem schowka środowiska Windows. Ostateczny wygląd siatki QBE takiej kwerendy pokazano poniżej.
W celu ograniczenia liczby miejsc po przecinku w wartości mocy jednostkowej do dwóch należy otworzyć arkusz
właściwości tego pola kwerendy i nadać właściwości Format wartość Stałoprzecinkowy.

Laboratorium Inżynierskich Baz Danych

2

Przykład 4 (kwerenda zwracająca wartości unikatowe)
Wybrać z tabeli SAMOCHÓD wszystkie roczniki samochodów znajdujących się w ofercie sprzedaży i posortować
je rosnąco. Utworzyć wersję kwerendy zwracającą wszystkie rekordy oraz zwracającą tylko wartości unikatowe i
porównać ich zbiory wyników. Siatkę QBE kwerendy przedstawiono poniżej wraz z arkuszem właściwości dla
wersji zwracającej wartości unikatowe.

Przykład 5 (kwerenda parametryczna, pole wyliczane)
Na podstawie kwerendy Przegląd oferty z bazy danych Komis zbudować kwerendę, która zwraca listę
samochodów w określonym przez parametry przedziale cen i roczników. Dodać do wiersza kwerendy pole
wyliczane zawierające cenę z VAT-em i przedstawić je w formacie walutowym. Postać wiersza danych kwerendy
przedstawiono poniżej. Dla określenia formatu walutowego dla pola ceny brutto należy odpowiednio ustawić
wartość właściwości Format tego pola. Uwaga: separator dziesiętny w wyrażeniu należy podać zgodnie z
aktualnymi ustawieniami środowiska Windows (np. przez naciśnięcie kropki na klawiaturze numerycznej).

2. Kwerendy agregujące

Przykład 6 (parametryczna kwerenda agregująca)
Na podstawie tabel SAMOCHÓD i TYPY sporządzić kwerendę zliczającą liczbę samochodów poszczególnych
modeli znajdujących się w ofercie sprzedaży oraz zwracającą cenę minimalną, maksymalną i przeciętną każdego
modelu. Uporządkować wyniki alfabetycznie według marek i modeli. Określić przejrzyste nazwy nagłówków
kolumn zbioru wyników kwerendy. Następnie zmodyfikować projekt kwerendy przez wyłączenie z podsumowań
pojazdów marki wprowadzanej przy użyciu parametru oraz określenie warunku minimalnej liczby pojazdów
danego modelu (także wprowadzanej przy użyciu parametru), od której kwerenda będzie prezentować wyniki
podsumowań dla tej grupy. Należy w tym celu wprowadzić określane przez parametr kryterium w kolumnie
MODEL / Zlicz. Ostateczną postać projektu wiersza zmodyfikowanej kwerendy pokazano poniżej.

Laboratorium Inżynierskich Baz Danych

3

3. Zastosowanie złączenia zewnętrznego

Przykład 7
Zbudować kwerendę zwracającą nazwy elementów wyposażenia dodatkowego, które nie są zainstalowane w
żadnym z samochodów znajdujących się w ofercie sprzedaży. Sposób określenia złączeń tabel źródłowych oraz
postać siatki QBE tej kwerendy pokazano poniżej.

Po przetestowaniu działania kwerendy sprawdzić, jakim zmianom uległby zbiór jej wyników przy pozostawieniu w
jej projekcie pochodzącego ze struktury logicznej bazy danych złączenia wewnętrznego tabel. Sprawdzić też, jak
działałyby oba warianty kwerendy po usunięciu kryterium Is Null dla pola SAM_ID.

4. Kwerendy zagnieżdżone i kwerendy skorelowane

Przykład 8
Zbudować kwerendę zwracającą listę samochodów o cenach wyższych, niż przeciętna cena samochodu w
ofercie sprzedaży. Posortować zbiór wyników przeszukiwania rosnąco względem marki i modelu oraz malejąco
względem roku produkcji i ceny. Jako źródła danych przyjąć tabele SAMOCHÓD i TYPY z bazy danych Komis.
Widok siatki QBE dla takiej kwerendy pokazano poniżej.

Warto zwrócić uwagę, iż w przypadku, gdy wymagane jest porównanie z ceną średnią danego modelu, należy
zbudować tzw. kwerendę skorelowaną. W programie Access może ona być napisana jedynie w języku SQL. Przy
wykonywaniu takiej kwerendy dla każdego wiersza danych samochodu musi być wykonana podkwerenda
operująca na tej samej tabeli Samochód i obliczająca średnią cenę modelu samochodu z aktualnego wiersza
danych. Wynik porównania tej średniej ceny dla modelu i ceny aktualnego pojazdu decyduje o tym, czy wiersz
jego danych zostanie włączony do zbioru wyników kwerendy. Dla odróżnienia odwołań do tych samych pól tabeli
Samochód dokonywanych przez zasadniczą kwerendę i przez podkwerendę konieczne jest zatem określenie w
treści instrukcji SELECT tzw. aliasu, czyli zastępczej nazwy dla tabeli Samochód. Postać takiej kwerendy
pokazano poniżej, przy czym jako zastępczą nazwę tabeli Samochód przyjęto w niej X.
SELECT MARKA, MODEL, R_PROD, PRZEBIEG, CENA
FROM [Samochód] AS X, TYPY
WHERE X.TYP_ID = TYPY.TYP_ID
AND CENA > (SELECT AVG(CENA) FROM [Samochód] WHERE X.TYP_ID = TYP_ID)
ORDER BY MARKA, MODEL, R_PROD DESC, CENA DESC;

Przykład 9
Zmodyfikować kwerendę utworzoną w poprzednim przykładzie przez wprowadzenie przeciętnej ceny samochodu
w ofercie sprzedaży do listy wartości zwracanych w jej zbiorze wyników. Wymaga to uzupełnienia siatki QBE o
jedną kolumnę, tak jak pokazano poniżej.

Laboratorium Inżynierskich Baz Danych

4

5. Kwerendy stosujące autoprzegląd

Przykład 10
Na podstawie tabel SAMOCHÓD, TYPY i KOLORY zbudować kwerendę stosującą autoprzegląd. Wiersz
wyników powinien zawierać pola SAM_ID, MARKA, MODEL, SAMOCHÓD.TYP_ID, R_PROD, PRZEBIEG,
KOLOR_ID, KOLOR, CENA (kursywą zostały zaznaczone pola kluczy obcych w tabelach po stronie „wiele”
relacji). Zmieniać wartości kluczy obcych w wierszach zbioru wyników kwerendy i obserwować działanie
autoprzeglądu. Wprowadzić za pośrednictwem widoku Arkusz danych tej kwerendy nowy wiersz danych do tabeli
SAMOCHÓD. Obserwować działanie autoprzeglądu w czasie tej operacji. Postać siatki QBE dla tworzonej
kwerendy pokazano poniżej.

6. Kwerendy krzyżowe

Przykład 11 (tworzenie przy użyciu kreatora)
Na podstawie kwerendy utworzonej w przykładzie 10 zbudować przy użyciu kreatora kwerendę krzyżową
podającą liczby modeli samochodów różnych kolorów znajdujących się w ofercie sprzedaży. Utworzyć też
kolumnę podsumowań podającą ogólne liczby samochodów poszczególnych modeli. Nagłówkami wierszy dla
tworzonej kwerendy powinny być kolumny MARKA i MODEL oraz kolumna podsumowań, zaś nagłówkiem
kolumny – kolumna KOLOR. Widok siatki QBE uzyskanej w wyniku działania kreatora pokazano poniżej
(TZap_Autoprzegląd jest nazwą kwerendy z przykładu 10), zaś schemat okna, w którym kwerenda krzyżowa
prezentuje dane (nie dotyczące tego przykładu) zamieszczono na rys.2 w przykładzie 13.

Po uruchomieniu tej kwerendy należy w widoku Arkusz danych dostosować szerokości jego kolumn, tak aby
możliwie najwięcej kolumn zmieściło się na ekranie. Następnie warto zablokować kolumny MARKA, MODEL i
ewentualnie także kolumnę podsumowania dla wiersza.

Przykład 12 (tworzenie bez użycia kreatora)
Utworzyć kwerendę krzyżową pokazującą ilości wystąpień poszczególnych elementów wyposażenia
dodatkowego w zależności od modeli samochodów. Jako nagłówki wierszy wybrać marki i modele samochodów
uporządkowane alfabetycznie. Nagłówkami kolumn powinny być nazwy poszczególnych elementów
wyposażenia, zaś zliczać należy identyfikatory samochodów, w których występują odpowiednie elementy
wyposażenia. Ponadto należy utworzyć nagłówek wiersza dokonujący podsumowań w obrębie wiersza (wartości
jego pól będą podawać ogólną liczbę dowolnych elementów wyposażenia dodatkowego dla modelu samochodu
odpowiadającego danemu wierszowi widoku Arkusz danych kwerendy) i nadać mu tytuł Razem.
W widoku Arkusz danych utworzonej kwerendy należy tak dobrać szerokości kolumn, aby umożliwić prezentację
jak największej ilości danych na szerokości ekranu, a następnie przy użyciu polecenia menu Format | Zablokuj
kolumny

zablokować kolumny marki, modelu i podsumowania dla wiersza.

Poniżej pokazano schemat źródła danych tej kwerendy oraz końcowy wygląd jej projektu w siatce QBE.

Laboratorium Inżynierskich Baz Danych

5

Przykład 13 (kwerenda krzyżowa z ustalonymi nagłówkami kolumn)
Na podstawie tabel TYPY i SAMOCHÓD zbudować kwerendę krzyżową grupującą samochody znajdujące się w
ofercie sprzedaży według modeli. Należy pominąć modele Opla oraz uwzględniać tylko samochody mające
przebieg poniżej 50 000 km. W arkuszu danych kwerendy umieścić ogólną liczbę samochodów każdego modelu
oraz liczby pojazdów wyprodukowanych w latach 1990-1999 w rozbiciu na roczniki. Uwzględnić też lata, dla
których w ofercie brak samochodów. Wymaga to użycia ustalonych nagłówków kolumn. Przy założeniu, iż
separatorem listy jest średnik, należy dokonać następującego określenia właściwości Nagłówki kolumn w arkuszu
właściwości kwerendy:

Nagłówki kolumn: 1990; 1991; 1992; 1993; 1994; 1995; 1996; 1997; 1998; 1999

Siatkę QBE dla tej kwerendy pokazano poniżej, natomiast jej widok Arkusz danych można obejrzeć na rys. 1.

Wprowadzenie warunku dla marki przez dodatkową kolumnę siatki QBE z opcją Gdzie w komórce
Podsumowanie, a nie bezpośrednio w istniejącej kolumnie nagłówka wiersza, ma na celu wyeliminowanie
rekordów nie spełniających kryterium wyboru jeszcze przed wykonaniem obliczeń agregujących. Warunek
umieszczony w kolumnie nagłówka grupy powodowałby natomiast odrzucanie wyników dla nie spełniających go
grup już po dokonaniu wszystkich obliczeń. Te dwa sposoby umieszczenia warunku są odpowiednio równoważne
członom WHERE i HAVING instrukcji SELECT języka SQL.

Rys. 1. Widok Arkusz danych kwerendy krzyżowej

7. Tworzenie kwerendy w widoku SQL

Przykład 14
Utworzyć w widoku SQL kwerendę wybierającą z tabel TYPY i SAMOCHÓD dane modeli Forda mających
przebiegi nie większe niż 50 000 km, uporządkować je według modeli oraz w ramach modelu malejąco według lat
produkcji, zaś dla danego roku produkcji malejąco według cen. Przetestować działanie utworzonej kwerendy i
sprawdzić zawartość widoku Projekt. Przykładową postać tej kwerendy podano poniżej.

SELECT MODEL, PREDK, R_PROD, PRZEBIEG, CENA
FROM TYPY INNER JOIN Samochód ON TYPY.TYP_ID = Samochód.TYP_ID
WHERE PRZEBIEG < 50000 AND MARKA <> "FORD"
ORDER BY MODEL, R_PROD DESC, PRZEBIEG DESC;

jedno-
znaczne
kombi-
nacje
wartości
kolumn
obiektów
źród-
łowych

nagłówek

wiersza

1

nagłówek

wiersza

2

nagł.
wier.

3

podsumowania w
ramach wiersza

nagłówki kolumn

(wartości wybranej kolumny obiektu źródłowego)

podsumowania (grupowanie
według wartości nagłówków
wierszy i nagłówka kolumny)

Laboratorium Inżynierskich Baz Danych

6

8. Kwerendy funkcjonalne

Przykład 15 (kwerenda tworząca tabelę)
Na podstawie tabel SAMOCHÓD, TYPY, KOLORY i SILNIK utworzyć tabelę o nazwie VW, która będzie zawierać
dane samochodów marki Volkswagen znajdujących się w ofercie sprzedaży. Należy zwrócić uwagę, iż kryterium
wyboru rekordów do tworzonej tabeli wprowadza się dla kolumny MARKA tabeli TYPY, która nie powinna
wchodzić w skład nowej tabeli, gdyż wartości jej pól we wszystkich wierszach tej tabeli musiałyby być takie same.
W kolumnie MARKA siatki QBE należy więc usunąć znak zaznaczenia opcji Pokaż. W projekcie kwerendy można
też określić sposób sortowania rekordów nowej tabeli, tak jak pokazano to na poniższym schemacie siatki QBE
tej kwerendy.

Po utworzeniu tabeli obejrzeć ją w widoku Arkusz danych zwracając uwagę na kolejność rekordów. Następnie
otworzyć tabelę w widoku Projekt i określić jej klucz główny (które z pól w jednoznaczny sposób identyfikuje
samochód ?). Kolejny raz otworzyć widok Arkusz danych tabeli i sprawdzić, czy kolejność wyświetlania rekordów
uległa zmianie.

Przykład 16 (kwerenda aktualizująca)
Zmodyfikować zawartość tabeli VW utworzonej w poprzednim przykładzie za pomocą kwerendy aktualizującej,
która spowoduje zwiększenie o 15% cen wszystkich pojazdów wyprodukowanych przed 1989 rokiem z
wyłączeniem modelu Passat. Schemat siatki QBE dla tej kwerendy pokazano poniżej.

Obie pokazane powyżej postaci projektu kwerendy umożliwiają dokonanie żądanej aktualizacji. Zaletą wersji
zamieszczonej po prawej stronie jest włączenie do podglądu dotychczasowej zawartości tabeli także kolumn
modelu i roku produkcji, co pozwala na łatwą kontrolę należytego wypełniania zadanych warunków zmiany ceny.

Przykład 17 (kwerenda usuwająca)
Z wykorzystywanej w poprzednich przykładach tabeli VW usunąć dane samochodów mających więcej niż 6 lat, z
wyjątkiem modelu Polo. Kwerenda powinna odczytywać datę systemową i na jej podstawie określać warunek dla
roku produkcji pojazdu. Wymaga to użycia dwóch funkcji wbudowanych języka Visual Basic for Applications:
§ Date( ), która zwraca aktualną datę systemową.

§ DatePart(symbol_części_daty, data), która zwraca część daty podanej w drugim argumencie funkcji

odpowiadającą symbolowi umieszczonemu w jej pierwszym argumencie (w polskiej wersji programu Access
symbolem roku przedstawionego przy użyciu czterech cyfr jest "rrrr"). Argumenty funkcji muszą być
rozdzielone przez separator listy określony w środowisku Windows.

Postać siatki QBE dla tej kwerendy pokazano poniżej.

Obejrzeć następnie wiersze przeznaczone do usunięcia w widoku Arkusz danych i ocenić poprawność projektu
kwerendy. Zapamiętać poprawny projekt kwerendy pod wybraną nazwą. Uruchomić kwerendę, dokonać
usunięcia danych, a następnie obejrzeć zawartość tabeli VW w widoku Arkusz danych.

Laboratorium Inżynierskich Baz Danych

7

Przykład 18 (kwerenda dołączająca)
Zbudować kwerendę dołączającą do tabeli VW wiersze usunięte z niej w przykładzie 17. Jako tabele źródłowe
wybrać tabele SAMOCHÓD, TYPY, SILNIK i KOLORY. Siatka QBE tworzonej kwerendy jest pokazana poniżej.

Pole MARKA umieszczone w ostatniej kolumnie siatki służy jedynie do wprowadzenia kryterium wyszukiwania
(dlatego w jego kolumnie rubryka Dołączanie do jest pusta). Kryterium roku produkcji zostało zbudowane tak jak
w przykładzie 17.
Po zbudowaniu kwerendy obejrzeć w widoku Arkusz danych wiersze, które dołączy ona do tabeli VW.
Zapamiętać i uruchomić poprawnie działającą kwerendę, obejrzeć zmodyfikowaną zawartość tabeli VW i
spróbować uruchomić kwerendę po raz drugi. Wyjaśnić przyczyny niepowodzenia.

9. Kwerenda wybierająca nie pasujące dane

Przykład 19
Utworzyć przy użyciu kreatora kwerendę zwracającą samochody bez wyposażenia dodatkowego znajdujące się
w ofercie sprzedaży. Podczas działania kreatora określić tabele SAMOCHÓD i WYPOSAŻ jako tabele
powiązane, a jako pole łączące – pole SAM_ID. Tabelę SAMOCHÓD wybrać przy tym jako tabelę zawierającą
wynikowe rekordy kwerendy, zaś tabelę WYPOSAŻ – jako tabelę zawierającą pokrewne rekordy. Obejrzeć
kwerendę utworzoną przez kreator w widokach Projekt i SQL i w razie potrzeby dokonać jej modyfikacji (np.
dodając jako źródło danych tabelę KOLORY i zastępując liczbowy kod koloru jego nazwą). Przykładowa siatka
QBE kwerendy utworzonej przez kreator została pokazana poniżej.