Kwerenda

 

Punkt zawiera następujące pozycje:

5. Tworzenie kwerendy

5.1. Kwerenda wybierająca

5.2. Kwerenda parametryczna

5.3. Kwerenda krzyżowa

5.4. Kwerenda funkcjonalna

5.4.1. Kwerenda aktualizująca

5.4.2. Kwerenda dołączająca

5.4.3. Kwerenda usuwająca

5.4.4. Kwerenda tworząca tabelę

5.5. Kwerenda wyszukująca duplikaty

5.6. Kwerenda wyszukująca niepasujące dane

5.7. Kwerenda Autoodnośnika automatycznego

5.8. Kwerenda SQL

5.8.1. Kwerenda składająca

 

5. Tworzenie kwerendy

 

Kwerenda jest zapytaniem skierowanym do tabeli lub innej kwerendy. W wyniku jej wykonania wyświetlone zostaną rekordy tabeli w liczbie i kolejności spełniających warunki zapytania. Kwerendy pozwalają na różne sposoby oglądać, zmieniać i analizować dane. Można ich również używać jako źródeł rekordów dla następnych obiektów (formularzy, raportów i stron dostępu do danych). Najczęściej używanym typem kwerendy jest kwerenda wybierająca.

 

 

5.1. Kwerenda wybierająca

 

Kwerenda wybierająca pobiera dane z jednej lub kilku tabel przy użyciu kryteriów określonych przez użytkownika a następnie wyświetla je w żądanym porządku.

Aby utworzyć kwerendę wybierającą klikamy na opcję „utwórz kwerendę w widoku projekt” w obiekcie KWERENDY (Rys. 1). W otwartym oknie zawierającym spis wszystkich tabel i utworzonych do tej pory kwerend wskazujemy tabelę do której utworzymy zapytanie (niech to będzie tabela STATKI)

 

Rys. 1. Tworzenie kwerendy

 

W dalszej kolejności przenosimy pola z tabeli statki do poszczególnych kolumn kwerendy (chwytamy i upuszczamy lub podwójne klikamy na nazwie pola, lub wybieramy pole w wierszu POLE kwerendy). Możemy uwzględnić w kwerendzie dowolną liczbę dostępnych pól z tabeli – w przykładzie przenosimy wszystkie pola (Rys. 2).

 

Rys. 2. Kwerenda wybierająca – wybór pól

 

Kwerenda zawiera następujące wiersze:

-        pole – nazwa wybranego pola z tabeli

-        tabela – nazwa tabeli z której pochodzi wybrane pole

-        sortuj – do wyboru rosnąco/malejąco, domyślnie bez sortowania

-        pokaż – do wyboru tak/nie

-        kryteria – wiersz w którym wpisujemy parametry zapytania

-        lub – wiersz w którym wpisujemy parametry zapytania

W przypadku braku parametrów zapytań i porządku sortowania, po uruchomieniu kwerendy wyświetlona zostanie cała zwartość tabeli STATKI.

Aby uruchomić kwerendę należy w MENU WIDOK wybrać opcję WIDOK ARKUSZ DANYCH lub kliknąć na przycisku  na pasku narzędzi „projekt kwerendy”. Utworzoną kwerendę należy zamknąć i zapisać (podaj nazwę np. wg bandery). W ten sposób powstała kwerenda jako nowy obiekt dostępna będzie w oknie głównym bazy danych. Uruchamiamy ją kliknięciem lub przyciskiem OTWÓRZ (Rys. 3).

 

Rys. 3. Uruchamianie kwerendy

 

Uruchomiona kwerenda WG BANDERY wyświetli wszystkie rekordy (w kolejności zgodnej z ich wpisem) z tabeli STATKI. Oczywiście znacznie częściej tworzymy kwerendy które wyświetlają część rekordów - tych spełniających kryteria zapytań. Parametry zapytań wprowadzamy w wierszu „kryteria” oraz „lub” w widoku projekt kwerendy.

Dobrą zasadą jest dla każdego zapytania tworzyć oddzielną kwerendę (nawet jeżeli zapytanie dotyczy tej samej tabeli). Wtenczas na większość pytań będziemy mieli gotowe odpowiedzi w formie kwerend. Przypomnijmy, iż dane wyświetlone można dowolnie edytować (modyfikacja, usuwaniem, dodawanie) i zaktualizowane w tabelach. Parametry zapytań mogą mieć różnoraką formę i wyróżniamy:

-        stałe-konkretne wartości. Np. kwerenda, której widok przedstawiono na poniższym rysunku (Rys. 4) wyświetli wszystkie statki bandery norweskiej (parametr poszukiwany - NORWEGIAN) w kolejności alfabetycznej (utworzoną kwerendę zapisz „statki norweskie”).

 

Rys. 4. Kwerenda wybierająca – stały parametr (widok projekt oraz arkusz danych)

 

-        możemy stosować znaki specjalne (* - zastępuje ciąg znaków, ? – zastępuje jeden znak). Dla przykładu parametr k* wprowadzony jako kryterium dla pola NAZWA STATKU spowoduje wyświetlenie wszystkich statków o nazwach rozpoczynających się na k (LIKE jest automatycznie dodawane przez program). Utworzoną kwerendę zachowaj jako „statki na k”

 

Rys. 5. Kwerenda wybierająca – znak specjalny (widok projekt oraz arkusz danych)

 

