Wyk³ad 3 - Kwerendy

Obiekty baz danych Access

tabele

kwerendy (zapytania)

formularze

raporty

makra

moduły

Tabela

Kwerenda

Formularz

Raport

Makro

Moduł

System baz danych MS Access

Wyszukiwanie danych

wyszukiwanie dowolnego ciągu znaków

definiowanie filtrów

definiowanie kwerend (zapytań)

Wyszukiwanie dowolnego ciągu znaków

standardowa technika wykorzystywana w aplikacjach

MS Windows

umoŜliwia wyszukiwanie danych w wybranej

kolumnie lub całym arkuszu

moŜliwość określenia sposobu porównania

poszukiwanego wzorca z wartościami pola

definicja za pomocą symboli wieloznacznych

(dowolna cyfra)

Wyszukiwanie przy uŜyciu filtrów

bardziej zaawansowana technika wyszukiwania danych w
arkuszu

róŜne rodzaje filtrów: wg formularza, wg wyboru wartości
pola tabeli, szablon QBE

uŜywa się do wybierania i porządkowania wyświetlanych
rekordów

wyszukiwanie moŜliwe jest

tylko w jednej

tabeli

moŜna zapamiętać definicję filtru w postaci zapytania
wybierającego

filtry stosuje się tylko w otwartej tabeli, zamknięcie tabeli
powoduje anulowanie działania filtru

Metody filtrowania

filtrowanie wg formularza

– poprzez wskazanie na

liście wyboru w danym polu poszukiwanej wartości

filtrowanie wg wyboru

– zastosowanie filtru naleŜy

poprzedzić przejściem do komórki zawierającej
poszukiwaną wartość

filtrowanie z wyłączeniem wyboru

– wybierane są

rekordy, które nie zawierają w polu wartości
wskazanej w danej kolumnie

filtrowanie zaawansowane

– złoŜone warunki

filtrowania z wykorzystaniem m.in. operatorów
logicznych AND i OR

Zapytania (kwerendy)

umoŜliwiają dostęp do danych w jednej lub wielu

tabelach

automatycznie wybierają i przetwarzają dane np.

wykonywanie obliczeń, zmiana wartości, sortowanie

rekordów

usuwanie danych z tabeli

tworzenie tabel

łączenie informacji pochodzących z wielu tabel

wprowadzanie zmian w tabelach

Kategorie zapytań (kwerend)

wybierające – prosty sposób wyświetlania informacji
pochodzących z wielu tabel

funkcjonalne

tworzące tabele

aktualizujące

dołączające

usuwające

krzyŜowe – tworzenie złoŜonych zestawień
informacyjnych

SQL

składające

przekazujące

definiujące dane

Sposób tworzenia kwerend

Wybór zakładki Kwerendy.

Klikniecie na przycisk Nowy.

Wybór sposobu tworzenia zapytania:

wykorzystanie kreatorów,

samodzielny projekt.

Wybór tabeli/kwerend na podstawie, których utworzona
będzie kwerenda.

Określenie w oknie projektu charakterystycznych
parametrów dla kwerendy (kryteria wyboru, sortowanie
itp.).

Zapis kwerendy.

Wykonanie kwerendy.

Wyświetlanie kwerend

Widok Arkusza danych

– podgląd danych w postaci

tabelarycznej

Widok Projektu

– definicja kwerendy w szablonie QBE

Widok SQL

– definicja kwerendy w języku SQL

Kwerendy wybierające

wybór informacji z jednej lub wielu tabel

definicja statycznych i dynamicznych warunków

wyboru rekordów

konstruowanie wyraŜeń i wykonywanie działań

matematycznych na wartościach w polach

formatowanie i sortowanie wyświetlanych

danych

Kryteria wyboru rekordów - operatory

operatory porównania:

operator

– porównanie z zadanym wzorcem z uŜyciem

symboli zastępczych:

– pojedynczy znak

- dowolny ciąg znaków

- pojedyncza cyfra

[b-t]

– dowolny znak z podanego zakresu

![b-t]

- dowolny znak nie naleŜący do podanego zakresu

inne operatory

IN (lista wartości)

– naleŜy do zbioru

BETWEEN … AND …

- przedział wartości

IS NULL

– sprawdzenie wartości pustej

Kryteria wyboru rekordów - złoŜone

pojedyncze pola:

uŜycie operatorów

AND

w wierszu Kryterium

wykorzystanie wiersza Kryterium oraz wiersza lub, jeśli

warunek ma postać alternatywy wielu warunków logicznych

dla wielu pól:

warunek w wierszu Kryterium w róŜnych kolumnach szablonu

QBE (iloczyn logiczny warunków dla pojedynczych pól)

wprowadzenie warunków w wierszu Kryterium i wierszu lub

(alternatywa warunków dla pojedynczych pól)

Wybór danych z wielu tabel - uwagi

naleŜy wskazać wszystkie tabele

związki między tabelami w zapytaniu oraz typ połączenia między nimi
są dziedziczone ze schematu relacji

