Soms wilt u records uit één tabel of query combineren met records uit een of meer andere tabellen tot één resultaat. Dat is wat een samenvoegquery doet in Access.
Om samenvoegquery's goed te kunnen begrijpen, moet u eerst vertrouwd zijn met het ontwerpen van eenvoudige selectiequery's in Access. Zie Een eenvoudige selectiequery maken voor meer informatie over het ontwerpen van selectiequery's.
Een voorbeeld van een werkende samenvoegquery bekijken
Als u nog nooit eerder een samenvoegquery hebt gemaakt, kan het handig zijn om eerst een werkend voorbeeld te bestuderen in de sjabloon Northwind Access. U kunt zoeken naar de voorbeeldsjabloon Northwind op de pagina Aan de slag van Access door Nieuw bestand> teselecteren. U kunt een kopie ook rechtstreeks downloaden vanuit de Northwind-voorbeeldsjabloon.
Nadat access de database Northwind heeft geopend, sluit u het aanmeldingsdialoogvenster dat voor het eerst wordt weergegeven en vouwt u vervolgens het navigatiedeelvenster uit. Selecteer de bovenkant van het navigatiedeelvenster en selecteer vervolgens Objecttype om alle databaseobjecten op type te ordenen. Vouw vervolgens de groep Query's uit en u ziet een query met de naam Producttransacties.
Samenvoegquery's zijn gemakkelijk te onderscheiden van andere queryobjecten omdat deze een speciaal pictogram hebben die lijkt op twee verweven cirkels waarmee een samengevoegde set van twee groepen wordt aangegeven:
In tegenstelling tot normale selectie- en actiequery's zijn tabellen niet gerelateerd in een samenvoegquery. Dit betekent dat u de ontwerpfunctie voor grafische query's van Access niet kunt gebruiken om samenvoegquery's te maken of te bewerken. Als u een samenvoegquery opent vanuit het navigatiedeelvenster, wordt deze geopend en worden de resultaten weergegeven in de gegevensbladweergave. Onder Weergaven op het tabblad Start ziet u dat de ontwerpweergave niet beschikbaar is wanneer u met samenvoegquery's werkt. U kunt alleen schakelen tussen gegevensbladweergave en SQL-weergave.
Als u wilt doorgaan met het bestuderen van dit samenvoegqueryvoorbeeld, klikt u opStartweergaven>>SQL-weergaven om de SQL syntaxis weer te geven waarmee deze wordt gedefinieerd. In deze afbeelding hebben we extra afstand toegevoegd aan de SQL , zodat u eenvoudig de verschillende onderdelen kunt zien waaruit een samenvoegquery bestaat.
Laten we de SQL syntaxis van deze samenvoegquery uit de Northwind-database in detail bekijken:
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;
Het eerste en derde gedeelte van deze SQL-instructie zijn eigenlijk twee selectiequery's. Met deze query's worden twee verschillende recordsets opgehaald: één uit de tabel Productorders en één uit de tabel Productinkoop.
Het tweede deel van deze SQL instructie is het UNION trefwoord, waarmee Access deze twee recordsets moet combineren.
Het laatste deel van deze SQL instructie bepaalt de volgorde van de gecombineerde records met behulp van een ORDER BY -instructie. In dit voorbeeld worden alle records in aflopende volgorde gerangschikt op het veld Orderdatum.
Opmerking
Samenvoegquery's zijn altijd alleen-lezen in Access. U kunt geen waarden wijzigen in de gegevensbladweergave.
Een samenvoegquery maken door selectiequery's te maken en te combineren
Hoewel u een samenvoegquery kunt maken door de syntaxis rechtstreeks in de SQLSQL-weergave te schrijven, kunt u deze mogelijk gemakkelijker in delen bouwen met geselecteerde query's. U kunt de SQL-delen vervolgens kopiëren en in een gecombineerde samenvoegquery plakken.
Zie de volgende sectie Een voorbeeld van het maken van een samenvoegquery bekijken als u niet verder wilt lezen en in plaats hiervan een voorbeeld wilt bekijken.
- Klik op het tabblad Maken in de groep Query's op Queryontwerp.
- Dubbelklik op de tabel met de velden die u wilt opnemen. De tabel wordt toegevoegd aan het queryontwerpvenster.
- Dubbelklik in het queryontwerpvenster op elk veld dat u wilt toevoegen. Zorg er bij het selecteren van de velden voor dat u evenveel velden toevoegt als aan de andere selectiequery's en dat u ze in dezelfde volgorde toevoegt. Let op dat de gegevenstypen van de velden compatibel zijn met de gegevenstypen van velden in dezelfde positie in de andere query's die u combineert. Als uw eerste selectiequery bijvoorbeeld vijf velden heeft en het eerste veld gegevens van het type Datum/tijd bevat, moet u ervoor zorgen dat alle andere selectiequery's die u combineert, ook vijf velden hebben, met als eerste een veld van het type Datum/tijd, enzovoort.
- Voeg desgewenst criteria aan de velden toe door de juiste expressies te typen in de rij Criteria van het veldraster.
- Wanneer u klaar bent met het toevoegen van velden en veldcriteria, voert u de selectiequery uit en controleert u de queryresultaten. Klik op het tabblad Ontwerpen in de groep Resultaten op itvoeren.
- Schakel over naar de ontwerpweergave.
- Sla de selectiequery op en laat deze open.
- Herhaal deze procedure voor elke selectiequery die u wilt combineren.
Nu u uw selectiequery's hebt gemaakt, is het tijd om deze te combineren. In deze stap maakt u de samenvoegquery door de SQL instructies te kopiëren en te plakken.
- Klik op het tabblad Maken in de groep Query's op Queryontwerp.
- Klik op het tabblad Ontwerp in de groep Query op Samenvoeging. Het queryontwerpvenster wordt verborgen en het objecttabblad SQL View weergegeven. Op dit moment is het tabblad leeg.
- Klik op de tab voor de eerste selectiequery die u in de samenvoegquery wilt combineren.
- Klik op het tabblad Start op >SQL-weergave weergeven.
- Kopieer de
SQLinstructie voor de selectiequery. Klik op de tab voor de samenvoegquery waarmee u eerder bent begonnen. - Plak de
SQLinstructie voor de selectiequery in het objecttabblad SQL View van de samenvoegquery. - Verwijder de puntkomma (
;) aan het einde van de instructie selectquerySQL. - Druk op Enter om de cursor één regel omlaag te verplaatsen en typ
UNIONop de nieuwe regel. - Klik op de tab voor de volgende selectiequery die u in de samenvoegquery wilt combineren.
- Herhaal stap 5 tot en met 10 totdat u alle instructies voor de
SQLselectiequery's hebt gekopieerd en geplakt in het VENSTER SQL-weergave van de samenvoegquery. Verwijder de puntkomma niet en typ niets na deSQLinstructie voor de laatste selectiequery. - Ga naar het tabblad Ontwerp en klik in de groep Resultaten op Uitvoeren.
De resultaten van uw samenvoegquery worden weergegeven in de gegevensbladweergave.
Een voorbeeld van het maken van een samenvoegquery bekijken
Hier volgt een voorbeeld dat u opnieuw kunt maken in de voorbeelddatabase Northwind. Met deze samenvoegquery worden de namen van personen uit de tabel Klanten verzameld en worden deze gecombineerd met de namen van personen uit de tabel Leveranciers. Als u dit ook wilt doen, voert u deze stappen uit in uw exemplaar van de Northwind-voorbeelddatabase.
Dit zijn de stappen voor het maken van dit voorbeeld:
Maak twee selectiequery's met de naam Query1 en Query2 met als gegevensbron respectievelijk de tabellen Klanten en Leveranciers. Gebruik de velden Voornaam en Achternaam als weergavewaarden.
Maak een nieuwe query met de naam Query3 in eerste instantie zonder gegevensbron en klik op de opdracht Samenvoegquery op het tabblad Ontwerp om van deze query een samenvoegquery te maken.
Kopieer en plak de SQL-instructies van Query1 en Query2 in Query3. Zorg ervoor dat u de extra puntkomma verwijdert en het
UNIONtrefwoord toevoegt. U kunt de resultaten nu controleren in de gegevensbladweergave.Voeg een bestelcomponent toe aan een van de query's en plak de
ORDER BYinstructie vervolgens in de samenvoegquery in de SQL-weergave. Wanneer de volgordecomponent wordt toegevoegd, ziet u dat in Query3, de samenvoegquery, eerst de puntkomma's worden verwijderd en daarna de tabelnaam uit de veldnamen wordt verwijderd.De laatste
SQLwaarin de namen voor dit samenvoegqueryvoorbeeld worden gecombineerd en gesorteerd, is het volgende: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];
Als u erg vertrouwd bent met het schrijven SQL van syntaxis, kunt u uw eigen SQL instructie voor de samenvoegquery rechtstreeks in SQL View schrijven. Het kan echter handig zijn om SQL van andere queryobjecten te kopiëren en te plakken. Elke query kan veel ingewikkelder zijn dan de voorbeelden van de eenvoudige selectiequery's die hier worden gebruikt. Het is nuttig om elke query te maken en uitgebreid te testen voordat u deze combineert in de samenvoegquery. Als de samenvoegquery niet wordt uitgevoerd, kunt u elke query afzonderlijk aanpassen totdat deze wel wordt uitgevoerd en uw samenvoegquery vervolgens opnieuw maken met de gecorrigeerde syntaxis.
Bekijk de resterende secties van dit artikel voor meer tips en trucs voor het gebruik van samenvoegquery's.
Drie of meer tabellen of query's in een samenvoegquery combineren
In het voorbeeld uit de vorige sectie waarin de northwind-database wordt gebruikt, worden gegevens uit slechts twee tabellen gecombineerd. U kunt echter eenvoudig drie of meer tabellen combineren in een samenvoegquery. Als we verder gaan met het vorige voorbeeld kunnen we bijvoorbeeld ook de namen van de werknemers in de queryuitvoer opnemen. U kunt dit doen door een derde query toe te voegen en deze te combineren met de vorige SQL-instructie met een extra UNION-trefwoord, zoals u hier ziet:
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];
Wanneer u het resultaat in de gegevensbladweergave weergeeft, worden alle werknemers weergegeven met de voorbeeldnaam van het bedrijf, wat waarschijnlijk niet erg handig is. Als u wilt dat dit veld laat zien of een persoon een interne werknemer is, van een leverancier of van een klant, kunt u een vaste waarde opnemen in plaats van de bedrijfsnaam.
SQL De ziet er als volgt uit:
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];
Het resultaat ziet er zo uit in de gegevensbladweergave. In Access worden deze vijf voorbeeldrecords weergegeven:
| Dienstverband | Achternaam | Voornaam |
|---|---|---|
| Intern | Plant | Fenna |
| Intern | Gunther | Elsemiek |
| Leverancier | Spel | Stefan |
| Klant | Kregel | Floris |
| Klant | Neumann | Roelf |
U kunt de query nog verder verkleinen omdat de namen van de uitvoervelden alleen worden gelezen uit de eerste query in een samenvoegquery. Hier wordt de uitvoer van de tweede en derde querysectie verwijderd:
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];
Filteren in samenvoegquery's
In een Access-samenvoegquery is ordenen slechts één keer toegestaan, maar u kunt elke query afzonderlijk filteren. Voortbouwend op de samenvoegquery van de vorige sectie, volgt hier een voorbeeld waarmee elke query wordt gefilterd door een component toe te WHERE voegen.
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];
Als u naar de gegevensbladweergave gaat, ziet u deze resultaten:
| Dienstverband | Achternaam | Voornaam |
|---|---|---|
| Leverancier | Vanderstar | Lynn |
| Intern | Plant | Fenna |
| Klant | Schoonen | Arnaud |
| Intern | Roossien | Johanna |
| Leverancier | Rigter | Elize |
| Klant | Spel | Joep |
| Leverancier | Scholts | Pieter-Bas |
| Leverancier | Jobse | Enver |
| Intern | de Graaf | Jimmy |
| Leverancier | Zuidwijk | Jacobje |
| Intern | Veenhuizen | Ger |
Gegevenstypen combineren
Als de query's die u samenvoegt, zeer verschillend zijn, kan er een situatie optreden waarin een uitvoerveld gegevens van verschillende gegevenstypen moet combineren. Als dit het geval is, worden de resultaten met de samenvoegquery meestal geretourneerd als tekstgegevenstype omdat dit gegevenstype zoals tekst als getallen kan bevatten.
Om inzicht te krijgen in hoe dit werkt, gebruiken we de samenvoegquery Producttransacties in de Northwind-voorbeelddatabase. Open deze voorbeelddatabase en open de query Producttransacties in de gegevensbladweergave. De laatste tien records moeten ongeveer gelijk zijn aan deze uitvoer:
| Product-id | Orderdatum | Bedrijfsnaam | Transactie | Hoeveelheid |
|---|---|---|---|---|
| 77 | 22-1-2006 | Leverancier B | Aankoop | 60 |
| 80 | 22-1-2006 | Leverancier D | Aankoop | 75 |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 125 |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 200 |
| 7 | 20-1-2006 | Bedrijf D | Verkoop | 10 |
| 51 | 20-1-2006 | Bedrijf D | Verkoop | 10 |
| 80 | 20-1-2006 | Bedrijf D | Verkoop | 10 |
| 34 | 15-1-2006 | Bedrijf AA | Verkoop | 100 |
| 80 | 15-1-2006 | Bedrijf AA | Verkoop | 30 |
Stel dat u het veld Hoeveelheid wilt splitsen in twee velden: Kopen en Verkopen. We gaan er ook van uit dat u een vaste nulwaarde wilt voor het veld zonder waarde.
SQL De ziet er als volgt uit voor deze samenvoegquery:
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;
Als u naar de gegevensbladweergave gaat, ziet u dat de laatste tien records nu als volgt worden weergegeven:
| Product-id | Orderdatum | Bedrijfsnaam | Transactie | Kopen | Verkopen |
|---|---|---|---|---|---|
| 74 | 22-1-2006 | Leverancier B | Aankoop | 20 | 0 |
| 77 | 22-1-2006 | Leverancier B | Aankoop | 60 | 0 |
| 80 | 22-1-2006 | Leverancier D | Aankoop | 75 | 0 |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 125 | 0 |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 200 | 0 |
| 7 | 20-1-2006 | Bedrijf D | Verkoop | 0 | 10 |
| 51 | 20-1-2006 | Bedrijf D | Verkoop | 0 | 10 |
| 80 | 20-1-2006 | Bedrijf D | Verkoop | 0 | 10 |
| 34 | 15-1-2006 | Bedrijf AA | Verkoop | 0 | 100 |
| 80 | 15-1-2006 | Bedrijf AA | Verkoop | 0 | 30 |
Wat gebeurt er als u wilt dat de velden met nulwaarden leeg zijn? U kunt de SQL wijzigen om niets weer te geven in plaats van nul door het Null trefwoord toe te voegen, zoals hier wordt weergegeven:
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;
Maar zoals u misschien hebt gezien toen u naar de gegevensbladweergave overschakelde, hebt u nu een onverwacht resultaat. In de kolom Kopen is elk veld gewist:
| Product-id | Orderdatum | Bedrijfsnaam | Transactie | Kopen | Verkopen |
|---|---|---|---|---|---|
| 74 | 22-1-2006 | Leverancier B | Aankoop | ||
| 77 | 22-1-2006 | Leverancier B | Aankoop | ||
| 80 | 22-1-2006 | Leverancier D | Aankoop | ||
| 81 | 22-1-2006 | Leverancier A | Aankoop | ||
| 81 | 22-1-2006 | Leverancier A | Aankoop | ||
| 7 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 51 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 80 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 34 | 15-1-2006 | Bedrijf AA | Verkoop | 100 | |
| 80 | 15-1-2006 | Bedrijf AA | Verkoop | 30 |
Dit komt omdat de gegevenstypen van de velden uit de eerste query door Access worden vastgesteld. In dit voorbeeld is Null geen getal.
Wat gebeurt er als u een lege tekenreeks voor de lege waarde van de velden probeert in te voegen? De SQL voor deze poging kan er als volgt uitzien:
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;
Wanneer u naar de gegevensbladweergave gaat, ziet u dat in Access de waarden voor Kopen worden opgehaald, maar dat de waarden worden geconverteerd naar tekst. U kunt zien dat dit tekstwaarden zijn, omdat ze links zijn uitgelijnd in de gegevensbladweergave. De lege tekenreeks in de eerste query is geen getal en daarom ziet u deze resultaten. U ziet ook dat de waarden voor Verkopen worden geconverteerd naar tekst omdat de aankooprecords een lege tekenreeks bevatten.
| Product-id | Orderdatum | Bedrijfsnaam | Transactie | Kopen | Verkopen |
|---|---|---|---|---|---|
| 74 | 22-1-2006 | Leverancier B | Aankoop | 20 | |
| 77 | 22-1-2006 | Leverancier B | Aankoop | 60 | |
| 80 | 22-1-2006 | Leverancier D | Aankoop | 75 | |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 125 | |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 200 | |
| 7 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 51 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 80 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 34 | 15-1-2006 | Bedrijf AA | Verkoop | 100 | |
| 80 | 15-1-2006 | Bedrijf AA | Verkoop | 30 |
Hoe lost u deze puzzel op?
Eén oplossing is om af te dwingen dat de query verwacht dat de veldwaarde een getal is. U kunt dit doen met deze expressie:
IIf(False, 0, Null)
De voorwaarde om te controleren, False, is nooit True, dus de expressie retourneert Nullaltijd . Access evalueert echter nog steeds beide uitvoeropties en behandelt de uitvoer als numeriek of Null.
Hier ziet u hoe we deze expressie kunnen gebruiken in ons voorbeeld:
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;
U hoeft de tweede query niet te wijzigen.
Als u naar de gegevensbladweergave overschakelt, ziet u nu het gewenste resultaat:
| Product-id | Orderdatum | Bedrijfsnaam | Transactie | Kopen | Verkopen |
|---|---|---|---|---|---|
| 74 | 22-1-2006 | Leverancier B | Aankoop | 20 | |
| 77 | 22-1-2006 | Leverancier B | Aankoop | 60 | |
| 80 | 22-1-2006 | Leverancier D | Aankoop | 75 | |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 125 | |
| 81 | 22-1-2006 | Leverancier A | Aankoop | 200 | |
| 7 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 51 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 80 | 20-1-2006 | Bedrijf D | Verkoop | 10 | |
| 34 | 15-1-2006 | Bedrijf AA | Verkoop | 100 | |
| 80 | 15-1-2006 | Bedrijf AA | Verkoop | 30 |
U kunt hetzelfde resultaat behalen door de query's in de samenvoegquery vooraf te laten gaan door weer een andere query:
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
Voor elk veld retourneert Access vaste waarden van het gegevenstype dat u definieert. Natuurlijk wilt u niet dat de uitvoer van deze query de resultaten verstoort, dus de truc om dat te voorkomen is om een WHERE-component op Onwaar op te nemen:
WHERE False
Dit is een klein trucje. Omdat de voorwaarde altijd onwaar is, retourneert de query niets. Als u deze instructie combineert met de bestaande SQL, ziet de volledige instructie er als volgt uit:
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;
Opmerking
In dit voorbeeld retourneert de gecombineerde query in de northwind-database 100 records, terwijl de twee afzonderlijke query's 58 en 43 records retourneren voor een totaal van 101 records. Dit verschil treedt op omdat twee records niet uniek zijn. Zie Werken met afzonderlijke records in samenvoegquery's met behulp van UNION ALL voor meer informatie over het oplossen van dit scenario met behulp van UNION ALL.
Totalen toevoegen in een samenvoegquery
Een speciaal gebruik voor een samenvoegquery is om een set records te combineren met één record die de som van een of meer velden bevat.
Hier is nog een voorbeeld dat u in de Northwind-voorbeelddatabase kunt maken om te zien hoe u een totaal kunt verkrijgen in een samenvoegquery.
Maak een nieuwe eenvoudige query om de aanschaf van bier (Product-id = 34 in de Northwind-database) te bekijken met de volgende SQL-syntaxis:
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];Ga naar de gegevensbladweergave, waar u vier aankopen moet zien:
Datum ontvangen Hoeveelheid 22-1-2006 100 22-1-2006 60 4-4-2006 50 5-4-2006 300 Om het totaal te verkrijgen, maakt u een eenvoudige statistische query met de volgende SQL:
SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Product ID])=34))Ga naar de gegevensbladweergave, waar u slechts één aankoop moet zien:
MaxOfDate ontvangen SumOfQuantity 5-4-2006 510 Combineer deze twee query's tot een samenvoegquery om de record met de totale hoeveelheid toe te voegen aan de aankooprecords:
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];Ga naar de gegevensbladweergave, waar u de vier aankopen ziet met de som van elke aankoop, gevolgd door een record met de som van het aantal:
Datum ontvangen Hoeveelheid 22-1-2006 60 22-1-2006 100 4-4-2006 50 5-4-2006 300 5-4-2006 510
Dit zijn de basisbeginselen van het toevoegen van totalen aan een samenvoegquery. Mogelijk wilt u ook vaste waarden opnemen in beide query's, zoals 'Detail' en 'Totaal' om de totale record visueel te scheiden van de andere records. Informatie over het gebruik van vaste waarden vindt u in de sectie Drie of meer tabellen of query's in een samenvoegquery combineren.
Werken met unieke records in samenvoegquery's met UNION ALL
Samenvoegquery's in Access bevatten standaard alleen unieke records. Maar wat moet u doen als u alle records wilt opnemen? Dat kunt u zien in dit voorbeeld.
In de vorige sectie hebt u gezien hoe u een totaal kunt maken in een samenvoegquery. Wijzig die samenvoegquery SQL om het volgende op te nemen 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];
Ga naar de gegevensbladweergave, waar u een enigszins misleidend resultaat ziet:
| Datum ontvangen | Hoeveelheid |
|---|---|
| 22-1-2006 | 100 |
| 22-1-2006 | 200 |
Natuurlijk retourneert één record niet tweemaal de totale hoeveelheid.
U ziet dit resultaat omdat op één dag dezelfde hoeveelheid chocolaatjes tweemaal is verkocht, zoals vermeld in de tabel Details van inkooporder. Dit is een eenvoudig selectiequeryresultaat waarin beide records in de Northwind-voorbeelddatabase worden weergegeven:
| Inkooporder-id | Product | Hoeveelheid |
|---|---|---|
| 100 | Northwind Traders Chocolate | 100 |
| 92 | Northwind Traders Chocolate | 100 |
In de eerder genoteerde samenvoegquery ziet u dat het veld Inkooporder-id niet is opgenomen en dat de twee velden geen twee afzonderlijke records vormen.
Als u alle records wilt opnemen, gebruikt UNION ALL u in plaats van UNION in uw SQL. Dit heeft waarschijnlijk invloed op de sortering van de resultaten, dus misschien wilt u ook een ORDER BY component opnemen om een sorteervolgorde te bepalen. Dit is de wijziging SQL op basis van het vorige voorbeeld:
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];
Als u naar de gegevensbladweergave gaat, ziet u alle details naast een totaal als laatste record:
| Datum ontvangen | Totaal | Hoeveelheid |
|---|---|---|
| 22-1-2006 | 100 | |
| 22-1-2006 | 100 | |
| 22-1-2006 | Totaal | 200 |
Gebruik een samenvoegquery om records te filteren in een formulier in een keuzelijst met invoervak
Een samenvoegquery wordt vaak gebruikt als recordbron voor een keuzelijst met invoervak in een formulier. U kunt deze keuzelijst met invoervak gebruiken om een waarde te selecteren waarop u de records in het formulier wilt filteren. U kunt bijvoorbeeld de werknemerrecords filteren op plaats.
U kunt zien hoe dit werkt in een ander voorbeeld dat u in de Northwind-voorbeelddatabase kunt maken om dit scenario te bekijken.
Maak een eenvoudige selectiequery met behulp van deze
SQLsyntaxis:SELECT Employees.City, Employees.City AS Filter FROM Employees;Ga naar de gegevensbladweergave, waar u de volgende resultaten ziet:
Plaats Filter Zwolle Amsterdam Alkmaar Alkmaar Den Helder Den Helder Haarlem Haarlem Zwolle Zwolle Den Helder Den Helder Zwolle Zwolle Den Helder Den Helder Zwolle Zwolle Als u naar deze resultaten kijkt, ziet u niet veel waarden. Vouw de query echter uit en maak er een samenvoegquery van met behulp van het volgende
SQL:SELECT Employees.City, Employees.City AS Filter FROM Employees UNION SELECT "<All>", "*" AS Filter FROM Employees ORDER BY City;Ga naar de gegevensbladweergave, waar u de volgende resultaten ziet:
Plaats Filter <Alles> * Alkmaar Alkmaar Haarlem Haarlem Den Helder Den Helder Zwolle Zwolle Access voert een samenvoeging uit van de negen records, die eerder werden weergegeven, met vaste veldwaarden van <Alle> en *. Omdat deze samenvoegingsclausule geen bevat
UNION ALL, retourneert Access alleen afzonderlijke records. Dit betekent dat elke plaats slechts eenmaal wordt geretourneerd met vaste identieke waarden.Nu u een voltooide samenvoegquery hebt waarmee elke plaatsnaam slechts één keer wordt weergegeven, samen met een optie waarmee alle plaatsen worden geselecteerd, kunt u deze query gebruiken als recordbron voor een keuzelijst met invoervak in een formulier. Als u dit specifieke voorbeeld gebruikt als model, kunt u een keuzelijst met invoervak maken in een formulier, deze query instellen als de recordbron, de eigenschap Kolombreedte van de kolom Filter instellen op 0 (nul) om deze te verbergen en de eigenschap Afhankelijke kolom instellen op 1 om de index van de tweede kolom aan te geven. In de
Filtereigenschap van het formulier zelf kunt u vervolgens code zoals de volgende toevoegen om een formulierfilter te activeren met behulp van de waarde die is geselecteerd in het besturingselement keuzelijst met invoervak:Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'" Me.FilterOn = TrueDe gebruiker van het formulier kan vervolgens de formulierrecords filteren op een specifieke plaatsnaam of Alles> selecteren <om alle records voor alle steden weer te geven.
Naar boven