ACCESS -KWERENDY (WYBIERAJĄCE, PARAMETRYCZNE, FUNKCJONALNE)

Zakres zajęć:

1. Tworzenie prostej kwerendy od podstaw

2. Kryteria w kwerendzie: proste i złożone

3. Kwerendy oparte na wielu tabelach

4. Operacje na datach.

5. Pola kwerend z wyrażeniami stworzonymi przez użytkownika

6. Formaty dat i operacje na datach

7. Kwerendy parametryczne:

• zwracające dane z zakresu

• z możliwością zmiany kryteriów

8. Kwerendy tworzące nowe tabele

9. Kwerendy:

• dołączające,

• usuwające,

• znajdujące największe wartości,

• aktualizujące ,

• krzyżowe.

10. Kwerendy składające

11. Defragmentacja bazy danych.

CZĘŚĆ I. PROSTE KWERENDY WYBIERAJĄCE

1. Dwa podstawowe sposoby stworzenia prostej kwerendy:

1. samodzielne jej zaprojektowanie

2. użycie kreatora prostych kwerend

2. Zadanie: zaprojektować kwerendę, która wyświetli wszystkie dane z tabeli PUBLIKACJE

1. Kwerendy -> Nowy -> Widok projekt

2. W oknie „Pokaż tabele” wybrać tabele, z których będzie korzystała kwerenda (tu: PUBLIKACJE). Kliknąć „zamknij”

3. Teraz dodać pola do siatki projektu: przeciągnąć pola z listy pól tabeli do wiersza „pole” w projekcie kwerendy. Dodamy pola: tytuł, Idwydawnictwa, RokWydania

4. Uruchom (!)

3. Zmienić dowolny tytuł publikacji na wyniku kwerendy. Zamknąć kwerendę i uruchomić tabelę PUBLIKACJE, sprawdzić co się stało. Wniosek:

UWAGA! Zmiany wykonane na wartościach pól pokazywanych przez kwerendę pojawią się w tabelach i innych kwerendach związanych z tymi tabelami.

4. Zmienić dowolny tytuł publikacji na wyniku kwerendy. Zamknąć kwerendę i uruchomić tabelę PUBLIKACJE, sprawdzić co się stało. Wniosek:

UWAGA! Zmiany wykonane na wartościach pól pokazywanych przez kwerendę pojawią się w tabelach i innych kwerendach związanych z tymi tabelami.

5. Zadanie: posortować dane w kwerendzie rosnąco wg tytułów książek

1. widok projekt

2. ustawić się w wierszu „sortuj” projektu, w kolumnie „tytuł”, wybrać „rosnąco”. Uruchomić. Zamknąć, otworzyć PUBLIKACJE, i sprawdzić czy są zmiany porządku.

UWAGA, zmiana porządku rekordów kwerendy nie ma wpływu na porządek danych w związanej z nią tabeli.

6. Kryteria w kwerendzie: służą do wydobywania konkretnych podzbiorów danych.

7. Zadanie: Umieścić w ostatniej kwerendzie kryterium, które spowoduje, że kwerenda pokaże tylko te rekordy z PUBLIKACJE, które zostały wydane w 1996 roku.

1. widok projekt

2. wiersz „kryteria”, wpisać 1996 w kolumnie „rok wydania”, TAB

3. uruchom. I co?

8. Zadanie. Zmienić powyższe kryterium na:

1. >=1996 AND <=1998. Jak zadziała kwerenda? Uruchom.

2. <=1996 AND >=1998. Uruchom. Co się stało i dlaczego?

9. PRZYKŁADY KRYTERIÓW:

Przykłady wyrażeń, w których jako kryteria zastosowano wartości tekstowe

Pole	Wyrażenie	Opis
MiastoOdbiorcy	"Londyn"	Wyświetla zamówienia dostarczone do Londynu.
MiastoOdbiorcy	"Londyn" Or "Hedge End"	Używa operatora Or aby wyświetlić zamówienia wysłane do Londynu lub Hedge End.
DataWysyłki	Between #1/5/95# And #1/10/95#	Używa operatora Between...And, aby wyświetlić zamówienia zrealizowane nie wcześniej niż 5 stycznia 95 i nie później niż 10 stycznia 95.
DataWysyłki	#2/2/95#	Wyświetla zamówienia zrealizowane 2 lutego 95.
KrajOdbiorcy	In("Kanada", "UK")	Używa operatora In, aby wyświetlić zamówienia wysłane do Kanady lub Wielkiej Brytanii.
KrajOdbiorcy	Not "USA"	Używa operatora Not, aby wyświetlić zamówienia wysłane do wszystkich krajów z wyjątkiem USA.
NazwaKlienta	Like "S*"	Zamówienia wysłane do klientów, których nazwy zaczynają się na literę S.
NazwaFirmy	>="N"	Wyświetla zamówienia wysłane do firm, których nazwy zaczynają się na litery od N do Z.
IDzamówienia	Right([OrderID], 2)="99"	Używa funkcji Right aby wyświetlić zamówienia, których IDzamówienia kończy się na 99.
NazwaFirmy	Len([NazwaFirmy]) >Val(30)	Używa funkcji Len i Val, aby wyświetlić zamówienia wysłane do firm, których nazwy są dłuższe niż 30 znaków.