w przypadku braku relacji pomiędzy tabelami w schemacie relacji
Access domyślnie tworzy połączenia między tymi obiektami w oparciu o
te same nazwy pól

moŜliwe jest zdefiniowanie związków pomiędzy tabelami w zapytaniu
całkowicie niezaleŜnie od typu połączenia określonego na etapie
definiowania relacji

usunięcie związków pomiędzy tabelami w zapytani nie wpływa na
schemat relacji pomiędzy tabelami

moŜliwe jest definiowanie dodatkowych związków między obiektami
zapytania, których waŜność ograniczona jest wyłącznie w obrębie
zapytania

Kwerendy parametryczne

umoŜliwiają modyfikację wartości uŜytej w Kryterium podczas uruchamiania
zapytania

wymagane jest wpisanie nazwy parametru w wierszu Kryterium w nawiasach
prostokątnych np:

[Jaki kolor?]

moŜliwe jest definiowanie wielu parametrów w tym samym polu lub dla róŜnych
pól

w wyniku działania kwerendy parametrycznej wyświetlane jest okno dialogowe, w
którym uŜytkownik powinien wprowadzić bieŜącą wartość kryterium

domyślna kolejność wyświetlania okien dialogowych jest zgodna z kolejnością ich
występowania w polach kryteriów

kolejność wyświetlania moŜna zmienić oknie Parametrów kwerendy

Kwerenda parametryczna

– rodzaj kwerendy wybierającej, która po

uruchomieniu pobiera od uŜytkownika parametry kwerendy

Kwerendy wybierające (pola obliczeniowe)

umoŜliwiają wykonywanie obliczeń na wartościach pól

obliczenia moŜna wykonywac dla pól tekstowych,
liczbowych, walutowych, daty i godziny

domyślna nazwa pola to

Wyr#:

pole moŜna dowolnie nazywać, po nazwie pola naleŜy
umieścić dwukropek, np.

Imię i nazwisko:

wyraŜenia moŜna definiować w prosty sposób za
pomocą Konstruktora wyraŜeń

Przykład:

Imię i nazwisko: [Adresy].[Imię] & " " & [Adresy].[Nazwisko]

Kwerendy wybierające – agregacja danych

umoŜliwiają wykonywanie działań na

wartościach tego samego pola

obliczenia moŜna wykonywać na danych typu

Liczba, Data/Godzina, Walutowy, Autonumer

wymagane jest wyświetlenie dodatkowego

wiersza w szablonie QBE (Widok/Sumy)

Funkcje podsumowujące (agregujące)

Grupuj według

– grupowanie rekordów, na których mają być wykonane

obliczenia

Suma

– suma wartości danego pola

Średnia

– średnia arytmetyczna wartości pola

Minimum

– najmniejsza wartość pola

Maksimum

– największa wartość pola

Policz

– zlicza liczbę niepustych wartości pola (rekordów w grupie)

OdchStd

– odchylenie standardowe wartości w polu

Wariancja

– wariancja wartości pola

Pierwszy

– wartość pola z pierwszego rekordu w grupie

Ostatni

– wartość pola z ostatniego rekordu w grupie

WyraŜenie

– pole obliczeniowe zawierające w swoim wyraŜeniu funkcję

agregującą

Gdzie

– kryterium ograniczające liczbę rekordów

Kwerendy funkcjonalne

spełniają określoną funkcję, która polega nie tylko na
wybraniu rekordów

typ kwerend wybierających, którym przypisano
określone zadanie do wykonania na grupie rekordów
(tworzenie tabeli, aktualizacja, dołączanie lub
usuwanie rekordów)

zmiany mają trwały charakter

modyfikacje rekordów jednej tabeli moŜna
wykonywać bez ograniczeń

modyfikacja rekordów wymaga posiadania
odpowiednich praw do wykonania tych operacji

Kwerendy funkcjonalne – c.d.

wykonanie zapytania moŜna poprzedzić kontrolą
poprawności jego wykonania (przełączenie się do
widoku arkusza danych)

uruchomienie kwerendy funkcjonalnej odbywa się
przez wybór Kwerenda/Uruchom

właściwa kwerenda składa się z części realizującej
wybieranie określonych rekordów (kwerenda
wybierająca) i części realizującej zadanie
charakterystyczne dla danego rodzaju kwerendy np.
usunięcie rekordów, dołączenie rekordów, skoiowanie
do innej tabeli

Kwerendy funkcjonalne - rodzaje

kwerendy aktualizujące

kwerendy tworzące tabele

kwerendy dołączające

kwerendy usuwające

Kwerendy aktualizujące

umoŜliwiają szybką i automatyczną modyfikacje wartości pól w

wielu rekordach równocześnie

aktualizacja moŜe dotyczyć wartości pól w jednej tabeli (bez

Ŝadnych ograniczeń) lub rekordów w tabelach tworzących relacje

(obowiązuje warunek kaskadowej aktualizacji rekordów)

Elementy szablonu QBE zapytania aktualizującego:

Pole – nazwa aktualizowanego pola

