Używanie zapytania składającego w celu łączenia wyników wielu zapytań w jeden wynik

Używanie zapytania składającego w celu łączenia wyników wielu zapytań w jeden wynik

Czasami warto wyświetlić listę rekordów z jednej tabeli lub zapytania razem z rekordami z innych tabel, aby utworzyć jeden zestaw rekordów — listę wszystkich rekordów z co najmniej dwóch tabel. Do tego służy zapytanie składające w programie Access.

Aby dobrze zrozumieć zapytania składające, najpierw musisz zapoznać się z projektowaniem podstawowych zapytań wybierających w programie Access. Aby dowiedzieć się więcej na temat projektowania zapytań wybierających, zobacz Tworzenie prostego zapytania wybierającego.

Uwaga: Niniejszy artykuł dotyczy wyłącznie baz danych programu Access dla komputerów stacjonarnych. Zapytania składającego nie można utworzyć ani używać w internetowych bazach danych programu Access ani w aplikacjach internetowych programu Access.

Analiza działającego przykładu zapytania składającego

Użytkownikowi, który nigdy wcześniej nie tworzył zapytania składającego, może przydać się najpierw analiza działającego przykładu w szablonie Northwind programu Access. Przykładowy szablon Northwind możesz znaleźć na stronie wprowadzenia do programu Access, klikając pozycję Plik > Nowy. Możesz również pobrać jego kopię bezpośrednio z następującej lokalizacji: Przykładowy szablon Northwind.

Po otwarciu bazy danych Northwind w programie Access zamknij wyświetlany na początku formularz okna dialogowego logowania, a następnie rozwiń okienko nawigacji. Kliknij górną część okienka nawigacji, a następnie wybierz pozycję Typ obiektu, aby uporządkować wszystkie obiekty bazy danych według typów. Następnie rozwiń grupę Zapytania. Zostanie wyświetlone zapytanie o nazwie Product Transactions.

Zapytania składające można łatwo odróżnić od innych obiektów zapytań, ponieważ są oznaczone specjalną ikoną przypominającą dwa zachodzące na siebie okręgi, które oznaczają zestaw złożony z dwóch zestawów:

Zrzut ekranu przedstawiający ikonę zapytania składającego w programie Access.

W odróżnieniu od zwykłych zapytań wybierających i funkcjonalnych, w zapytaniu składającym tabele nie są ze sobą powiązane, co oznacza, że nie można tworzyć ani edytować zapytań składających za pomocą projektanta zapytań graficznych w programie Access. Przekonasz się o tym, jeśli otworzysz zapytanie składające w okienku nawigacji. Program Access otworzy je i wyświetli wyniki w widoku arkusza danych. W poleceniu Widoki na karcie Narzędzia główne zobaczysz, że podczas pracy z zapytaniem składającym nie jest dostępny widok projektu. Pracując z zapytaniami składającymi, możesz przełączać się tylko między widokiem arkusza danych a widokiem SQL.

Aby kontynuować analizę tego przykładu zapytania składającego, kliknij pozycję Narzędzia główne > Widoki > Widok SQL. Zostanie wyświetlona składnia SQL definiująca to zapytanie. Na poniższej ilustracji dodaliśmy odstępy w składni SQL, aby ułatwić Ci odróżnienie poszczególnych elementów tworzących zapytanie składające.

Przeglądarka nie obsługuje klipu wideo. Zainstaluj program Microsoft Silverlight, Adobe Flash Player lub Internet Explorer 9.

Przeanalizujmy szczegółowo składnię SQL tego zapytania składającego z bazy danych Northwind:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Pierwsza i trzecia część instrukcji SQL to zasadniczo dwa zapytania wybierające. Te zapytania powodują pobranie dwóch różnych zestawów rekordów: jednego z tabeli Product Orders i jednego z tabeli Product Purchases.

Drugą częścią tej instrukcji SQL jest słowo kluczowe UNION, które informuje program Access, że zapytanie spowoduje połączenie tych dwóch zestawów rekordów.

