De resultaten van verschillende query's combineren met een samenvoegquery

Soms wilt u records van een 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, is het misschien handig om eerst een werkend voorbeeld in de Northwind-sjabloon van Access te bestuderen. U kunt zoeken naar de voorbeeldsjabloon Noordenwind op de pagina Aan de slag van Access door Nieuw bestand> teselecteren. U kunt ook rechtstreeks een kopie downloaden van de voorbeeldsjabloon Noordenwind.

Wanneer de Northwind-database is geopend in Access, sluit u het aanmeldingsdialoogvenster dat eerst wordt weergegeven en vouwt u het navigatiedeelvenster uit. Selecteer boven in het navigatiedeelvenster en selecteer vervolgens Objecttype om alle databaseobjecten op type te ordenen. Vouw vervolgens de groep Query's uit, waarna u de query Producttransacties ziet.

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. Dat betekent dat u de grafische ontwerpfunctie van Access niet kunt gebruiken om samenvoegquery's te maken of te bewerken. Als u een samenvoegquery opent vanuit het navigatiedeelvenster, wordt deze in Access geopend en worden de resultaten weergegeven in de gegevensbladweergave. U ziet onder Weergaven op het tabblad Start dat de ontwerpweergave niet beschikbaar is wanneer u met samenvoegquery's werkt. U kunt alleen schakelen tussen de gegevensbladweergave en de SQL-weergave.

Als u wilt verdergaan met dit voorbeeld van een samenvoegquery, klikt u op Start>SQL-weergave> om de SQL syntaxis te bekijken waarmee de samenvoegquery is gedefinieerd. In deze afbeelding hebben we wat extra afstand toegevoegd zodat SQL u de verschillende onderdelen van een samenvoegquery eenvoudig kunt bekijken.

Laten we de syntaxis van deze samenvoegquery in de SQL 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 wordt aangegeven dat deze twee recordsets moeten worden gecombineerd.

Het laatste gedeelte van deze SQL instructie bepaalt de volgorde van de gecombineerde records door middel van een ORDER BY instructie. In dit voorbeeld worden alle records in aflopende volgorde gesorteerd op het veld Orderdatum.

Een samenvoegquery maken door selectiequery's te maken en te combineren

Hoewel u een samenvoegquery kunt maken door de SQL syntaxis rechtstreeks in de SQL-weergave te schrijven, vindt u het misschien makkelijker om deze in delen te maken met een selectiequery. 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.

1. Klik op het tabblad Maken in de groep Query's op Queryontwerp.
2. Dubbelklik op de tabel met de velden die u wilt toevoegen. De tabel wordt toegevoegd aan het queryontwerpvenster.
3. 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.
4. Voeg desgewenst criteria aan de velden toe door de juiste expressies te typen in de rij Criteria van het veldraster.
5. 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.
6. Schakel over naar de ontwerpweergave.
7. Sla de selectiequery op en laat deze open.
8. Herhaal deze procedure voor elke selectiequery die u wilt combineren.

Nu u uw selectiequery 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.

1. Klik op het tabblad Maken in de groep Query's op Queryontwerp.
2. Klik op het tabblad Ontwerpen in de groep Query op Samenvoegen. Het queryontwerpvenster wordt verborgen en u ziet het objecttabblad van de SQL-weergave . In dit stadium is het tabblad leeg.
3. Klik op de tab voor de eerste selectiequery die u in de samenvoegquery wilt combineren.
4. Klik op het tabblad Start opSQL-weergaveweergeven>.
5. Kopieer de SQL instructie voor de selectiequery. Klik op de tab voor de samenvoegquery waarmee u eerder bent begonnen.
6. Plak de SQL instructie voor de selectiequery in het objecttabblad SQL-weergave van de samenvoegquery.
7. Verwijder de puntkomma (;) aan het eind van de instructie van de selectiequery SQL .
8. Druk op Enter om de cursor één regel omlaag te verplaatsen en typ UNION op de nieuwe regel.
9. Klik op de tab voor de volgende selectiequery die u in de samenvoegquery wilt combineren.
10. Herhaal stap 5 tot en met 10 totdat u alle SQL instructies voor de selectiequery's naar het SQL View-venster van de samenvoegquery hebt gekopieerd. Laat de puntkomma aan het eind van de instructie voor de laatste selectiequery staan en typ niets achter deze SQL puntkomma.
11. 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 ziet u een voorbeeld dat u kunt namaken in de Northwind-voorbeelddatabase. 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:

1. 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.

2. 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.

3. Kopieer en plak de SQL-instructies van Query1 en Query2 in Query3. Verwijder de extra puntkomma en voeg het UNION trefwoord toe. U kunt de resultaten nu controleren in de gegevensbladweergave.

4. Voeg een volgordecomponent toe aan een van de query's en plak de ORDER BY instructie in de samenvoegquery in 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.

