De resultaten van verschillende query's combineren met een samenvoegquery

In het voorbeeld van de vorige sectie met de Northwind-database 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 met 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. De SQL ziet er dan 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:

De bovenstaande query kan nog verder worden beperkt omdat in Access alleen de namen van de uitvoervelden in de eerste query in een samenvoegquery worden gelezen. U ziet hier dat de uitvoer uit de tweede en derde querysecties is 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];

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 op basis van de samenvoegquery van de vorige sectie waarbij elke query is gefilterd door een WHERE-component toe te 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:

Als de query's die u wilt samenvoegen, 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:

Stel 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. De SQL 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:

Maar als u in dit voorbeeld nu wilt dat de velden met een nul leeg zijn? U kunt de SQL zo aanpassen dat er niets wordt weergegeven in plaats van een nul door het trefwoord Null als volgt toe te voegen:

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:

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 velden? De SQL hiervoor 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.

Hoe lost u deze puzzel op?

Eén oplossing is om af te dwingen dat de query verwacht dat de veldwaarde een getal is. Dit kunt u doen met de expressie:

IIf(False, 0, Null)

De voorwaarde die moet worden gecontroleerd, Onwaar, is nooit Waar, dus wordt met de expressie altijd Null geretourneerd, maar in Access worden beide uitvoeropties nog steeds geëvalueerd en wordt bepaald of de uitvoer numeriek of Null is.

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:

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 trucje omdat dit altijd onwaar is en er met de query zo niets wordt geretourneerd. 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;

Een speciaal gebruik van een samenvoegquery is het combineren van een set records 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.

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 SQL van deze samenvoegquery om Product-id = 48 op te nemen:

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:

Met één record wordt natuurlijk niet twee keer de hoeveelheid bij totaal geretourneerd.

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:

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 u UNION ALL 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 aangepaste 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:

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. Maak een eenvoudige selectiequery met deze SQL-syntaxis:

   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 transformeer deze in een samenvoegquery met behulp van de 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
   <Alle>	*
   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 UNION ALL bevat, worden alleen unieke records geretourneerd. Dit betekent dat elke plaats slechts één keer wordt geretourneerd met vaste identieke waarden.

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 eigenschap Filter van het formulier zelf kunt u vervolgens code zoals de volgende toevoegen om een formulierfilter te activeren met de waarde van het geselecteerde item in de keuzelijst met invoervak:

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

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

Naar boven