Ostatnia część tej instrukcji SQL określa kolejność połączonych rekordów przy użyciu instrukcji ORDER BY. W tym przykładzie program Access uporządkuje wszystkie rekordy na podstawie pola Order Date w kolejności malejącej.

Uwaga: Zapytania składające w programie Access są zawsze tylko do odczytu. Nie można zmienić żadnych wartości w widoku arkusza danych.

Tworzenie zapytania składającego przez utworzenie i połączenie zapytań wybierających

Mimo że zapytanie składające można utworzyć bezpośrednio, pisząc składnię SQL w widoku SQL, łatwiejsze może okazać się zbudowanie go z części za pomocą zapytań wybierających. Następnie można skopiować i wkleić części SQL do połączonego zapytania składającego.

Jeśli nie chcesz czytać tej instrukcji, a zamiast niej wolisz obejrzeć przykład, przejdź do następnej sekcji Obejrzyj przykład tworzenia zapytania składającego.

  1. Na karcie Tworzenie w grupie Kwerendy kliknij pozycję Projekt kwerendy.

  2. W oknie dialogowym Pokazywanie tabeli kliknij dwukrotnie tabelę zawierającą pola, które chcesz dołączyć. Tabela zostanie dodana do okna projektu kwerendy.

  3. Zamknij okno dialogowe Pokazywanie tabeli.

  4. W oknie projektu kwerendy kliknij dwukrotnie każde pole, które chcesz dołączyć.Wybierając pola, upewnij się, że dodajesz taką samą liczbę pól i że te pola występują w takiej samej kolejności jak podczas dodawania pól do pozostałych kwerend wybierających. Zwróć szczególną uwagę na typy danych pól — upewnij się, że pola mają zgodne typy danych z polami o tym samym położeniu w pozostałych kwerendach, które łączysz. Jeśli na przykład pierwsza kwerenda wybierająca ma pięć pól, z których pierwsze zawiera dane typu data/godzina, upewnij się, że każda z pozostałych łączonych kwerend wybierających również ma pięć pól, z których pierwsze zawiera dane typu data/godzina, i tak dalej.

  5. Opcjonalnie dodaj kryteria do pól, wpisując odpowiednie wyrażenia w wierszu Kryteria w siatce pól.

  6. Po zakończeniu dodawania pól i ich kryteriów uruchom zapytanie wybierające i przejrzyj jego wyniki. Na karcie Projektowanie w grupie Wyniki kliknij przycisk Uruchom.

  7. Przełącz kwerendę na widok projektu.

  8. Zapisz kwerendę wybierającą i pozostaw ją otwartą.

  9. Powtórz tę procedurę dla każdej z kwerend wybierających, które chcesz połączyć.

Teraz, gdy już masz utworzone zapytania wybierające, możesz je połączyć. W tym kroku utworzysz zapytanie składające, kopiując i wklejając instrukcje SQL.

  1. Na karcie Tworzenie w grupie Kwerendy kliknij pozycję Projekt kwerendy.

  2. Zamknij okno dialogowe Pokazywanie tabeli.

  3. Na karcie Projektowanie w grupie Zapytanie kliknij pozycję Składające. Program Access ukryje okno projektu zapytania i wyświetli kartę obiektu widoku SQL. Na tym etapie karta obiektu widoku SQL jest pusta.

  4. Kliknij kartę pierwszego zapytania wybierającego, które chcesz połączyć w zapytaniu składającym.

  5. Na karcie Narzędzia główne kliknij pozycję Widok > Widok SQL.

  6. Skopiuj instrukcję SQL dla zapytania wybierającego. Kliknij kartę zapytania składającego, którego tworzenie zostało rozpoczęte wcześniej.

  7. Wklej instrukcję SQL kwerendy wybierającej na karcie obiektu widoku SQL kwerendy składającej.

  8. Usuń średnik (;) znajdujący się na końcu instrukcji SQL kwerendy wybierającej.

  9. Naciśnij klawisz Enter, aby przenieść kursor w dół o jeden wiersz, a następnie w nowym wierszu wpisz słowo kluczowe UNION.

  10. Kliknij kartę następnej kwerendy wybierającej, którą chcesz połączyć w kwerendzie składającej.

  11. Powtarzaj kroki 5 do 10 do momentu skopiowania i wklejenia wszystkich instrukcji SQL kwerend wybierających do okna widoku SQL kwerendy składającej. Nie usuwaj średnika ani nie wpisuj niczego po instrukcji SQL ostatniej kwerendy wybierającej.

  12. Na karcie Projektowanie w grupie Wyniki kliknij przycisk Uruchom.