Przykłady wyrażeń obliczających i wykonujących operacje na datach, a następnie wykorzystujących otrzymane wyniki jako kryteria

Pole	Wyrażenie	Opis
DataWymagana	Between Date( ) And DateAdd("m", 3, Date( ))	Używa operatora Between...And i funkcji DateAdd i Date aby wyświetlić zamówienia, które mają zostać zrealizowane w ciągu trzech miesięcy od daty bieżącej.
DataZamówienia	< Date( )- 30	Używa funkcji Date, aby wyświetlić zamówienia, które mają ponad 30 dni.
DataZamówienia	Year([DataZamówienia])= 1996	Używa funkcji Year, aby wyświetlić zamówienia, które zostały złożone w roku 1996.
DataZamówienia	DatePart("q", [DataZamówienia])=4	Używa funkcji DatePart, aby wyświetlić zamówienia przypadające na czwarty kwartał.
DataZamówienia	DateSerial(Year ([DataZamówienia]), Month([DataZamówienia]) +1, 1)-1	Używa funkcji DateSerial, Year i Month, aby wyświetlić zamówienia do realizacji ostatniego dnia każdego miesiąca.
DataZamówienia	Year([DataZamówienia])= Year(Now()) And Month([DataZamówienia])= Month(Now())	Używa funkcji Year i Month oraz operatora And, aby wyświetlić zamówienia na bieżący rok i miesiąc.

Przykłady wyrażeń, w których jako kryteria zastosowano część wartości znajdującej się w polu

Pole	Wyrażenie	Wynik
NazwaKlienta	Like "S*"	Zamówienia wysłane do klientów, których nazwa zaczyna się na literę S.
NazwaKlienta	Like "*Imports"	Zamówienia wysłane do klientów, których nazwa kończy się słowem "Imports".
NazwaKlienta	Like "[A-D]*"	Zamówienia wysłane do klientów, których nazwa zaczyna się na literę od A do D.
NazwaKlienta	Like "*ar*"	Dostawy wysłane do klientów, w których nazwie znajduje się sekwencja liter "ar".
NazwaKlienta	Like "Maison Dewe?"	Dostawy wysłane do klientów, których pierwsza część nazwy jest słowem "Maison", a druga słowem pięcioliterowym zaczynającym się od "Dewe".

Przykłady wyrażeń, w których jako kryteria zastosowano wyniki podkwerendy:

Pole	Wyrażenie	Wynik
CanaJednostkowa	(SELECT [CanaJednostkowa] FROM [Produkty] WHERE [NazwaProduktu] = "Syrop anyżkowy ")	Produkty, których cena jest taka sama, jak cena syropu anyżkowego.
CanaJednostkowa	>(SELECT AVG([CanaJednostkowa]) FROM [Produkty])	Produkty, których cena jednostkowa jest wyższa niż średnia.
Pensja	> ALL (SELECT [Pensja] FROM [Pracownicy] WHERE ([Stanowisko] LIKE "*Dyrektor*") OR ([Stanowisko] LIKE "*Wiceprezes*"))	Wszyscy przedstawiciele handlowi, których wynagrodzenie jest wyższe od wynagrodzenia któregokolwiek z pracowników, których stanowisko zaczyna się od "Dyrektor" lub "Wiceprezes".
WartośćZamówienia: [CenaJednostkowa]* [Ilość]	> ALL (SELECT AVG([CenaJednostkowa] * [Ilość]) FROM [OpisyZamówień])	Zamówienia, których wartość jest większa od średniej wartości zamówień.

Pytanie: Co przypominają kryteria? Odp. Reguły poprawności.

10. Kryteria z operatorami

Operatory w kryteriach: AND, OR, NOT służą do budowania kryteriów złożonych.

11. Zadanie: Stworzyć nową kwerendę, która pokaże tylko te wydawnictwa, które mieszczą się w Łodzi, oraz ich nazwa zaczyna się na G.

1. kwerendy-> nowy-> widok projekt

2. tabela WYDAWNICTWA

3. kliknąć w tabeli na pole * -> dodamy do projektu wszystkie pola tabeli

4. dodaj ponownie pola „miasto” i „NazwaWydawnictwa” - bo na nie będziemy nakładać kryteria, a nie widać ich wyszczególnionych; nie można budować kryteriów dla pól *.

5. odznacz przy nich opcję „pokaż”, żeby nie pokazywały się podwójnie w wyniku działania.

6. w wierszu kryteria wpisać (w odpow. kolumnach): Łódź, G* (JAK KRYTERIA SĄ W TYM SAMYM WIERSZU (OBOK SIEBIE) TO ZNACZY ŻE SPÓJNIK i)

