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

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:

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:

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:

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:

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:

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.

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:

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 dane wyjściowe tego zapytania kolidowały z wynikami, aby tego unikać, to dołączyć 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;

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:

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:

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:

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