Wyniki zapytania składającego zostaną wyświetlone w widoku arkusza danych.

Obejrzyj przykład tworzenia zapytania składającego

Oto przykład, który możesz odtworzyć w bazie danych Northwind. To zapytanie składające zbiera nazwiska osób z tabeli Customers i łączy je z nazwiskami osób z tabeli Suppliers. Jeśli chcesz to wypróbować, wykonaj te kroki na swojej kopii przykładowej bazy danych Northwind.

Przeglądarka nie obsługuje klipu wideo. Zainstaluj program Microsoft Silverlight, Adobe Flash Player lub Internet Explorer 9.

Poniżej przedstawiono kroki niezbędne do utworzenia tego przykładu:

  1. Utwórz dwa zapytania wybierające o nazwie Query1 i Query2, jako źródeł danych używając odpowiednio tabel Customers i Suppliers. Jako wartości wyświetlanych użyj pól First Name i Last Name.

  2. Utwórz nowe zapytanie o nazwie Query3 początkowo bez źródła danych, a następnie kliknij polecenie Składające na karcie Projektowanie, aby przekształcić to zapytanie w zapytanie składające.

  3. Skopiuj i wklej instrukcje SQL z zapytań Query1 i Query2 do zapytania Query3. Usuń niepotrzebny średnik i dodaj słowo kluczowe UNION. Następnie możesz sprawdzić wyniki w widoku arkusza danych.

  4. Dodaj klauzulę porządkowania do jednego z zapytań, a następnie wklej instrukcję ORDER BY w widoku SQL zapytania składającego. Zwróć uwagę, że gdy w zapytaniu składającym Query3 ma zostać dodane porządkowanie, najpierw usuwa się średniki, a następnie nazwę tabeli z nazw pól.

  5. Ostateczna wersja składni SQL, która łączy i sortuje nazwy w tym przykładzie zapytania składającego, wygląda tak:

    SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
    FROM Customers
    
    UNION
    
    SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
    FROM Suppliers
    
    ORDER BY [Last Name], [First Name];

Jeśli dobrze Ci idzie pisanie składni SQL, możesz oczywiście napisać własną instrukcję SQL zapytania składającego bezpośrednio w widoku SQL. Jednak kopiowanie i wklejanie składni SQL z innych obiektów zapytań może okazać się łatwiejsze. Każde zapytanie może być znacznie bardziej skomplikowane niż użyte tutaj proste przykłady zapytań wybierających. Przed połączeniem poszczególnych zapytań w zapytanie składające warto je utworzyć i starannie przetestować. Jeśli nie udaje się uruchomić zapytania składającego, możesz dostosowywać poszczególne zapytania tak długo, aż będą działały prawidłowo, a następnie utworzyć ponownie zapytanie składające z poprawioną składnią.

Przejrzyj pozostałe sekcje tego artykułu, aby uzyskać więcej porad i wskazówek na temat korzystania z zapytań składających.

W przykładzie z poprzedniej sekcji korzystającym z bazy danych Northwind zostały połączone dane tylko z dwóch tabel. Jednak w zapytaniu składającym można z łatwością połączyć trzy lub więcej tabel. Na przykład, korzystając z poprzedniego przykładu, w wynikach zapytania możesz także uwzględnić nazwiska pracowników z tabeli Employees. W tym celu musisz dodać trzecie zapytanie i połączyć je z poprzednią instrukcją SQL za pomocą kolejnego słowa kluczowego UNION w następujący sposób:

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Po wyświetleniu wyniku w widoku arkusza danych zostanie wyświetlona lista wszystkich pracowników z przykładową nazwą firmy, która prawdopodobnie nie jest potrzebna. Jeśli chcesz, aby to pole pokazywało, czy dana osoba jest pracownikiem Twojej firmy, dostawcy czy klienta, zamiast nazwy firmy możesz wstawić stałą wartość. Składnia SQL może wyglądać tak:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Poniżej pokazano, jak wygląda wynik w widoku arkusza danych. Program Access wyświetla tych pięć przykładowych rekordów:

Employment

Nazwisko

Imię

In-house

Piotrowska

Maria

In-house

Giussani

Laura

Supplier

Glasson

Stuart

Customer

Goldschmidt

Daniel

Customer

Gratacos Solsona

Antonio

Powyższe zapytanie można jeszcze bardziej skrócić, ponieważ program Access odczytuje tylko nazwy pól wyjściowych z pierwszego zapytania w zapytaniu składającym. Tutaj możesz zobaczyć, że usunęliśmy dane wyjściowe z sekcji drugiego i trzeciego zapytania:

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

W zapytaniu składającym programu Access porządkowanie jest dozwolone tylko raz, ale każde zapytanie można filtrować osobno. Na podstawie zapytania składającego z poprzedniej sekcji pokazujemy przykład filtrowania poszczególnych zapytań przez dodanie klauzuli WHERE.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Przełącz się na widok arkusza danych. Zobaczysz wyniki podobne do następujących:

Employment

Nazwisko

Imię

Supplier

Andersen

Elizabeth A.

In-house

Piotrowska

Maria

Customer

Pawlak

Jerzy

In-house

Kwiatkowska

Hanna

Supplier

Hernandez-Echevarria

Amaya

Customer

Mortensen

Sven

Supplier

Sandberg

Mikael

Supplier

Ostrowski

Roman

In-house

Michalski

Dominik

Supplier

Weiler

Cornelia

In-house

Czarnecki

Tomasz

Jeśli zapytania do złożenia są bardzo różne, może zdarzyć się sytuacja wymagająca połączenia różnych typów danych w polu wyjściowym. W takim przypadku zapytanie składające najczęściej zwraca wyniki jako dane typu tekstowego, ponieważ ten typ danych umożliwia przechowywanie zarówno tekstu, jak i liczb.

Aby pokazać, jak to działa, skorzystamy z zapytania składającego Product Transactions w przykładowej bazie danych Northwind. Otwórz tę przykładową bazę danych, a następnie otwórz zapytanie Product Transactions w widoku arkusza danych. Ostatnich dziesięć rekordów powinno przypominać następujące dane wyjściowe:

Product ID

Order Date

Company Name

Transaction

Quantity

77

1/22/2006

Supplier B

Purchase

60

80

1/22/2006

Supplier D

Purchase

75

81

1/22/2006

Supplier A

Purchase

125

81

1/22/2006

Supplier A

Purchase

200

7

1/20/2006

Company D

Sale

10

51

1/20/2006

Company D

Sale

10

80

1/20/2006

Company D

Sale

10

34

1/15/2006

Company AA

Sale

100

80

1/15/2006

Company AA

Sale

30

Załóżmy, że chcesz podzielić pole Quantity na dwa pola — Buy i Sell. Załóżmy również, że pole, które nie ma wartości, ma mieć stałą wartość zero. Składnia SQL dla tego zapytania składającego będzie wyglądała tak:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

Jeśli przełączysz się na widok arkusza danych, zobaczysz, że ostatnich dziesięć rekordów jest teraz wyświetlanych tak:

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

20

0

77

1/22/2006

Supplier B

Purchase

60

0

80

1/22/2006

Supplier D

Purchase

75

0

81

1/22/2006

Supplier A

Purchase

125

0

81

1/22/2006

Supplier A

Purchase

200

0

7

1/20/2006

Company D

Sale

0

10

51

1/20/2006

Company D

Sale

0

10

80

1/20/2006

Company D

Sale

0

10

34

1/15/2006

Company AA

Sale

0

100

80

1/15/2006

Company AA

Sale

0

30

