KWERENDY Z DATAMI I PARAMETRYCZNE.
Przypomnienie formatów dat: otworzyć tabelę AUTORZY w widoku projekt, pole DataUrodzenia: zobaczyć, jakie formaty może mieć data. Wybrać format Data długa.
Wprowadzić maskę wprowadzania tak, aby trzeba było wprowadzić: dwie cyfry-dwie cyfry-cztery cyfry (00-00-0000). Analogicznie dla pola DataŚmierci. UWAGA! Pamiętać, że najpierw podaje się miesiąc, a potem dzień! Sprawdzić, czy działa.
Dodać rekord: Hermann Melville, narodowość USA, Miejsce urodzenia: Nowy Jork. Data urodzenia: 1 sierpnia 1819.
Wejść do innego rekordu i wprowadzić datę śmierci: 02-30-1950. Co się stanie?
Zmienić format daty urodzenia z Data długa na data średnia. Wyświetlić dane. Co się stało? UWAGA: zmiana formatu wyświetlania nie ma wpływu na wartość daty, czyli i tak pamięta pełny rok.
INFO: Access pamięta wszystkie daty jako liczby o podwójnej precyzji. Część całkowita zawiera dane o dacie, część dziesiętna - o czasie. Zaczyna liczyć daty od 31 grudnia 1899 (pamiętana jako nr 1), kończy na roku 9999. Daty sprzed 1 są pamiętane jako liczby ujemne. Dlatego można wykonywać operacje arytmetyczne na datach.
Kwerenda z datami. Zadanie: stwórz kwerendę, która zwróci wszystkie publikacje, podając o nich następujące informacje: tytuł, rok wydania, numer ISBN, imię i nazwisko autora oraz jego datę urodzenia
Zadanie: zmienić tę kwerendę tak, aby zwróciła publikacje tylko tych autorów, którzy urodzili się przed 1 stycznia 1970 roku (Odp.: w wierszu „kryteria” pola „data urodzenia” wpisać <1/1/1970).
INFO: Do dat można stosować takie operatory, jak =, >, <, oraz kombinacje: >=, <=, <>, operatory logiczne (np. NOT 11/26/1965), oraz Between.
Dynamiczne wyliczanie dat. Oznacza, że Access np. na podstawie daty urodzenia wylicza wiek autora bezpośrednio przed każdym wyświetleniem informacji. Potrzebne będą funkcje wbudowane:
Date() - zwraca bieżącą datę
DateDiff(interwał;data1;data2) - zwraca różnicę między dwiema datami. Parametry (tylko podstawowe): interwał - w jaki sposób różnica ma być wyświetlana („d” - dni, „m” - miesiące, „r” - lata).
Zadanie: zmodyfikować kwerendę tak, aby wyświetlała publikacje jak poprzednio (bez ograniczeń na datach urodzenia), ale wyświetlała dodatkowo pole „wiek w latach”, gdzie będzie podawała wiek autorów w latach.
Wykonanie:
Projekt kwerendy.
Usunąć kryterium nałożone na daty urodzenia.
Klik w wierszu „pole” na prawo od DatyUrodzenia. Shift+F2 (aby powiększyć).
wpisać: Wiek w latach: DateDiff(„rrrr”;[DataUrodzenia];date()) UWAGA: nazwę pola trzeba podawać w nawiasach kwadratowych.
Uruchomić kwerendę.
Zapisać kwerendę jako PublikacjeAutorzyWiek
INFO: Do tej pory kryteria wprowadzaliśmy podczas projektowania kwerendy. Użytkownik może chcieć wprowadzać kryteria podczas uruchamiania kwerendy (czyli aby zmienić kryterium nie trzeba wchodzić do projektu). Sposobem na to są tzw. kwerendy parametryczne.
Zadanie: zmodyfikować poprzednią kwerendę (PublikacjeAutorzyWiek) tak aby kwerenda wyświetlała publikacje tylko autorów o podanym przez użytkownika imieniu.
Wykonanie:
projekt kwerendy
usunąć kryterium z pola „wiek w latach” jeśli jest
wiersz „kryteria”, kolumna „Imię”: wpisać wyrażenie: [Wpisz imię: ]
Uruchomić. Wyświetlić tylko publikacje autorów o imieniu Craig.
UWAGA. Nie działają tu znaki specjalne. Jeśli chcieliby wyświetlić np. wszystkich autorów o imieniu na C, to w oknie parametru wyrażenie Like C* nie zadziała. PROBLEM: co z tym zrobić? ODP. Trzeba zmienić kryterium w kwerendzie na Like [Wpisz imię: ]
Kwerenda z dwoma parametrami. Zadanie: stworzyć kwerendę, która pozwoli wybierać publikacje autorów, którzy urodzili się w określonym przedziale czasowym (np. w XIX wieku).
Wykonanie:
Widok projekt (poprzedniej kwerendy)
usunąć wszystkie istniejące kryteria
(najpierw niech pomyślą sami, jakie operatory użyjemy w kwerendzie - przypomnieć sobie zasady pisania reguł poprawności!). Dla daty urodzenia wprowadzić wyrażenie (w wierszu „kryteria”): Between [wpisz datę początkową: ] And [wpisz datę końcową: ].
Uruchomić, wyświetlić autorów urodzonych w 19 wieku. Powtórzyć dla autorów ur. między 1 stycznia 1900 a 31 grudnia 1965.
CZĘŚĆ III. KWERENDY FUNKCJONALNE.
Kwerendy funkcjonalne to kwerendy wykonujące pewne zadania.
11. Pytanie: Skoro kwerenda tworzy tabelę na podstawie danych wyjściowych kwerendy, to jaką kwerendę należy utworzyć najpierw? (Odp: wybierającą).
12. Zadanie: Na podstawie ostatniej kwerendy (z przedziałem dat) stworzyć kwerendę, która poda publikacje autorów urodzonych między 1 stycznia 1900 a 31 grudnia 1965 i zapisze ich do nowej tabeli.
Wykonanie:
Widok projekt kwerendy (ostatniej)
Polecenie: Kwerenda -> Kwerenda tworząca tabelę
Nazwa tabeli, która ma powstać: KwerendaTworząca(PierwszaPróba)
Sprawdzić, czy na pewno zaznaczona opcja „Bieżąca baza danych” (jak widać, można nową tabelę wstawić do innej bazy danych)
Uruchom. Wprowadzić odpowiednie daty. Potwierdzić, że chcemy nową tabelę.
Kwerendę zmodyfikowaną zapisać pod nazwą PublikacjeAutorzyWiekUtwórzTabele. Wejść do zakładki Kwerendy w bazie danych. Jak teraz wygląda ikona obok nazwy kwerendy?
Kliknąć na zakładce Tabele i sprawdzić, że pojawiła się nowa tabela. Otworzyć ją. Co się w niej znajduje?
Kwerenda dołączająca. Wyciąga dane z jednej tabeli lub kwerendy i dołącza je do innej tabeli (także w innej bazie, lub np. do FoxPro lub dBase). Pytanie: czy widzisz różnicę w stosunku do kwerendy tworzącej tabele?
Zadanie: Utworzyć kwerendę, która pobierze rekordy znajdujące się w tabeli KSIĄŻKIJIMBOBA i dołączy je do tabeli PUBLIKACJE.
Wykonanie:
najpierw obejrzeć zawartość tabeli PUBLIKACJE.
Najpierw sam(a) tworz kwerendę wybierającą książki np. Jima Boba z tabeli KSIĄŻAKIJIMBOBA i uruchom ją jako zwykłą wybierającą.
[ Kwerendy->Nowy->Widok projekt
Dodać tabelę KSIĄŻKIJIMBOBA
Przeciągnąć gwiazdkę (*) z tej tabeli do wiersza „Pole” siatki projektu (w ten sposób dołączymy do kwerendy wszystkie pola)
Uruchomić, aby sprawdzić jak działa.]
Ponownie widok projekt, kliknąć „Typ kwerendy” i wybrać „Kwerenda dołączająca”. Otworzy się okno dialogowe „Dołącz”
Wybrać PUBLIKACJE.
Uruchomić. Potwierdzić dołączenie.
Zamknąć kwerendę pod nazwą DODAWANIE (zwrócić uwagę na ikonę kwerendy)
Otworzyć PUBLIKACJE, czy zadziałało?
Kwerenda usuwająca dane.
Cel: usuwanie więcej niż jednego rekordu naraz (jeśli baza jest bardzo duża, to usunięcie np. wszystkich rekordów obejmujących publikacje pewnego wydawnictwa może być żmudne).
Zadanie. Stworzyć kwerendę, która usunie z bazy wszystkie książki wydane przez JimBob Publishing Company.
Wykonanie:
Najpierw sam(a) tworz kwerendę wybierającą, która po wprowadzeniu parametru będącego nazwą wydawnictwa pokaże wszystkie publikacje żądanego wydawnictwa (tu: JimBob Publishing Company) (Uwaga! Ma pokazać wszystkie pola dot. publikacji UWAGA 2: będą potrzebne w projekcie kwerendy dwie tabele: PUBLIKACJE i WYDAWNICTWA, bo kryterium dotyczy pola „NazwaWydawnictwa”!)
[Kwerendy -> Nowy -> Widok projekt
dodać PUBLIKACJE i WYDAWNICTWA
do projektu przeciągnąć gwiazdkę z PUBLIKACJI i pole NazwaWydawnictwa z WYDAWNICTW
W wierszu „Kryteria” pola NazwaWydawnictwa wpisać [Podaj nazwę wydawnictwa: ]
Uruchomić, wpisać JimBob Publishing Company]
Ponownie Widok Projekt.
Kwerenda -> Kwerenda usuwająca
zamiast wiersza „Sortowanie” mamy teraz „usuwanie”. W tym wiersz, w kolumnie Publikacje*, ustawić wartość Skąd, która oznacza, że z tej tabeli będą usuwane rekordy.
Uruchomić, potwierdzić usunięcie.
Zapisać kwerendę jako USUWANIE.
Sprawdzić w PUBLIKACJE, czy zadziałała.
Kwerenda znajdująca „górne” wartości.
Te kwerendy wyszukują np. pięć najlepiej (najgorzej) sprzedających się książek.
Zadanie: Stworzyć kwerendę, która wyszuka pięć najlepiej sprzedających się publikacji.
Wykonanie:
Kwerendy -> nowy -> Widok projekt
dodaj tabelę POZYCJEZAMÓWIENIA
Do siatki przeciągnij pola Idpublikacji oraz Ilość.
Uruchom. Co zwróciła kwerenda? Co się nie podoba (Odp. Nie pogrupowane dane)
Widok Projekt. W wierszu Sortuj pola Ilość wybrać Malejąco.
Rozwinąć (na pasku narzędzi) listę „Największe wartości”. Jak myślisz, którą opcję wybrać? (Odp. „5”).
Uruchom. Co się zmieniło? czy już jest to wynik, jakiego potrzebujemy? (Odp. NIE, wciąż nie pogrupowane, szukamy pięciu najlepiej sprzedających się publikacji, a nie pięciu największych zamówień!)
Ponownie Widok Projekt. Kliknąć na przycisku Podsumowania na pasku narzędzi. Co się zmieniło w siatce projektu kwerendy? (odp. pojawił się wiersz „podsumowanie”).
W kolumnie Idpublikacji pozostawić opcję „grupuj według”, bo według tej kolumny chcemy mieć pogrupowane wyniki. W kolumnie Ilość wybrać opcję „Suma”, bo chcemy zsumować ilość sprzedaży dla każdej publikacji.
Uruchom. OK.? Zwrócić uwagę na etykiety kolumn!!! Zapisać jako „Pięć najlepszych książek”.
Pytanie: Co zmienić, aby znaleźć pięć najgorzej sprzedających się książek? (Odp. zmienić porządek sortowania na „rosnąco"). ZROBIĆ!
INFO: Access wybiera automatycznie „górne” wartości z pierwszej (licząc od lewej) kolumny, która jest posortowana. Jeśli nie byłaby żadna posortowana, to Access standardowo wybrałby 5 największych wartości z kolumny pierwszej, czyli u nas z Idpublikacji.
Zadanie (konieczne!!!): Ponownie uruchomić kwerendę dołączającą książki JimBob Publishing Company do PUBLIKACJE!!! (Będą nam potrzebne w kolejnym zadaniu)
Kwerendy aktualizujące
Kwerenda aktualizująca to kwerenda, której uruchomienie zmienia wartości pól docelowej tabeli. Jedyna kwerenda funkcjonalna, która działa na polach, a nie na całych rekordach. Przykładowo, chcąc usunąć rekord/rekordy, używa się kwerendy usuwającej, ale chcąc usunąć zawartość jednego/kilku pól, trzeba użyć kwerendy aktualizującej.
Zadanie: Stworzyć kwerendę, która znajdzie rekordy publikacji związane z wydawnictwem JimBob Publishing Company i zmieni wartość pola StronaWWW na http://www/jimbob.com/Books.
Wykonanie:
Kwerendy -> Nowy -> Widok projekt
Dodaj tabelę PUBLIKACJE
Plik->Zapisz jako KWR-UAKTUALNIJ. Dodaj do siatki projektu IDWydawnictwa i StronaWWW
Uruchom. Dlaczego Access wyświetlił wszystkie rekordy? Odp. bo nie zdefiniowaliśmy żadnego kryterium.
Widok projekt, dodać kryterium 6 w kolumnie IDWydawnictwa. Uruchomić.
Widok projekt. Z listy kwerend wybrać kwerendę aktualizującą. Access pokazał w projekcie wiersz „Zmiana na:”. W wierszu tym w kolumnie StronaWWW wpisz http://www.jimbob.com/books
Uruchom. Potwierdź aktualizację. Zamknij kwerendę zachowując zmiany.
Otwórz PUBLIKACJE w widoku Arkusz danych i sprawdź czy kwerenda zadziałała.
Kwerendy krzyżowe
Kwerenda krzyżowa to macierz z operacją matematyczną, wykonywaną na każdym przecięciu. Przykład: macierz, gdzie w pionie mamy wymienione wydawnictwa, a w poziomie liczbę książek wydanych w kolejnych latach przez kolejne wydawnictwa.
Zadanie: stworzyć kwerendę, która pokaże liczbę książek wydanych w kolejnych latach przez poszczeg. wydawnictwa i sumę wszystkich wydanych przez nie książek (Układ: w wierszach - wydawnictwa, w kolumnach - lata).
Wykonanie:
kwerendy -> Nowy -> Kreator kwerend krzyżowych
Wybrać PUBLIKACJE. Dalej
W wierszach mają być wydawnictwa, więc kliknąć (co?) IDWydawnictwa. Dalej.
W nagłówkach kolumn mają być lata wydania: wybrać (co?) RokWydania. Dalej.
Kolumną zliczaną dla każdego roku i każdego wydawcy jest (co?) Idpublikacji, więc wybrać i pozycja „Zlicz” (pytanie: a dlaczego nie funkcja „suma”?). Dalej.
Utwórz. Zapisz pod nazwą PUBLIKACJE_KRZYŻOWA.
Widok projekt tej kwerendy. Obejrzeć sobie. Uruchomić!
Zadanie: Zmienić tę kwerendę tak, aby pokazywała jak kształtowała się liczba wydawanych pozycji w poszczególnych latach dla kategorii. Wykonanie: w projekcie kwerendy zamienić w wierszu Pole: Idwydawnictwa na Idkategorii.
Kwerenda składająca.
Jest to kwerenda składająca zbiory rekordów pochodzące z różnych tabel w jeden plik wynikowy. Np. lista wszystkich wydawnictw i autorów znajdujących się w bazie danych. Uwaga. Do utworzenia kwerendy składającej nie można użyć siatki projektu, tylko języka SQL.
Zadanie: Stworzyć kwerendę jak wyżej.
Wykonanie:
Kwerendy -> Nowy -> Widok projekt
Zamknąć okno „Pokaż tabele”
Polecenie Kwerenda ->Wyłącznie SQL -> Składająca
wpisać wyrażenie:
SELECT Imię + ' ' + Nazwisko AS Nazwa FROM Autorzy
UNION
SELECT NazwaWydawnictwa AS Nazwa FROM Wydawnictwa
UWAGA. Każde wyrażenie SELECT w kwerendzie składającej musi zwracać taką samą ilość pól oraz odpowiadające sobie pola z kolejnych wyrażeń SELECT muszą mieć odpowiadające sobie typy danych. Można używać pól liczbowych i tekstowych dla tego samego pola wynikowego.
Uruchom. Zauważ, że dane wymieszane jako Nazwa i domyślnie posortowane rosnąco.
Wróć do okna SQL i na końcu dopisz: ORDER BY Nazwa DESC. Uruchom. Co się zmieniło?
Wróć do okna SQL i skasuj sortowanie malejące. Zapisz kwerendę jako Lista nazw. Zwróć uwagę na ikonę.
Defragmentacja bazy danych.
Cel: Gdy np. dodaje się tabele lub kwerendy, Access zwiększa plik bazy (mdb) tak, aby odpowiadał nowemu obiektowi. Gdy jednak później usunie się taki element z bazy, to rozmiar pliku nie zmniejsza się. Defragmentacja bazy „oczyszcza ją” z pustych miejsc, które Access pozostawia w strukturze jej pliku.
Zadanie: sprawdzić aktualny rozmiar bazy danych, zdefragmentować ją i ponownie sprawdzić rozmiar bazy danych.
Wykonanie:
Plik -> Właściwości bazy danych
Karta „Ogólne”: tu pokazany rozmiar bazy. Zapisać sobie gdzieś na kartce. Zamknąć okno właściwości.
Narzędzia -> Narzędzia bazy danych -> Defragmentuj bazę danych.
Ponownie sprawdzić rozmiar bazy i porównać z poprzednim.
UWAGA: wielkość zredukowanego miejsca zależy od liczby elementów i operacji wykonywanych na bazie danych.
1