12. Zadanie: Tym razem te wydawnictwa, które są w Łodzi LUB ich nazwa zaczyna się na H.

1. widok projekt poprzedniej kwerendy

2. usunąć kryterium z pola „NazwaWydawnictwa”

3. wstawić H* wiersz niżej (ZAUWAŻYĆ ŻE TEN WIERSZ MA NAZWĘ „LUB” czyli zmienił się spójnik)

4. uruchom. Jaka różnica?

Pytanie: Mamy w tabeli trzech autorów: Kowalskiego, Malinowskiego i Nowaka. Chcemy uzyskać jedynie rekordy Kowalskiego i Malinowskiego. Które kryterium będzie lepsze?:

• Kowalski OR Malinowski

• NOT Nowak

Odp. a, bo jeśli NOT Nowak, to kiedyś w przyszłości kwerenda mogłaby zwrócić też np. Kwiatkowskiego, a w A) zawsze tylko Kowalski i Malinowski.

13. Zadanie. Kwerenda zwracająca wszystkie wydawnictwa, które nie nazywają się na G. (odp. : NOT G*)

14. Zadanie: Kwerenda zwracająca wszystkie książki, których tytuł zaczyna się na K lub T (odp. K* OR T*)

15. Kwerendy oparte na wielu tabelach: wtedy, gdy informacje, jakie ma zwracać, rozrzucone są po kilku tabelach. Przykładowo, chcemy zobaczyć nie tylko, jakie książki mamy, ale też który autor je napisał i skąd on jest.

16. Zadanie: Stworzyć kwerendę, która zwróci publikacje napisane przez autorów, których mamy w bazie (autor i jego publikacje)

1. widok projekt

2. dodać tabele (KTÓRE?) -> AUTORZY, PUBLIKACJE

3. z AUTORZY przeciągnąć IDAutora, imię, nazwisko, narodowość; z PUBLIKACJE: tytuł

4. uruchom. Jak oceniają kwerendę? (odp. niepotrzebne ID autora na wyniku)

5. wróć do projektu, odznacz pole „pokaż” dla Idautora. Uruchom. Porównaj, co się zmieniło.

17. UWAGA! Jedyna sytuacja, gdy umieszczamy w projekcie kwerendy pola, które nie są potem wyświetlane, wtedy, gdy dla tego pola umieszczamy kryterium (por. poprzednie przykłady z wydawnictwami) lub gdy chcemy według tego pola np. sortować. Teraz tak nie jest, więc możemy usunąć IDAutora. UWAGA 2: nawet jeśli to pole byłoby polem odpowiedzialnym za relację, to i tak ACCESS będzie o tej relacji pamiętał.

18. Zadanie. Usunąć IDAutora z projektu kwerendy:

1. zaznaczyć nagłówek IDAutora

2. DEL.

19. Dodanie kolejnej tabeli do kwerendy.

Chcemy zobaczyć nie tylko jaką książkę napisał autor X, lecz także, które wydawnictwo ją wydało i w jakim mieście ono jest. Trzeba więc dodać tabelę? -> WYDAWNICTWA

20. Zadanie. Zmień projekt kwerendy poprzedniej, tak aby zwracała j.w.

1. widok projekt, kliknąć „dodaj tabelę”, wybrać WYDAWNICTWA

2. czy jest relacja między PUBLIKACJE a WYDAWNICTWA? Jeśli tak, to punkt D), jeśli nie to C)

3. Zamknąć okno kwerendy zachowując zmiany, i założyć relację. Wrócić do projektu kwerendy.

4. Przeciągnąć z WYDAWNICTWA które pola? -> Nazwa, miasto

5. Uruchom. Dopasuj szerokość kolumn. Jak oceniasz wynik (w kontekście tego, co miała zwracać kwerenda?) -> odp. posortować wg nazwisk.

21. Zadanie. Posortować rosnąc wg (do wyboru: nazwisko lub tytuł)

1. projekt

2. kliknąć w polu „sortuj” odpowiedniej kolumny, wybrać „rosnąco”

3. uruchom.

22. Tworzenie kwerendy przy pomocy kreatora.

Zadanie. Stworzyć kwerendę, która zwróci taki sam wynik, jak ostatnia.

1. kwerendy -> nowy -> kreator prostych kwerend

2. wybrać z listy rozwijanej tabelę, potem jej pola (dla wszystkich tabel powtórzyć)

3. wybrać czy kwerenda szczegółowa (lista wszystkich rekordów) czy podsumowująca (pogrupowane i podsumowane dane z rekordów) -> szczegółowa

4. ustalić tytuł kwerendy. Otwórz aby zobaczyć informacje. Utwórz.

5. czy dokładnie taki sam wynik jak poprzednio? -> NIE, nieposortowana. Dlaczego? -> bo kreator nigdzie o to nie pytał. ZMIENIĆ.

7

---