A co, jeśli chcesz, aby pola o wartości zero w tym przykładzie były puste? Możesz zmodyfikować składnię SQL, aby wyświetlać puste miejsce zamiast wartości zero, dodając słowo kluczowe Null w następujący sposób:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Jednak teraz otrzymujesz nieoczekiwany wynik, co możesz zaobserwować, przełączając się na widok arkusza danych. W kolumnie Buy wszystkie pola są puste:

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

 

 

77

1/22/2006

Supplier B

Purchase

 

 

80

1/22/2006

Supplier D

Purchase

 

 

81

1/22/2006

Supplier A

Purchase

 

 

81

1/22/2006

Supplier A

Purchase

 

 

7

1/20/2006

Company D

Sale

 

10

51

1/20/2006

Company D

Sale

 

10

80

1/20/2006

Company D

Sale

 

10

34

1/15/2006

Company AA

Sale

 

100

80

1/15/2006

Company AA

Sale

 

30

Dzieje się tak dlatego, że program Access określa typy danych pól na podstawie pierwszego zapytania. W tym przykładzie Null nie jest liczbą.

Co więc się stanie, jeśli spróbujesz wstawić pusty ciąg w miejsce pustej wartości pól? Składnia SQL w przypadku tej próby może wyglądać tak:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Gdy przełączysz się na widok arkusza danych, zobaczysz, że program Access pobiera wartości Buy, ale konwertuje je na tekst. Widać, że są to wartości tekstowe, ponieważ w widoku arkusza danych są one wyrównane do lewej strony. Pusty ciąg w pierwszym zapytaniu nie jest liczbą, dlatego widzisz takie wyniki. Zauważysz także, że wartości Sell również są konwertowane na tekst, ponieważ rekordy zakupu zawierają pusty ciąg.

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

20

 

77

1/22/2006

Supplier B

Purchase

60

 

80

1/22/2006

Supplier D

Purchase

75

 

81

1/22/2006

Supplier A

Purchase

125

 

81

1/22/2006

Supplier A

Purchase

200

 

7

1/20/2006

Company D

Sale

 

10

51

1/20/2006

Company D

Sale

 

10

80

1/20/2006

Company D

Sale

 

10

34

1/15/2006

Company AA

Sale

 

100

80

1/15/2006

Company AA

Sale

 

30

Jak rozwiązać tę łamigłówkę?

Rozwiązaniem jest wymuszenie na zapytaniu, aby oczekiwało, że wartość pola będzie liczbą. Można to uzyskać za pomocą wyrażenia:

IIf(False, 0, Null)

Warunek do sprawdzenia, False, nigdy nie będzie wartością True, dlatego wyrażenie zawsze zwraca wartość Null, ale program Access nadal ocenia obie opcje wyjściowe i decyduje, czy dane wyjściowe są wartością liczbową, czy wartością Null.

Oto, jak możemy użyć tego wyrażenia w naszym przykładzie roboczym:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Zwróć uwagę, że nie trzeba modyfikować drugiego zapytania.

Jeśli przełączysz się na widok arkusza danych, zobaczysz teraz pożądany wynik:

Product ID

Order Date

Company Name

Transaction

Buy

Sell

74

1/22/2006

Supplier B

Purchase

20

 

77

1/22/2006

Supplier B

Purchase

60

 

80

1/22/2006

Supplier D

Purchase

75

 

81

1/22/2006

Supplier A

Purchase

125

 

81

1/22/2006

Supplier A

Purchase

200

 

7

1/20/2006

Company D

Sale

 

10

51

1/20/2006

Company D

Sale

 

10

80

1/20/2006

Company D

Sale

 

10

34

1/15/2006

Company AA

Sale

 

100

80

1/15/2006

Company AA

Sale

 

30

Alternatywną metodą uzyskania takiego samego wyniku jest poprzedzenie zapytań w zapytaniu składającym jeszcze jednym zapytaniem:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

Dla każdego pola program Access zwraca stałe wartości zdefiniowanego typu danych. Oczywiście nie chcesz, aby wynik tego zapytania obniżał czytelność wyników. Aby tego uniknąć, zastosuj klauzulę WHERE o wartości False:

WHERE False