5. Dit is de laatste SQL opdracht waarmee de namen voor dit samenvoegqueryvoorbeeld worden gecombineerd en gesorteerd:

   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 vertrouwd bent met het schrijven SQL van syntaxis, kunt u uw eigen SQL instructie voor de samenvoegquery rechtstreeks in de SQL-weergave 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 in 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 bekijkt in de gegevensbladweergave, worden alle werknemers weergegeven met de naam van het voorbeeldbedrijf, wat misschien niet erg handig is. Als u in dit veld wilt aangeven of een persoon een interne werknemer, een werknemer van een leverancier of een werknemer van een klant is, kunt u een vaste waarde opnemen in plaats van de bedrijfsnaam. Dit ziet er als volgt SQL 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 beperken omdat in Access alleen de namen van de uitvoervelden uit de eerste query in een samenvoegquery worden gelezen. Hier worden de uitvoer van de tweede en derde querysecties 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];

Samenvoegquery's filteren

In een samenvoegquery van Access kunt u slechts één keer de volgorde instellen, maar u kunt elke query afzonderlijk filteren. U ziet hier een voorbeeld waarin elke query wordt gefilterd door een WHERE component toe te voegen op basis van de samenvoegquery van de vorige sectie.

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 nogal verschillen, kan het gebeuren dat er in een uitvoerveld gegevens met verschillende gegevenstypen moeten worden gecombineerd. 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

Laten we aannemen dat u het veld Hoeveelheid wilt splitsen in twee velden: Kopen en Verkopen. We gaan er ook vanuit dat u een vaste nulwaarde wilt voor het veld zonder waarde. Deze samenvoegquery ziet er als volgt SQL uit:

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

Maar als u in dit voorbeeld nu wilt dat de velden met nulwaarden leeg zijn? U kunt de SQL optie zo wijzigen dat er niets wordt weergegeven in plaats van een nul door het Null trefwoord toe te voegen, zoals hier wordt getoond:

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 wilt invoegen voor de lege waarde van de velden? Het SQL kan er voor deze poging 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 dat doen met deze expressie:

IIf(False, 0, Null)

De voorwaarde die moet worden gecontroleerd, False, is nooit True, dus de expressie geeft altijd als resultaat Null. In Access worden beide uitvoeropties echter nog steeds geëvalueerd en wordt de uitvoer als numerieke 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 worden vaste waarden geretourneerd van het gegevenstype dat u definieert. U wilt natuurlijk niet dat de uitvoer van deze query van invloed is op de resultaten. Dit kunt u voorkomen door een WHERE-component met de optie Onwaar op te nemen:

WHERE False

Dit is een kleine truc. 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;

Totalen toevoegen in een samenvoegquery

Een samenvoegquery wordt met name gebruikt 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.

1. 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];
   

2. 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

3. 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))
   

4. Ga naar de gegevensbladweergave, waar u slechts één aankoop moet zien:

   MaxOfDate ontvangen	SumOfQuantity
   5-4-2006	510

5. 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];
   

6. 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. U wilt mogelijk ook vaste waarden opnemen in beide query's, zoals Details en Totaal om de record met totalen visueel te onderscheiden 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 de 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

Eén record retourneert natuurlijk niet twee keer de totale hoeveelheid.

U ziet dit resultaat omdat op één dag dezelfde hoeveelheid bonbons twee keer is verkocht, zoals is vastgelegd in de tabel Inkooporderdetails. 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 samenvoegquery van eerder ziet u dat het veld Inkooporder-id niet is opgenomen en dat de twee velden niet twee unieke records zijn.

Als u alle records wilt opnemen, gebruikt UNION ALL u in plaats van UNION in uw SQL Dit is waarschijnlijk van invloed op de sortering van de resultaten, dus is het handig om ook een ORDER BY component op te nemen om de sorteervolgorde op te geven. Dit is de gewijzigde SQL versie 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.

1. Gebruik de volgende SQL syntaxis om een eenvoudige selectiequery te maken:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;
   

2. 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

3. Als u naar deze resultaten kijkt, ziet u niet veel waarden. Breid de query uit en verander deze in een samenvoegquery 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;
   

4. Ga naar de gegevensbladweergave, waar u de volgende resultaten ziet:

   Plaats	Filter
   <Alles>	*
   Alkmaar	Alkmaar
   Haarlem	Haarlem
   Den Helder	Den Helder
   Zwolle	Zwolle

   In Access wordt een samenvoeging uitgevoerd van de negen records die eerder zijn weergegeven, met vaste veldwaarden van <Alle> en '*'. Omdat deze samenvoegcomponent niet bevat UNION ALL, worden alleen unieke records geretourneerd. Dit betekent dat elke plaats slechts één keer met vaste identieke waarden als resultaat wordt gegeven.

5. 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 Filter eigenschap 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 = True
   

   De gebruiker van het formulier kan vervolgens de formulierrecords filteren op een specifieke plaatsnaam of Alles <> selecteren om alle records voor alle plaatsen weer te geven.

Naar boven