Tabela – nazwa tabeli, w której zostanie przeprowadzona aktualizacja

Aktualizacja do – wartość stała lub wyraŜenie wstawiane do pola

Kryteria – warunek ograniczający, które rekordy mają zostać

zaktualizowane

Tworzenie kwerend aktualizujących

utworzenie kwerendy wybierającej w szablonie QBE

zmiana rodzaju zapytania (Kwerenda/Kwerenda

aktualizująca)

wskazanie pól, które mają zostać zaktualizowane

wprowadzenie w wierszu Aktualizacja do wartość,

która zostanie przypisana do pola

definicja kryteriów wyboru rekordów do aktualizacji

zapis kwerendy pod unikalną nazwą

Kwerendy dołączające

umoŜliwiają dodanie do istniejącej tabeli nowych rekordów
pochodzących z tabeli kwerendy źródłowej

docelowa tabela musi juŜ istnieć w BD

rekordy moŜna dopisywać w obrębie tej samej bazy lub do innej bazy
MS Access

rekordy moŜna równieŜ dopisywać do otwartej tabeli

Elementy szablonu QBE zapytania dołączającego:

nazwa tabeli docelowej, do której dołączone zostaną dane

Pole – nazwa pola, którego wartości będą kopiowane do innej tabeli

Tabela – nazwa tabeli źródłowej

Sortuj - określanie kolejności dołączania rekordów

Dołączanie do – nazwa pola, do którego zostaną przyporządkowane
wartości z pola tabeli źródłowej

Kryteria – warunek określający, które rekordy mają zostać dołączone

Tworzenie kwerend dołączających

utworzenie kwerendy wybierającej w szablonie QBE

zmiana rodzaju zapytania (Kwerenda/Kwerenda

dołączająca)

wskazanie tabeli docelowej

wskazanie pól tabeli źródłowej, które mają zostać

skopiowane

definicja kryteriów wyboru rekordów do dołączenia

zapis kwerendy pod unikalną nazwą

Kwerendy dołączające - uwagi

dopisanie nowych rekordów odbywa się na końcu
tabeli docelowej

dołączanie nie wymaga zgodności liczby pól w tabeli
źródłowej i docelowej (dla pominiętych pól zostanie
przypisana wartość domyślna lub NULL)

jeŜeli tabela docelowa ma klucz podstawowy, rekordy
nie mogą zawierać wartości NULL, ani teŜ posiadać
zduplikowanych wartości pola klucza

w przypadku rekordów zawierających pole
Autonumer nie naleŜy tego pola dodawać do tabeli
docelowej

Kwerendy tworzące tabele

umoŜliwiają tworzenie kopii zapasowych i archiwizację danych

rezultat zapytania przechowywany jest w postaci trwałej – nowej tabeli

często uŜywane w celu tworzenia tabel pomocniczych, np.
wykorzystywanych do generowania raportów

Elementy szablonu QBE zapytania tworzącego tabelę:

nazwa tworzonej tabeli

Pole – nazwa pola (wyraŜenie), którego wartości będą kopiowane do
nowej tabeli

Tabela – nazwa tabeli (kwerendy) źródłowej

Podsumowanie – rodzaj wyraŜenia grupującego

Sortuj - określanie kolejności dołączania rekordów

PokaŜ – określenie czy dane pole ma być wstawiane

Kryteria – warunek określający, które rekordy mają zostać wybrane i
przeniesione do nowo tworzonej tabeli

Tworzenie kwerend tworzących tabele

utworzenie kwerendy wybierającej w szablonie QBE

zmiana rodzaju zapytania (Kwerenda/Kwerenda

tworząca tabelę)

wskazanie tabeli zawierającej informacje źródłowe

wskazanie pól tabeli źródłowej, które mają zostać

przeniesione do nowo tworzonej tabeli

definicja kryteriów wyboru rekordów do skopiowania

do nowej tabeli

zapis kwerendy pod unikalną nazwą

Kwerendy usuwające

umoŜliwiają usuwanie wielu rekordów jednocześnie

usuwanie moŜe odbywać się w jednej lub w wielu tabelach

wykonanie kwerendy usuwającej (usunięcie rekordów) jest operacją
nieodwracalną

Elementy szablonu QBE zapytania usuwającego rekordy:

Pole – nazwa pola wchodzącego w skład warunku zdefiniowanego w
kryterium

Tabela – nazwa tabeli, z której będą usuwane rekordy

Usuwanie – opcje zapytania (Skąd – wskazanie tabeli, Gdzie –
wskazanie pola)

Kryteria – warunek określający, które rekordy mają zostać usunięte

Tworzenie kwerend usuwających rekordy

utworzenie kwerendy wybierającej w szablonie QBE

zmiana rodzaju zapytania (Kwerenda/Kwerenda

usuwająca)

określenie w wierszu Pole tabeli z której mają być

usuwane rekordy oraz podanie nazw pól, które

definiują warunki usuwania rekordów

definicja kryteriów wyboru rekordów do usunięcia

zapis kwerendy pod unikalną nazwą