To prosta sztuczka, ponieważ wynik jest zawsze fałszywy i zapytanie nie zwraca żadnej wartości. Łącząc tę instrukcję z istniejącą składnią SQL, otrzymujemy kompletną instrukcję, która wygląda tak:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Uwaga: Połączone zapytanie w tym przykładzie korzystającym z bazy danych Northwind zwraca 100 rekordów, podczas gdy dwa oddzielne zapytania zwracają 58 i 43 rekordy, co łącznie daje 101 rekordów. Rozbieżność wynika z tego, że dwa rekordy nie są unikatowe. Zobacz sekcję Praca z unikatowymi rekordami w zapytaniu składającym przy użyciu instrukcji UNION ALL, aby dowiedzieć się, jak rozwiązać ten scenariusz za pomocą instrukcji UNION ALL.

Szczególnym przypadkiem zapytania składającego jest połączenie zestawu rekordów zawierającego jeden rekord będący sumą jednego lub więcej pól.

Oto kolejny przykład, który możesz utworzyć w przykładowej bazie danych Northwind, aby zobaczyć, jak uzyskać sumę w zapytaniu składającym.

  1. Utwórz nowe proste zapytanie, aby wyświetlić zakupy piwa (Product ID=34 w bazie danych Northwind), korzystając z następującej składni SQL:

    SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
    FROM [Purchase Order Details]
    WHERE ((([Purchase Order Details].[Product ID])=34))
    
    ORDER BY [Purchase Order Details].[Date Received];
  2. Przełącz się na widok arkusza danych. Powinny być widoczne cztery zakupy:

    Date Received

    Quantity

    1/22/2006

    100

    1/22/2006

    60

    4/4/2006

    50

    4/5/2006

    300

  3. Aby uzyskać sumę, utwórz proste zapytanie agregujące za pomocą następującej składni SQL:

    SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
    FROM [Purchase Order Details]
    WHERE ((([Purchase Order Details].[Product ID])=34))
  4. Przełącz się na widok arkusza danych. Powinien być widoczny tylko jeden rekord:

    MaxOfDate Received

    SumOfQuantity

    4/5/2006

    510

  5. Połącz te dwa zapytania w zapytanie składające, aby do rekordów zakupów dołączyć rekord z całkowitą ilością:

    SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
    FROM [Purchase Order Details]
    WHERE ((([Purchase Order Details].[Product ID])=34))
    
    UNION
    
    SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
    FROM [Purchase Order Details]
    WHERE ((([Purchase Order Details].[Product ID])=34))
    
    ORDER BY [Purchase Order Details].[Date Received];
  6. Przełącz się na widok arkusza danych. Powinny być widoczne cztery zakupy z sumą każdego z nich oraz rekord podsumowujący całkowitą ilość:

    Date Received

    Quantity

    1/22/2006

    60

    1/22/2006

    100

    4/4/2006

    50

    4/5/2006

    300

    4/5/2006

    510

To całe podstawy dodawania sum do zapytania składającego. Możesz również w obu zapytaniach dodać stałe wartości, takie jak „Detail” i „Total”, aby wizualnie oddzielić rekord sumy od pozostałych rekordów. Informacje na temat korzystania ze stałych wartości zawiera sekcja Łączenie co najmniej trzech tabel lub zapytań w zapytanie składające.

Zapytania składające w programie Access domyślnie zawierają tylko unikatowe rekordy. Ale co zrobić, jeśli chcesz uwzględnić wszystkie rekordy? Tutaj może być przydatny kolejny przykład.

W poprzedniej sekcji pokazaliśmy, jak utworzyć sumę w zapytaniu składającym. Zmodyfikuj składnię SQL tego zapytania składającego tak, aby zawierała pole Product ID=48:

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Przełącz się na widok arkusza danych. Powinien być widoczny nieco mylący wynik:

Date Received

Quantity

1/22/2006

100

1/22/2006

200

Jeden rekord oczywiście nie zwraca dwukrotnie całkowitej ilości.

Przyczyną otrzymania takiego wyniku jest dwukrotna sprzedaż jednego dnia tej samej liczby czekolad, co jest zapisane w tabeli Purchase Order Details. Oto wynik prostego zapytania wybierającego przedstawiający oba rekordy w przykładowej bazie danych Northwind:

Purchase Order ID

Produkt

Ilość

100

Northwind Traders Chocolate

100

92

Northwind Traders Chocolate

100

W poprzednio zanotowanym zapytaniu składającym widać, że pole Purchase Order ID nie jest uwzględnione i że dwa pola nie tworzą dwóch unikatowych rekordów.

Jeśli chcesz uwzględnić wszystkie rekordy, użyj w składni SQL instrukcji UNION ALL, zamiast instrukcji UNION. Najprawdopodobniej będzie to miało wpływ na sortowanie wyników, dlatego warto także dołączyć klauzulę ORDER BY, aby określić kolejność sortowania. Oto zmodyfikowana składnia SQL utworzona na bazie poprzedniego przykładu:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Przełącz się na widok arkusza danych. W ostatnim rekordzie powinny być widoczne wszystkie szczegóły, a nie tylko suma:

Date Received

Total

Quantity

1/22/2006

 

100

1/22/2006

 

100

1/22/2006

Total

200

Typowym zastosowaniem zapytania składającego jest źródło rekordów dla kontrolki pola kombi w formularzu. Za pomocą tego pola kombi możesz wybrać wartość do filtrowania rekordów formularza. Na przykład filtrowania rekordów pracowników według miast.

Aby sprawdzić, jak to działa, zobacz kolejny przykład, który możesz utworzyć w przykładowej bazie danych Northwind w celu zilustrowania tego scenariusza.

  1. Utwórz proste zapytanie wybierające przy użyciu następującej składni SQL:

    SELECT Employees.City, Employees.City AS Filter
    FROM Employees;
  2. Przełącz się na widok arkusza danych. Powinny zostać wyświetlone następujące wyniki:

    City

    Filter

    Seattle

    Seattle

    Bellevue

    Bellevue

    Redmond

    Redmond

    Kirkland

    Kirkland

    Seattle

    Seattle

    Redmond

    Redmond

    Seattle

    Seattle

    Redmond

    Redmond

    Seattle

    Seattle

  3. Te wyniki mogą wydawać się niezbyt wartościowe. Rozwiń zapytanie i przekształć je w zapytanie składające, używając następującej składni SQL:

    SELECT Employees.City, Employees.City AS Filter
    FROM Employees
    
    UNION
    
    SELECT "<All>", "*" AS Filter
    FROM Employees
    
    ORDER BY City;
  4. Przełącz się na widok arkusza danych. Powinny zostać wyświetlone następujące wyniki:

    City

    Filter

    <All>

    *

    Bellevue

    Bellevue

    Kirkland

    Kirkland

    Redmond

    Redmond

    Seattle

    Seattle

    Program Access przeprowadza składanie pokazanych wcześniej dziewięciu rekordów za pomocą stałych wartości pól <All> i „*”.

    Ponieważ ta klauzula składania nie zawiera instrukcji UNION ALL, program Access zwraca tylko unikatowe rekordy, co oznacza, że każde miasto jest zwracane tylko raz za pomocą stałych identycznych wartości.

  5. Teraz, gdy zapytanie składające wyświetla każdą nazwę miasta tylko raz, razem z opcją skutecznego wybierania wszystkich miast, możesz użyć tego zapytania jako źródła rekordów dla pola kombi w formularzu. Korzystając z tego konkretnego przykładu jako modelu, możesz utworzyć kontrolkę pola kombi w formularzu, ustawić to zapytanie jako źródło rekordów, określić właściwość Column Width kolumny Filter jako 0 (zero), aby ukryć ją wizualnie, a następnie określić właściwość Bound Column jako 1, aby wskazać indeks drugiej kolumny. We właściwości Filter w samym formularzu możesz następnie dodać kod, na przykład taki jak poniższy, aby aktywować filtr formularza za pomocą wartości, która została wybrana w kontrolce pola kombi:

    Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
    Me.FilterOn = True

    Użytkownik formularza może następnie filtrować rekordy formularza, aby uzyskać określoną nazwę miasta, lub wybrać pozycję <All>, aby wyświetlić listę wszystkich rekordów dla wszystkich miast.

Początek strony

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×