Czasami może być konieczne połączenie rekordów z jednej tabeli lub zapytania z rekordami z jednej lub kilku innych tabel w jeden wynik. Tak właśnie robi 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.
Analiza działającego przykładu zapytania składającego
Jeśli zapytanie składające nie zostało jeszcze utworzone, warto najpierw przeanalizować działający przykład w szablonie Northwind Access. Możesz wyszukać przykładowy szablon Northwind na stronie wprowadzenie programu Access, wybierając pozycję Plik>nowy. Możesz również pobrać kopię bezpośrednio z szablonu przykładowego Northwind.
Po otwarciu bazy danych Northwind w programie Access odrzuć wyświetlone najpierw okno dialogowe logowania, a następnie rozwiń okienko nawigacji. Zaznacz górną część okienka nawigacji, a następnie wybierz pozycję Typ obiektu , aby uporządkować wszystkie obiekty bazy danych według typu. Następnie rozwiń grupę Zapytania , a zobaczysz zapytanie o nazwie Transakcje produktów.
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:
W przeciwieństwie do zwykłych zapytań wybierających i funkcjonalnych tabele nie są powiązane w zapytaniu składającym. Oznacza to, że nie można tworzyć ani edytować zapytań składających za pomocą projektanta zapytań graficznych programu Access. Jeśli otworzysz zapytanie składające z poziomu okienka nawigacji, program Access otworzy je i wyświetli wyniki w widoku arkusza danych. W obszarze Widoki na karcie Narzędzia główne zwróć uwagę, że widok projektu nie jest dostępny podczas pracy 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ęWidok SQLWidoki>główne> w celu wyświetlenia składni definiującej SQL go. Na tej ilustracji dodaliśmy kilka dodatkowych odstępów, SQL dzięki czemu można łatwo zobaczyć różne części składające się z zapytania składającego.
Przyjrzyjmy SQL się szczegółowo składni 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 SQL instrukcji jest UNION słowo kluczowe, które informuje program Access o połączeniu tych dwóch zestawów rekordów.
Ostatnia część tej SQL instrukcji określa kolejność połączonych rekordów za pomocą ORDER BY instrukcji. W tym przykładzie program Access porządkuje wszystkie rekordy według pola Data zamówienia 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ć, pisząc składnię SQL bezpośrednio w widoku SQL, tworzenie go w częściach za pomocą zapytań wybierających może być łatwiejsze. 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.
- Na karcie Tworzenie w grupie Kwerendy kliknij pozycję Projekt kwerendy.
- Kliknij dwukrotnie tabelę zawierającą pola, które chcesz uwzględnić. Tabela zostanie dodana do okna projektu kwerendy.
- W oknie projektu zapytania kliknij dwukrotnie każde z pól, które chcesz uwzględnić. Podczas wybierania pól upewnij się, że dodasz tę samą liczbę pól w tej samej kolejności, którą dodasz do innych zapytań wybierających. Zwracaj szczególną uwagę na typy danych pól i upewnij się, że mają one zgodne typy danych z polami znajdującymi się w tej samej pozycji w innych połączonych zapytaniach. Jeśli na przykład pierwsze zapytanie wybierające ma pięć pól, z których pierwsze zawiera dane daty/godziny, upewnij się, że każde z połączonych zapytań wybierających ma również pięć pól, z których pierwsze zawiera dane daty/godziny itd.
- Opcjonalnie dodaj kryteria do pól, wpisując odpowiednie wyrażenia w wierszu Kryteria w siatce pól.
- 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.
- Przełącz kwerendę na widok projektu.
- Zapisz kwerendę wybierającą i pozostaw ją otwartą.
- Powtórz tę procedurę dla każdej z kwerend wybierających, które chcesz połączyć.
Po utworzeniu zapytań wybierających nadszedł czas, aby je połączyć. W tym kroku utworzysz zapytanie składające, kopiując i wklejając SQL instrukcje.
- Na karcie Tworzenie w grupie Kwerendy kliknij pozycję Projekt kwerendy.
- Na karcie Projektowanie w grupie Zapytanie kliknij pozycję Składające. Program Access ukryje okno projektu zapytania i wyświetli kartę obiektu Widok SQL . Na tym etapie karta jest pusta.
- Kliknij kartę pierwszego zapytania wybierającego, które chcesz połączyć w zapytaniu składającym.
- Na karcie Narzędzia główne kliknij pozycję Wyświetl>widok SQL.
- Skopiuj instrukcję
SQLdla zapytania wybierającego. Kliknij kartę zapytania składającego, którego tworzenie zostało rozpoczęte wcześniej. - Wklej instrukcję
SQLdla zapytania wybierającego na karcie obiektu widoku SQL zapytania składającego. - Usuń średnik (
;) na końcu instrukcji zapytaniaSQLwybierającego. - Naciśnij klawisz Enter, aby przenieść kursor o jeden wiersz w dół, a następnie wpisz
UNIONtekst w nowym wierszu. - Kliknij kartę następnej kwerendy wybierającej, którą chcesz połączyć w kwerendzie składającej.
- Powtarzaj kroki od 5 do 10 do momentu skopiowania i wklejenia wszystkich
SQLinstrukcji zapytań wybierających do okna widoku SQL zapytania składającego. Nie usuwaj średnika ani nie wpisuj niczego zgodnie z instrukcjąSQLostatniego zapytania wybierającego. - 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żna odtworzyć w przykładowej 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.
Poniżej przedstawiono kroki niezbędne do utworzenia tego przykładu:
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.
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.
Skopiuj i wklej instrukcje SQL z zapytań Query1 i Query2 do zapytania Query3. Pamiętaj, aby usunąć dodatkowy średnik i dodać
UNIONsłowo kluczowe. Następnie możesz sprawdzić wyniki w widoku arkusza danych.Dodaj klauzulę porządkowania do jednego z zapytań, a następnie wklej instrukcję
ORDER BYdo zapytania składającego w widoku SQL. 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.Wersja ostateczna
SQL, która łączy i sortuje nazwy dla tego przykładu zapytania składającego, jest następująca: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 znasz składnię do pisania SQL , możesz napisać własną SQL instrukcję dotyczącą 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.
Łączenie co najmniej trzech tabel lub zapytań w zapytanie składające
W przykładzie z poprzedniej sekcji korzystającej z bazy danych Northwind są łą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];
Podczas wyświetlania wyniku w widoku arkusza danych wszyscy pracownicy będą wyświetlani na liście z przykładowymi nazwami firm, co prawdopodobnie nie jest bardzo przydatne. Jeśli chcesz, aby to pole pokazywało, czy dana osoba jest pracownikiem domowym, dostawcą, czy klientem, możesz zamiast nazwy firmy dołączyć stałą wartość . Wygląda to następująco SQL :
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 |
Zapytanie można jeszcze bardziej ograniczyć, ponieważ program Access odczytuje nazwy pól wyjściowych tylko z pierwszego zapytania w zapytaniu składającym. W tym miejscu dane wyjściowe z drugiej i trzeciej sekcji zapytania zostaną usunięte:
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];
Filtrowanie w zapytaniach składających
W zapytaniu składającym programu Access kolejność jest dozwolona tylko raz, ale każde zapytanie można filtrować osobno. Na podstawie zapytania składającego z poprzedniej sekcji poniżej przedstawiono 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 |
Mieszanie typów danych
Jeśli zapytania składające są bardzo różne, może wystąpić sytuacja, w której pole wyjściowe musi łączyć dane o różnych typach danych. 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 Ilość na dwa pola: Kup i Sprzedaj. Załóżmy też, że dla pola, które nie ma żadnej wartości, ma być wyświetlana stała wartość zero. Poniżej przedstawiono SQL wygląd tego zapytania składającego:
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 |
Co zrobić, jeśli pola z wartościami zerowymi mają być puste w tym przykładzie? Możesz zmodyfikować wyświetlanie SQL niczego zamiast zera, dodając Null słowo kluczowe, jak pokazano poniżej:
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 się stanie, jeśli spróbujesz wstawić pusty ciąg dla pustej wartości pól? Dla SQL tej próby może wyglądać następująco:
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ę?
Jednym z rozwiązań jest wymuszenie, aby zapytanie oczekiwało, że wartość pola będzie liczbą. Możesz to zrobić za pomocą tego wyrażenia:
IIf(False, 0, Null)
Warunek, który należy sprawdzić, Falsenigdy nie Truejest , więc wyrażenie zawsze zwraca Null. Jednak program Access nadal ocenia obie opcje wyjściowe i traktuje dane wyjściowe jako liczbowe lub 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;
Nie musisz 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 dane wyjściowe tego zapytania kolidowały z wynikami, aby tego unikać, to dołączyć klauzulę WHERE o wartości False:
WHERE False
To mała sztuczka. Warunek jest zawsze fałszywy, dlatego zapytanie nie zwraca niczego. Łą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
W tym przykładzie połączone zapytanie w bazie danych Northwind zwraca 100 rekordów, a dwa pojedyncze zapytania zwracają 58 i 43 rekordy, co daje łącznie 101 rekordów. Ta różnica występuje, ponieważ dwa rekordy nie są unikatowe. Zobacz Praca z unikatowymi rekordami w zapytaniach składających przy użyciu instrukcji UNION ALL, aby dowiedzieć się, jak rozwiązać ten scenariusz przy użyciu .UNION ALL
Dodawanie sum w zapytaniu składającym
Szczególnym zastosowaniem zapytania składającego jest połączenie zestawu rekordów z jednym rekordem zawierającym sumę co najmniej jednego pola.
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.
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];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 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))Przełącz się na widok arkusza danych. Powinien być widoczny tylko jeden rekord:
MaxOfDate Received SumOfQuantity 4/5/2006 510 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];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. Warto także uwzględnić stałe wartości w obu zapytaniach, 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.
Praca z unikatowymi rekordami w zapytaniu składającym przy użyciu instrukcji UNION ALL
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 to zapytanie składające SQL , aby uwzględnić 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 |
Oczywiście jeden rekord nie zwraca dwukrotności całkowitej ilości.
Widać ten wynik, ponieważ pewnego dnia ta sama ilość czekoladek została sprzedana dwa razy, co w tabeli Szczegóły zamówienia zakupu. 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 wcześniej zanotowanej zapytaniu składającym widać, że pole Identyfikator zamówienia zakupu nie jest uwzględniane i że dwa pola nie tworzą dwóch unikatowych rekordów.
Jeśli chcesz uwzględnić wszystkie rekordy, użyj UNION ALL zamiast UNION w SQL. Najprawdopodobniej będzie to miało wpływ na sortowanie wyników, dlatego warto również dołączyć klauzulę ORDER BY w celu określenia kolejności sortowania. Oto modyfikacja SQL na podstawie 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 |
Filtrowanie rekordów w formularzu za pośrednictwem kontrolki pola kombi przy użyciu zapytania składającego
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.
Utwórz proste zapytanie wybierające przy użyciu następującej
SQLskładni:SELECT Employees.City, Employees.City AS Filter FROM Employees;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 Te wyniki mogą wydawać się niezbyt wartościowe. Rozwiń zapytanie i przekrój je w zapytanie składające, używając następujących funkcji
SQL:SELECT Employees.City, Employees.City AS Filter FROM Employees UNION SELECT "<All>", "*" AS Filter FROM Employees ORDER BY City;Przełącz się na widok arkusza danych. Powinny zostać wyświetlone następujące wyniki:
City Filter <Wszystkie> * Bellevue Bellevue Kirkland Kirkland Redmond Redmond Seattle Seattle Program Access wykonuje połączenie dziewięciu wcześniej wyświetlanych rekordów ze stałymi <wartościami pól Typu Wszystkie> i "*". Ponieważ ta klauzula składająca nie zawiera
UNION ALL, program Access zwraca tylko unikatowe rekordy. Oznacza to, że każde miasto jest zwracane tylko raz ze stałymi identycznymi wartościami.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.
FilterWe właściwości samego formularza możesz następnie dodać kod podobny do poniższego, aby aktywować filtr formularza, używając wartości wybranej w kontrolce pola kombi:Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'" Me.FilterOn = TrueUżytkownik formularza może następnie filtrować rekordy formularza pod określoną nazwą miasta lub wybrać pozycję <Wszystkie> , aby wyświetlić listę wszystkich rekordów dla wszystkich miast.