-        w polach typu liczbowego/walutowego/data/godzina do formułowania zapytań stosować możemy operatory porównań znane np. z arkusza kalkulacyjnego: >,<,>=,<=,<>, operatorów porównania IS, LIKE oraz operatorów logicznych AND, OR, NOT a także wyrażenia języka Microsoft Jet SQL np. BETWEEN...AND. Dla przykładu w wyniku zapytania przedstawionego na Rys. 6 program wyświetli nam statki o długości większej bądź równej 100 metrów znajdujących się w naszej bazie. Utworzoną kwerendę zachowaj jako „statki dłuższe od 100 m”

 

Rys. 6. Kwerenda wybierająca – wyrażenie (widok projekt)

 

-        zapytania oczywiście mogą być bardziej złożone i dotyczyć większej liczby pól. Przedstawiona na poniższym rysunku kwerenda wyświetli te masowce które mają długość mniejszą od 125 metrów. Utworzoną kwerendę zachowaj podając nazwę „drobnicowce o długości do 125 metrów”

 

Rys. 7. Kwerenda wybierająca – zapytanie złożone (widok projekt)

 

-        zapytanie złożone -kwerenda wybierająca. W poniższym przykładzie (Rys. 8) kwerenda wyświetli statki inne niż drobnicowiec o długości w przedziale (50,150) i szerokości w przedziale (12,13). Utworzoną kwerendę zachowaj podając nazwę „statki inne niż drobnicowce o długości i szerokości w zadanym przedziale”

 

 

Rys. 8. Kwerenda wybierająca

 

-        kwerenda wybierająca dodatkowo dla każdego rekordu może wykonać obliczenia w oparciu o dane zawarte w poszczególnych polach rekordu. Dla przykładu obliczmy objętość podwodzia naszych statków. W tym celu utworzymy nową kwerendę do której z tabeli STATKI pobierzemy interesujące nas pola (id_statku, nazwa_statku...,długość, szerokość, zanurzenie_max). Kwerendę zapisujemy (nazwa objętość) i następnie otwieramy w widoku projekt. W nowej-pustej kolumnie klikamy w wierszu POLE i wybieramy opcję BUDUJ (Rys. 9). Uruchomiony zostanie KONSTRUKOTR WYRAŻEŃ w którym poprzez kliknięcia na odpowiednie pola kwerendy OBJETOSC budujemy wyrażenie (iloczyn długości, szerokości i zanurzenia)

 

Rys. 9. Kwerenda wybierająca – konstruowanie wyrażenia

 

-        Zatwierdzamy utworzone wyrażenie, następnie wybieramy właściwości pola (Rys. 9). Tam dokonujemy ustawień formatu pola (format: standardowy, miejsca dziesiętne 2, tytuł: objętość podwodzia). Możemy także zimnic nazwę utworzonego pola z „Wyr 1”: na „Objętość podwodzia:” Nowoutworzone pole możemy jak każde inne przefiltrować wg dowolnie zdefiniowanego parametru (w przykładzie: >10000, sortowanie: rosnąco). W efekcie (Rys. 9) po uruchomieniu kwerendy w każdym rekordzie zostanie dokonane wyliczenie objętości, wyświetlone zostaną wyłącznie te spełniające kryterium. Utworzoną kwerendę zachowaj podając nazwę „wg wyrażenia - statki o pojemności większej niż 10000”

 

 

Rys. 10. Kwerenda wybierająca – konstruowanie wyrażenia, format, filtr. Widok arkusz danych

 

-        obliczenia wykonywane przez kwerendę wybierającą mogą być wykonywane z wykorzystaniem wbudowanych funkcji. Wiemy z kursu stateczności, iż współczynnik pełnotliwości podwodzia  przyjmuje wartości w pewnych przedziałach dla określonych typów statków. Możemy zgrubnie przyjąć, iż dla statków towarowych małych współczynnik ten osiągnie wartość do 0.7. Wartości wyższe będą posiadały statki towarowe duże. Znajdźmy te statki przy czym współczynnik pełnotliwości wyliczymy przy pomocy formuły . Statki duże i małe znajdziemy wykorzystując funkcję IIF(WYRAŻENIE; WARTOŚĆ JEŻELI PRAWDA; WARTOŚĆ JEŻELI FAŁSZ). Budujemy nową kwerendę do w której uwzględnimy pola: id_statku, nazwa_statku, nośność, długość, szerokość, zanurzenie_max. Kwerendę zapisujemy podając nazwę wg pełnotliwości, następnie otwieramy ją w widoku projekt. Podobnie jak w poprzednim przykładzie w nowej-pustej kolumnie wskazujemy na opcję BUDUJ. W oknie konstruktora wyrażeń otwieramy katalog FUNKCJE WBUDOWANE i odnajdujemy funkcję IIF. Po jej wyborze budujemy wyrażenie zgodnie wg Rys. 11

 

Rys. 11. Konstruktor wyrażeń

 

-        Nowopowstałe pole dodatkowo formatujemy (przykład poprzedni). Kwerenda gotowa jest do uruchomienia. Wynik wyliczenia widoczny jest na poniższym rysunku (Rys. 12). Utworzoną kwerendę zachowaj podając nazwę „wg wyrażenia - podział statków na małe i duże”

 

 

Rys. 12. Kwerenda wybierająca – wynik wyliczenia

 

-        kwerenda wybierająca może korzystać z danych zawartych w większej liczbie tabel (budowaliśmy m.in. w tym celu relacje). Warunkiem jest istnienie pól łączących w wybieranych tabelach. Zbudujmy zapytanie korzystając z tabel ZAWINIĘCIA oraz STATKI. Przy budowie kwerendy wskazujemy dwie tabele z których będziemy korzystali. Jeżeli utworzono wcześniej relacje między tabelami wtenczas system sam połączy pola łączące tabel. W przeciwnym przypadku sami wskazujemy pola łączące poprzez przeciągnięcie jednego z nich na drugie. W kwerendzie możemy uwzględnić dowolne pola z obydwu tabel. Dokonać możemy także dowolnego filtrowania (patrz. poprzednie przykłady). Dla przykładu kwerenda na poniższym rysunku (Rys. 13) wyświetli wszystkie statki cumujące w roku 2004 (Between #2004-01-01# And #2004-12-31#), które przypłynęły z ładunkiem (Not "ballast"). Utworzoną kwerendę zachowaj podając nazwę „zawinięcia statków do portu w wybranym okresie – dwie tabele”

 

Rys. 13. Kwerenda wybierająca – dwie tabele

 

-        kwerenda wybierająca – agregująca. Kwerendy mogą wybierać rekordy spełniających określone kryteria. Dodatkowo wybrane rekordy mogą być grupowane wg wartości znajdujących się w polach. Dla grup kwerenda dokonać dodatkowe wyliczenia. Omówię je na przykładzie. Zbudujmy kwerendę do tabeli statki. Uwzględnimy w niej pola: id_statku, bandera, długość, szerokość, zanurzenie_max. Następie klikamy na przycisk sumy , który wyświetli nowy wiersz w kwerendzie – PODSUMOWANIE (Rys. 14). W wierszu tym pojawia się domyślenie opcje GRUPUJ WEDŁUG.

 

Rys. 14. Kwerenda grupująca

 

Oprócz grupowania możemy dla poszczególnych pól dokonać obliczeń w przy pomocy dostępnych funkcji odpowiednio: suma, średnia, minimum, maksimum, policz, odchylenie standardowe, wariancja, pierwszy, ostatni, wyrażenie, gdzie.

Ustalmy dla poszczególnych pól (Rys. 15): id_statku – policz oraz sortowanie malejąco, bandera – grupuj według, długość – średnia, szerokość – maksimum, zanurzenie_max – maksimum. Ustalamy także formaty poszczególnych pól (miejsca dziesiętne, tytuły pól).

 

Rys. 15. Kwerenda grupująca – odpowiedź

 

Po uruchomieniu kwerendy w efekcie zobaczymy jej odpowiedź na następujące pytanie: ile jest statków poszczególnych bander? Jaka jest ich średnia długość? maksymalna szerokość? (szerokość najszerszego) oraz największe zanurzenie statku danej bandery? Utworzoną kwerendę zachowaj podając nazwę „kwerenda agregująca – zestawienie statków wg bander”

 

 

Uwaga!

Dane wyświetlone w kwerendzie grupującej nie mogą być edytowane.

Parametr kryterium do filtracji danych w tabelach mogą przyjąć dowolną postać przedstawioną w poprzednich przykładach.

 

Dla przykładu kwerenda grupująca przedstawiona na poniższym rysunku odpowie na pytanie: ile masowców z bazy może zacumować na nabrzeżu o dostępnej głębokości 7,5 metra? Utworzoną kwerendę zachowaj podając nazwę „zliczenie masowców wg zanurzenia”.

 

 

Rys. 16. Kwerenda grupująca – kryterium

 

góra

 

5.2. Kwerenda parametryczna

 

Kwerenda parametryczna to następna postać kwerendy. Wykonanie kwerendy parametrycznej powoduje wyświetlenie okna dialogowego, w którym należy podać informacje, takie jak kryteria wyszukiwania rekordów czy wartości, które mają być wstawione w polach. Kwerendę można tak zaprojektować, aby użytkownik był pytany o kilka różnych informacji, na przykład o dwie daty. W takim przypadku zostaną znalezione wszystkie te rekordy, które znajdują się między dwoma podanymi datami.

 

-        Każdą wartość parametru filtrującego dane w kwerendach wybierających możemy zastąpić oknem dialogowym (monitem) w którym użytkownik będzie wpisywał kryteria poszukiwania/filtrowania.

-        Okno dialogowe kwerendy parametrycznej wywołujemy nawiasem [] który oczywiście umieszczamy w wierszu KRYTERIA bądź LUB kwerendy. W nawiasie tym wpisujemy treść komunikatu, który powinien skłonić użytkownika do podania odpowiedniej wartości parametru filtracji (Rys. 17).

 

Rys. 17. Kwerenda parametryczna (projekt kwerendy, uruchomienie - pytanie, arkusz danych - odpowiedź)

 

Po uruchomieniu kwerendy w pierwszym kroku wyświetlone zostanie okno (w przykładzie - jakiej bandery...?). Do okna tego wpisujemy wartość parametru (w przykładzie – polska). Po wpisaniu parametru i jego zatwierdzeniu tabela zostanie przefiltrowana. Tylko rekordy spełniające (wartość w polu równa jest wartości poszukiwanej) kryterium zostanie wyświetlone. Kwerendę taką możemy uruchamiać dowolną ilość razy za każdym razem podając inną wartość parametru przeszukiwania. Utworzoną kwerendę zachowaj podając nazwę „monit wg bandery”.

 

-        W kwerendach parametrycznych stosować możemy wszystkie uprzednio przedstawione reguły poszukiwań jak. znaki specjalne, wyrażenia, itd. np.:

§         Instrukcja: LIKE [Podaj początek nazwy statku] & * wprowadzona w polu NAZWA STATKU umożliwi wyszukiwać statki podając część początku ich nazwy,

§         Wyobraźmy sobie sytuację w której świadek incydentu nawigacyjnego zapamiętał tylko pewien fragment nazwy statku uczestniczącego w nim – Instrukcja postaci LIKE * & [Podaj dowolny człon nazwy statku] & * wprowadzona do pola NAZWA STATKU wyświetli wszystkie potencjalne podejrzane statki z naszej bazy które mogły w nim uczestniczyć,

§         Instrukcja >[Podaj datę cumowania] wprowadzona do pola DATA PRZYBYCIA i <[podaj datę odcumowania] w prowadzona do pola DATA WYJSCIA (Rys. 18) umożliwi wyświetlić statki danego armatora (monit proszący o podanie nazwy armatora także się pojawi - zgodnie z wpisem przedstawionym na rysunku!) cumujące w porcie w podanym przez użytkownika przedziale. Utworzoną kwerendę zachowaj podając nazwę „monit statki armatora w okresie”.

 

Rys. 18. Kwerenda parametryczna

 

góra

 

5.3. Kwerenda krzyżowa

 

Kwerenda krzyżowa wyświetla wartości sumowane (sumy, zliczenia i średnie) z jednego pola w tabeli i na podstawie jednego zestawu faktów grupuje je po lewej stronie arkusza danych, a na podstawie drugiego zestawu faktów w górnej części arkusza.

 

Zróbmy kwerendę krzyżową na podstawie danych zawartych w tabeli zawinięcia. Tabela ta zawiera m.in. dane dotyczące zawinięć statków każdego dnia, przywożonych ładunków ich mas itd. Kwerenda którą sporządzimy zestawi ładunki, ich masy, sumę mas oraz zliczy ilość zawinięć w każdym dniu.

Kwerendę rozpoczynamy budować wg jak każdą inną do tej pory poznaną:

  1. W oknie głównym bazy danych, w obiekcie kwerendy klikamy na „utwórz kwerendę widoku projekt”
  2. Wskazujemy tabelę zawinięcia i z niej wybieramy kolejno pola: data przybycia, id_zawinięcia, masa ładunku, ładunek, masa ładunku.

 

Rys. 19. Kwerend krzyżowa – widok projekt

 

 

Sytuację po wyborze przedstawiono na powyższym (Rys. 19) rysunku. Na pasku narzędzi rozwijamy przyciskiem „Typ kwerendy” zawartość pola kombi (Rys. 20) i wybieramy kwerendę krzyżową.

Rys. 20. Typ kwerendy

 

  1. Wybór kwerendy krzyżowej spowodował wyświetlenie dwóch dodatkowych wierszy: PODSUMOWANIE oraz KRZYŻOWE. Pasek podsumowanie już znamy. W nim domyślnie ustawiona jest opcja GRUPUJ WEDŁUG.
  2. Pola których wartości mają być wyświetlane jako nagłówki wierszy w pozycji Krzyżowe ustawiamy opcję Nagłówek wiersza. Dla nich też możemy wybrać funkcje agregujące w wierszu podsumowanie.

W naszym przykładzie wybieramy kolejno: DATA PRZYBYCIA-KRZYŻOWE-GRUPUJ WEDŁUG; ID_STATKU-KRZYŻOWE-POLICZ; MASA LADUNKU-GRUPUJ WEDŁUG- SUMA

 

  1. Pola których wartości mają być wyświetlane jako nagłówki kolumn, w wierszu Krzyżowe wybieramy opcję Nagłówek kolumny. Opcję Nagłówek kolumny można wybrać tylko dla jednego pola. Podsumowanie dla tego pola musi pozostać bez zmian - domyślnie Grupuj według. Domyślnie nagłówki kolumn są sortowane w porządku alfabetycznym lub liczbowym. Jeśli mają wystąpić w innym porządku, albo liczba wyświetlanych nagłówków kolumn ma być ograniczona, należy ustawić odpowiednio właściwość Nagłówki kolumn danej kwerendy (Rys. 21). W naszym przypadku ustawienie nagłówek kolumny wybieramy dla pola ŁADUNEK

 

Rys. 21. Właściwości kwerendy krzyżowej

 

  1. Pole którego wartość ma zostać użyta w kwerendzie krzyżowej (przecięcie kolumny i wiersza) ustawiamy jako Krzyżowe z opcją Wartość. W kwerendzie może wystąpić tylko jedno takie pole!. W przykładzie dla pola MASA LADUNKU wybieramy KRZYŻOWE oraz WARTOŚĆ. Postać kwerendy po ustawieniach przedstawiono na poniższym rysunku

 

Rys. 22. Kwerendy krzyżowa – widok projekt oraz arkusz danych

 

 

Ostatecznie dokonamy formatowania naszej kwerendy - odpowiednio: zmienimy tytuły pól z „PoliczOfIdStatku” na „Dzienna liczba zawinięć”, „Sumaofmasa ładunku” na „Dzienna suma ładunków”, formatu liczb (standardowy dla pola „sumaofmasa ładunku” oraz masa ładunku). Powyższe ustawienia dokonujemy we właściwościach poszczególnych pól. Dodatkowo przy pomocy kryteriów ograniczymy zawinięcia do przedziału dat i wykluczymy zawinięcia pod balastem. Dla pola data przybycia ustawimy sortowanie rosnąco. Ustawienia oraz postać kwerendy w widoku arkusz danych przedstawiono na poniższym rysunku (Rys. 23)

 

Rys. 23. Kwerenda krzyżowa – postać końcowa

 

Zauważmy i w przestawionym przykładzie uwzględniliśmy dwa razy pole MASA ŁADUNKU. Wybór tej był świadomy i miał następujące cel. Po pierwsze przy ustawieniu NAGŁÓWEK WIERSZA-SUMA uzyskaliśmy dla każdego wiersza (w naszym przypadku dnia) sumę wszystkich ładunków przywiezionych do portu. Drugie zastosowanie pola MASA ŁADUNKU z opcjami KRZYŻOWE-WARTOŚĆ-SUMA pozwoliło uzyskać sumę masy (wynik na przecięciu wierszy i kolumn) konkretnego ładunku przywiezionego do portu danego dnia.

Kwerendę zapisujemy podając nazwę „dzienne obroty ładunków”

Dane wyświetlone przez kwerendę zablokowane są do edycji

Kwerendę krzyżową możemy także utworzyć przy pomocy kreatora. W tym celu w oknie głównym bazy danych w obiekcie kwerendy klikamy na przycisk nowy. W otwartym oknie (Rys. 24) wybieramy pozycję „kreator kwerend krzyżowych”. Dalej postępujemy wg wskazówek kreatora (proszę wykonać samodzielnie). W utworzonej przy pomocy kreatora kwerendzie krzyżowej wszelkie kryteria, sortowania, formaty musimy zdefiniować samodzielnie („ręcznie”) w widoku projekt kwerendy.

 

Rys. 24. Nowa kwerenda – kreator kwerend krzyżowych.

 

5.4. Kwerenda funkcjonalna

 

Kwerenda funkcjonalna pozwala na wprowadzenie zmian w wielu rekordach za pomocą jednej procedury. Istnieją cztery rodzaje kwerend funkcjonalnych: usuwająca, aktualizująca, dołączająca i tworząca tabele.

 

5.4.1. Kwerenda aktualizująca

 

Kwerenda aktualizująca

Dokonuje globalnych zmian w grupie rekordów w tabeli lub kilku tabelach. Na przykład można zmienić współczynnik sztauerski wybranych ładunków w istniejących tabelach.

 

Zbudujemy kwerendę aktualizującą, która zmieni nazwę ładunku w tabeli ZAWINIĘCIA.

W tym celu tworzymy kwerendę która obejmie pola, który wartości zamierzamy zmodyfikować (jeżeli są to wartości przechowywane w kilku tabelach, które wykorzystujemy np. do łączenia tabel, wtenczas zaktualizować musimy dane w wszystkich tabelach). W przykładzie zaktualizujemy nazwy ładunków. Oto kolejne kroki:

  1. Tworzymy nową kwerendę uwzględniamy w nim pole ładunek z tabeli ZAWINIĘCIA
  2. W widoku projekt kwerendy po kliknięciu na przycisku „typ kwerendy” wybieramy kwerendę aktualizującą - .
  3. W wierszu ZMIANA NA definiujemy wartość lub wyrażenie aktualizujące dane. Zmieńmy wiec nazwy ładunku „Ballast” w tabeli na „Brak ładunku”.

 

Rys. 25. Kwerenda aktualizująca – widok projekt

 

  1. Dla ZMIANA NA wpisujemy wartość „Brak ładunku” (Rys. 30). W wierszu kryteria wpisujemy „Ballast” aby tylko rekordy zawierające ten wpis zostały zmodyfikowana a nie wszystkie!!
  2. Dane które będą modyfikowane możemy zobaczyć poprzez klikniecie na opcji widok-”widok arkusza danych” lub . Po kliknięciu na przycisku uruchom  dane spełniające kryterium zostaną zaktualizowane. Widok tabel przed i po aktualizacji przedstawiono na poniższym wykresie (Rys. 30). Zapisz kwerendę podając nazwę „aktualizacja nazw ładunku”

 

Rys. 26. Aktualizacja danych przy pomocy kwerendy aktualizującej

 

góra

 

5.4.2. Kwerenda dołączająca

 

Kwerenda dołączająca

Dodaje grupę rekordów z tabeli lub tabel na końcu innej tabeli lub tabel. Na przykład jeśli otrzymalibyśmy tabelę zawierającą dane o statkach z innego źródła wtenczas, aby uniknąć wpisywania wszystkich tych informacji, można dołączyć je do tabeli STATKI. Kwerendy dołączające są również przydatne w następujących sytuacjach:

-        dołączanie pól wybranych na podstawie kryteriów.

-        dołączanie rekordów w sytuacji, gdy część pól jednej tabeli nie ma swoich odpowiedników w drugiej tabeli. Na przykład tabela STATKI w naszej bazie danych 15 pól. Jeśli do tej tabeli chcemy dołączyć rekordy z tabeli, w której 12 z 15 pól jest takich samych, jak w tabeli STATKI, kwerenda dołączająca uwzględni tylko dane w dopasowanych polach, pozostałe zaś będą zignorowane.

 

Wykorzystamy kwerendę dołączającą do archiwizacji danych. Archiwizować będziemy tabelę zawinięcia. Utworzymy błyskawicznie pustą tabelę ZAWINIĘCIA_ARCHIWUM. Tabela ta będzie posiadała identyczną strukturę jak tabela pierwotna ZAWINIĘCIA. W tym celu w obiekcie TABELE w oknie głównym bazy danych kopiujemy tabelę zawinięcia i wklejamy ją. W wyświetlonym oknie zaznaczamy opcję „tylko struktura”, podajemy nazwę i zatwierdzamy (Rys. 27). Możemy oczywiście utworzyć nową tabelę od podstaw. Pamiętać musimy przy tym aby typy i formaty pól odpowiadały polom z tabeli z której będziemy dołączać dane. Unikniemy w ten sposób niezamierzonych strat danych.

 

Rys. 27. Monit przy wklejaniu tabeli do bazy

 

Tworząc tabelę dołączającą wykonujemy następujące kroki.

 

 

  1. Tworzymy kwerendę pozyskującą dane z tabeli pierwotnej (Tabela zawinięcia, uwzględniamy wszystkie pola).
  2. W widoku projekt kwerendy po kliknięciu na przycisku „typ kwerendy” wybieramy kwerendę dołączającą - .
  3. W polu Nazwa tabeli wpisujemy nazwę tabeli (Rys. 28), do której mają zostać dołączone rekordy (Tabela zawinięcia_archiwum).

 

Rys. 28. Dołączanie do – konstrukcja kwerendy dołączającej

 

  1. W wierszu kryteria możemy podać dowolne kryterium (wpiszmy monity ograniczające dane do okresu)

 

Rys. 29. Kryteria kwerendy dołączającej

 

Na powyższym rysunku widoczny jest także nowy wiersz DOŁĄCZANIE DO powstały po wyborze kwerendy dołączającej

 

  1. Kwerenda jest gotowa, możemy ją zapisać podając nazwę „dołącz zawinięcia do archiwum”. Po klikniecie na opcji widok ”widok arkusza danych” lub  zobaczymy dane które będą dołączone do tabeli. Po kliknięciu na przycisku uruchom  dane spełniające kryterium zostaną dołączone (Rys. 30). Na rysunku poniżej przedstawiono dane dołączone do tabeli „zawinięcia_archiwum” z okresu 1-31 styczeń 2003

 

Rys. 30. Tabela pierwotna, kwerenda dołączająca, wynik uruchomienia kwerendy dołączającej – dane dołączone

 

5.4.3. Kwerenda usuwająca

 

Kwerenda usuwająca

Usuwa grupę rekordów z jednej lub kilku tabel. Kwerenda usuwająca może być użyta na przykład do usunięcia statków, które zostały wycofane z eksploatacji. Użycie kwerendy usuwającej powoduje usunięcie całych rekordów, nie zaś wybranych pól w rekordach.

 

W tabeli ZAWINIĘCIA istnieją rekordy dla których nie podano Id_statku. Nie wiemy więc jakie statki zawinęły do portu. Załóżmy, iż rekordy te zostały omyłkowo wstawione. Znajdźmy te rekordy i usuńmy. Zadanie wykonamy przy pomocy kwerendy usuwającej. Tok postępowania jest następujący:

  1. Tworzymy nową kwerendę w widoku projekt z tabeli z której będziemy usuwali rekordy.
  2. Uwzględniamy pole które pozwoli nam wyselekcjonować szukane rekordy (id_statku) dodatkowo możemy dodać inne pola wg uznania lub tez przeciągnąć „*” z listy pól tabeli ZAWINIECIA co pozwoli wszystkie pola tabeli.
  3. Przyciskiem „typ kwerendy”  wybieramy kwerendę usuwającą
  4. Wprowadzamy kryterium dla pola id_statku pozwalające wyselekcjonować szukane rekordy (wiersz kryteria: 0, wiersz lub „is null” - Rys. 29)

 

Rys. 31. Kwerenda usuwająca – widok projekt

 

  1. Kwerenda jest gotowa. Możemy wyświetlić rekordy które będą usunięte ikoną  (widok arkusz danych). Uruchamiając kwerendę ikoną  usuniemy rekordy (Rys. 30).

 

Rys. 32. Kwerenda usuwająca – wynik uruchomienia

 

  1. Zapisz kwerendę podając nazwę „Usuniecie zawinięć gdzie brak statków”

 

góra

 

 

5.4.4. Kwerenda tworząca tabelę

 

 

Kwerenda tworząca tabelę

Tworzy nową tabelę z wszystkich lub z części danych znajdujących się w jednej lub kilku tabelach. Kwerendy tworzące tabele są przydatne w następujących sytuacjach:

Tworzenie tabel, które mają być eksportowane do innych baz danych programu Microsoft Access. Na przykład można utworzyć tabelę zawierającą część pól z tabeli Pracownicy, a następnie wyeksportować ją do bazy danych używanej przez dział kadr.

Tworzenie stron dostępu do danych zawierających dane poczynając od określonego momentu.

 

Tworząc kwerendę tworzącą tabelę postępujemy kolejno:

  1. Tworzymy kwerendę wybierającą która pobierze rekordy z tabeli (tabel), które mają znaleźć się w nowej tabeli.
  2. Utworzymy więc kwerendę, która będzie zawierała zawinięcia statków pod balastem w wybranym okresie (dane znajdują się w dwóch tabelach: STATKI oraz ZAWINIĘCIA).
  3. Przyciskiem „typ kwerendy”  wybieramy kwerendę tworzącą tabelę
  4. Podajemy nazwę tabeli do której zostaną przekazane pobrane rekordy (Rys. 33 - tabela może być zlokalizowana w bieżącej bazie lub zewnętrznej – wtenczas podajemy także ścieżkę dostępu do bazy)

 

Rys. 33. Tworzenie nowej tabeli przy pomocy kwerendy

 

  1. Kwerenda jest gotowa (Rys. 34). Zapisujemy kwerendę podając nazwę „Utwórz tabelę statki pod balastem w okresie”

 

Rys. 34. Kwerenda tworząca tabelę w widoku projekt

 

  1. Kwerenda jest gotowa (Rys. 34). Zapisujemy kwerendę podając nazwę „Utwórz tabelę statki pod balastem w okresie”
  1. Rekordy które zostaną przekazane do nowej tabeli możemy zobaczyć klikając na ikonie  (widok arkusz danych). Uruchamiając kwerendę ikoną  spowodujemy utworzenie tabeli o podanej nazwie i wypełnieniu jej rekordami spełniającymi podane kryteria (Rys. 35).

 

Rys. 35. Kwerenda tworząca tabelę – widok projekt, widok arkusz danych oraz utworzona przez nią tabela dla III kwartału 2004 roku.

 

góra

 

5.5. Kwerenda wyszukująca duplikaty

 

Kwerenda pozwala wyszukać powtarzające się rekordy lub wartości pól w tabeli.

 

Najprostszą metodą utworzenia takiej kwerendy jest wykorzystanie kreatora. W tym celu:

  1. W oknie bazy danych klikamy opcję KWERENDY w polu OBIEKTY, następnie na pasku narzędzi wybieramy pozycję NOWY OBIEKT.
  2. W otwartym oknie dialogowym NOWA KWERENDA wybieramy pozycję Kreator kwerend wyszukujących duplikaty
  3. Dalej postępujemy zgodnie z wyświetlanymi instrukcjami.

 

5.6. Kwerenda wyszukująca niepasujące dane

 

Za pomocą kwerendy wyszukującej niepasujące dane można wyszukiwać w jednej tabeli rekordy, które nie mają swoich odpowiedników w drugiej tabeli. Można na przykład znaleźć statki z znajdujące się w tabeli STATKI (spis), które nie zawinęły do naszego portu (tabela ZAWINIĘCIA).

 

Podobnie jak w poprzednim przypadku najprostszą metodą utworzenia takiej kwerendy jest wykorzystanie kreatora. W tym celu:

  1. W oknie bazy danych klikamy opcję KWERENDY w polu OBIEKTY, następnie na pasku narzędzi wybieramy pozycję NOWY OBIEKT.
  2. W otwartym oknie dialogowym NOWA KWERENDA wybieramy pozycję Kreator kwerend wyszukujących niepasujące dane
  3. Dalej postępujemy zgodnie z wyświetlanymi instrukcjami.

 

Oczywiście kwerendę taką możemy wykonać samodzielnie gdyż jest to zwykła kwerenda wybierająca z odpowiednim kryterium. Dla przykładu problem, który zdefiniowałem powyżej (tzn. pytanie następującej treści: które statki z mego spisu (tabela STATKI) nie zawinęły do portu (nie ma ich w tabeli ZAWINIĘCIA)) przedstawi kwerenda zaprezentowana na poniższym rysunku (Rys. 36)

 

Rys. 36. Kwerenda wyszukująca niepasujące dane – widok projekt

 

góra

 

5.7. Kwerenda Autoodnośnika automatycznego

 

Kwerenda autoodnośnika automatycznego (wielotabelowa) pozwala automatycznie wypełniać wartości pola (pól) dla nowych rekordów. Szczegółowe wymagania są następujące:

 

-        Kwerenda musi być utworzona na podstawie więcej niż jednej tabeli i tabele muszą się znajdować w relacji jeden-do-wielu,

-        Pole sprzężenia po stronie "jeden" relacji musi posiadać indeks unikatowy. Indeks unikatowy oznacza, że pole jest kluczem podstawowym lub jego właściwość Indeksowane w widoku "Projekt" tabeli ma ustawienie Tak (Bez powtórzeń),

-        Pole sprzężenia dodawane do siatki projektu musi być polem z tabeli znajdującej się po stronie "wiele" relacji jeden-do-wielu. Aby Autoodnośnik działał, pole to nie może być kluczem podstawowym i jego właściwość Indeksowane nie może mieć ustawienia Tak (Bez powtórzeń).

-        Wartość wprowadzana w polu sprzężenia po stronie "wiele" relacji musi już istnieć w polu sprzężenia po stronie "jeden" relacji.

Poniżej przedstawię przykład kwerendy autoodnośnika wykorzystując tabele STATKI oraz ZAWINIĘCIA. Wprowadzając kolejne rekordy w tabeli zawinięcia (kolejne zawiniecie statku) po wskazaniu numeru statku zwijającego, system (kwerenda) sam wypełni pola szczegółami statku. Kolejność postępowania jest następująca:

  1. Tworzymy kwerendę wybierającą z dwóch tabel (STATKI, ZAWINIĘCIA) będących w relacji jeden do wielu (jeden statek może wystąpić w wielu zawinięciach - Rys. 37).

 

Rys. 37. Kwerenda autoodnośnika – widok projekt.

 

  1. Kilka warunków musi być spełnionych (zgodnie z wcześniejszym opisem):
    1. Należy uwzględnić w kwerendzie pole sprzężenia ze strony "wiele" relacji jeden-do-wielu (id statku z tabeli zawinięcia)
    2. Pole ze sprzężenia po stronie jeden (w naszym przypadku id_statku z tabeli STATKI) musi być polem unikatowym tj. oznaczonym kluczem podstawowym – i tak jest (sprawdź punkt - tworzenie tabeli)
  2. Uwzględniamy inne pola z tabel wg uznania
  3. Kwerenda jest gotowa możemy ją zapisać podając nazwę „kwerenda autoodnośnika”. Kwerendę w widoku projekt przedstawiono na poniższym wykresie (Rys. 38).

 

Rys. 38. Kwerenda autoodnośnika – widok arkusz danych.

 

  1. Kwerenda jest gotowa możemy ją zapisać podając nazwę „kwerenda autoodnośnika”. Kwerendę w widoku projekt przedstawiono na poniższym wykresie (Rys. 38).

 

  1. Proszę zauważyć, iż po wyborze lub wpisaniu numeru statku w polu ID STATKU pozostałe pola kwerendy (bandera, długość, szerokość, zanurzenie) zostaną automatycznie wypełnione – porównaj rysunki Rys. 38 i Rys. 39.

 

Rys. 39. Kwerenda autoodnośnika – wypełnione dane.

 

góra

 

5.8. Kwerenda SQL

 

 

Kwerendy SQL tworzymy w oparciu o instrukcje języka SQL (Structured Query Language) .

Każda kwerenda, którą do tej pory utworzyliśmy de facto jest reprezentowana poprzez instrukcje SQL. Aby zobaczyć utworzone do tej pory instrukcje wystarczy jakąkolwiek kwerendę wyświetlić w widoku SQL (Rys. 40).

 

Rys. 40. Kwerenda w widoku projekt oraz widoku SQL

 

W Ms Access możemy tworzyć trzy rodzaje kwerend typu SQL - odpowiednio: kwerendę przekazującą, kwerendę definiującą dane oraz kwerendę składającą. Pierwsza z kwerend umożliwia wysyłać zapytania do serwera bazy danych ODBC (Open Database Connectivity). Kwerenda definiująca dane umożliwia tworzyć nowe tabele (Create Table), zmieniać istniejące tabel (Alter table), usuwać tabele (Drop Table), tworzyć indeksy (Create Index). W kursie przedstawię wyłącznie kwerendę składającą.

 

5.8.1. Kwerenda składająca

 

Kwerenda składająca łączy pola (kolumny) z jednej lub kilku tabel lub kwerend w jedno pole lub kolumnę w wynikach kwerendy.

Aby utworzyć kwerendę składająca

  1. W obiekcie kwerendy kliknij Nowa kwerenda – utwórz kwerendę w widoku projekt
  2. Zamknij automatycznie otwarte okno dialogowe pokaż tabelę
  3. W menu Kwerenda wskaż polecenie Wyłącznie SQL, a następnie kliknij polecenie Składająca (Rys. 41).

 

Rys. 41. Wybór kwerendy składającej

 

  1. W otwartym oknie należy wpisać odpowiednią instrukcję SQL (składnia języka SQL wykracza poza zakres tego opracowania. Nie będę więc go tutaj omawiał. Polecam natomiast liczną literaturę na ten temat, która czytelnik znajdzie bez trudu.)
  2. W swojej bazie posiadamy tabelę ładunków oraz tabelę ładunków niebezpiecznych. Gromadzone w nich dane to m.in. nazwa ładunku oraz kod ładunku (Rys. 42). Utworzymy kwerendę zawierającą jeden łączny spis ładunków.

 

Rys. 42. Tabele ładunków bezpiecznych i niebezpiecznych

  1. Tworzymy kwerendę składającą zgodnie z krokami 1-4 i wpisujemy do niej instrukcję SQL postaci przedstawionej na poniższym rysunku (Rys. 43).

 

Rys. 43. Instrukcja SQL kwerendy składującej

 

  1. Wynik uruchomienia przedstawiono na poniższym rysunku (Rys. 44). Arkusz danych kwerendy zawiera wszystkie ładunki i kody z obydwu tabel, dodatkowo dodałem kolumnę „Tabela” z informacją o ładunku (bezpieczny, niebezpieczny) i uporządkowałem w porządku rosnącym nazwy ładunków.

 

Rys. 44. Kwerenda składająca – widok arkusz danych