Använd en unionsfråga för att kombinera flera frågor till ett resultat

I exemplet i det föregående avsnittet med Northwind-databasen kombineras bara data från två tabeller. Du kan dock enkelt kombinera tre eller fler tabeller i en unionsfråga. Till exempel kanske du vill bygga vidare på föregående exempel och även inkludera namnen på de anställda i frågeresultatet. Du kan utföra uppgiften genom att lägga till en tredje fråga och kombinera med det föregående SQL-uttrycket med ett ytterligare UNION-nyckelord så här:

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

När du visar resultatet i databladsvyn visas alla anställda med exempelföretagsnamnet, vilket förmodligen inte är så användbart. Om du vill att fältet ska ange om en person är en intern anställd, från en leverantör eller från en kund kan du inkludera ett fast värde i stället för företagsnamnet. Så här bör SQL-syntaxen se ut:

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

Så här visas resultatet i vyn Datablad. Access visar följande fem exempelposter:

Frågan ovan kan reduceras ytterligare eftersom Access bara läser namnen i utdatafälten för den första frågan i en unionsfråga. Här ser du att vi har tagit bort utdata från det andra och tredje frågeavsnittet:

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

Ordningen kan endast ändras en gång i en unionsfråga i Access men varje fråga kan filtreras individuellt. Med utgångspunkt i föregående avsnitts unionsfråga visas här är ett exempel där vi har filtrerat varje fråga genom att lägga till en WHERE-sats.

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

Växla till databladsvyn så visas resultatet ungefär så här:

Om frågorna som ska samordnas till unionsfrågor är mycket olika kan det uppstå en situation där ett utdatafält måste kombinera data av olika datatyper. Om så är fallet returnerar unionsfrågan oftast resultaten som en textdatatyp eftersom den datatypen kan innehålla både text och tal.

För att visa hur detta fungerar använder vi unionsfrågan Produkttransaktioner i Northwind-exempeldatabasen. Öppna exempeldatabasen och öppna sedan frågan Produkttransaktioner i databladsvyn. De sista tio posterna bör se ut ungefär så här:

Vi antar att du vill dela fältet Antal i två – Köp och Försäljning. Låt oss också anta att du vill ha ett fast nollvärde för fältet utan något värde. Så här kommer SQL-syntaxen att se ut för den här unionsfrågan:

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; 

Om du växlar till databladsvyn visas de sista tio posterna så här:

Om du fortsätter med det här exemplet, vad händer om du vill att fälten med noll ska vara tomma? Du kan ändra SQL så att ingenting visas i stället för noll genom att lägga till nyckelordet Null så här:

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;

Men som du kanske har sett när du växlar till databladsvyn har du nu fått ett oväntat resultat. Alla fält är tomma i kolumnen Köp:

Orsaken till att detta händer är att Access avgör datatyperna för fält utifrån den första frågan. I det här exemplet är Null inte ett tal.

Vad händer om du försöker infoga en tom sträng för det tomma värdet i fälten? SQL för det här försöket kan se ut så här:

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;

När du växlar till databladsvyn ser du att Access hämtar Köp-värden, men värdena konverteras till text. Du ser att dessa är textvärden eftersom de är vänsterjusterade i databladsvyn. Den tomma strängen i första frågan är inte ett tal, det är därför du får det här resultatet. Du ser också att värdena för Försäljning också konverterats till text eftersom köpposterna innehåller en tom sträng.

Hur löser du problemet?

En lösning är att tvinga frågan att förvänta att fältvärdet ska vara ett tal. Det kan uppnås med uttrycket:

IIf(False, 0, Null)

Villkoret att kontrollera, Falskt, kommer aldrig att bli Sant, därav returnerar uttrycket alltid Null, men Access utvärderar ändå båda utdataalternativen och bestämmer om resultatet ska vara numeriskt eller Null.

Så här kan vi använda det här uttrycket i vårt arbetsexempel:

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;

Observera att det inte är nödvändigt att ändra den andra frågan.

Om du växlar till databladsvyn ser du nu ett resultat som vi vill ha:

Ett annat sätt att uppnå samma resultat är att infoga frågorna i unionsfrågan med en annan fråga:

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

För varje fält returnerar Access fasta värden för den datatyp du definierar. Naturligtvis vill du inte att resultatet av den här frågan ska störa resultatet så tricket för att undvika det är att inkludera en WHERE-sats till Falskt:

WHERE False

Det är ett trick eftersom det alltid är falskt och då returnerar frågan ingenting. Om vi kombinerar det här uttrycket med befintlig SQL får vi ett slutligt uttryck enligt följande:

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;

Ett specialfall för en unionsfråga är att kombinera en uppsättning poster med en post som innehåller summan av ett eller flera fält.

Här är ett annat exempel som du kan skapa i Northwind-exempeldatabasen för att illustrera hur du får en totalsumma i en unionsfråga.

1. Skapa en ny enkel fråga för att visa köp av öl (Produkt-ID=34 i Northwind-databasen) med hjälp av följande SQL-syntax:

   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. När du växlar till databladsvyn bör du se fyra köp:

   Mottaget den	Antal
   2006-01-22	100
   2006-01-22	60
   2006-04-04	50
   2006-04-05	300

3. Du får totalsumman genom att skapa en enkel sammanställningsfråga med hjälp av följande SQL:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. När du växlar till databladsvyn bör du endast se en post:

   MaxOfDate Received	SumOfQuantity
   2006-04-05	510

5. Kombinera följande två frågor i en unionsfråga för att lägga till posten med totalt antal för köpposterna:

   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. När du växlar till databladsvyn bör du se de fyra köpen med summan för varje följt av en post som summerar antalet:

   Mottaget den	Antal
   2006-01-22	60
   2006-01-22	100
   2006-04-04	50
   2006-04-05	300
   2006-04-05	510

Det var allt om grunderna för att lägga till summor i en unionsfråga. Du kanske också vill inkludera fasta värden i båda frågorna, till exempel ”Detaljer” och ”Summa”, för att visuellt separera summaposten från de andra posterna. Du kan läsa om att använda fasta värden i avsnittet Kombinera tre eller fler tabeller eller frågor i en unionsfråga.

Unionsfrågor i Access inkluderar som standard endast unika poster. Men vad händer om du vill ta med alla poster? Ett annat exempel kan vara användbart här.

I föregående avsnitt visade vi hur du skapar en totalsumma i en unionsfråga. Ändra den unionsfrågans SQL så att den inkluderar Produkt-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];

När du växlar till databladsvyn bör du se ett något vilseledande resultat:

En post returnerar förstås inte två gånger kvantiteten totalt.

Orsaken till att du ser det här resultatet är att en dag såldes samma kvantitet choklad två gånger – vilket registrerades i tabellen Inköpsorderdetaljer. Här är resultatet av en enkel urvalsfråga som visar båda posterna i Northwind-exempeldatabasen:

I unionsfrågan som angavs tidigare ser du att fältet Inköpsorder-ID inte finns med och att de två fälten inte är två unika poster.

Om du vill inkludera alla poster kan använda UNION ALL i stället för UNION i SQL. Detta har sannolikt en påverkan på sorteringen av resultatet, så du kanske även vill inkludera en ORDER BY-sats för att bestämma en sorteringsordning. Här visas den ändrade SQL:en som bygger på föregående exempel:

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

När du växlar till databladsvyn bör du se alla detaljer förutom en summa som den sista posten:

En vanlig användning för en unionsfråga är att fungera som datakälla för en kombinationsrutekontroll i ett formulär. Du kan använda den kombinationsrutan för att välja ett värde för att filtrera posterna i formuläret. Du kan till exempel filtrera poster för anställda efter ort.

För att visa hur det kan fungera visar vi här ett annat exempel som du kan skapa i Northwind-exempeldatabasen för att illustrera scenariot.

1. Skapa en enkel urvalsfråga med hjälp av denna SQL-syntax:

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

2. När du växlar till databladsvyn bör du se följande resultat:

   Ort	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. När du tittar på resultaten kanske du inte ser mycket värde. Men du kan expandera frågan och omvandla den till en unionsfråga med hjälp av följande SQL:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. När du växlar till databladsvyn bör du se följande resultat:

   Ort	Filter
   <Alla>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access skapar en union av de nio posterna, som tidigare visats, med de fasta fältvärdena <Alla> och ”*”.

   Eftersom den här unionssatsen inte innehåller UNION ALL returnerar Access endast unika poster vilket innebär att varje ort bara returneras en gång med fasta identiska värden.

5. Nu när du har en slutförd unionsfråga som visar varje ortsnamn bara en gång, tillsammans med ett alternativ som effektivt väljer ut alla orter, kan du använda den här frågan som datakälla för en kombinationsruta i ett formulär. Med det här specifika exemplet som en modell kan du skapa en kombinationsrutekontroll i ett formulär, ange den här frågan som dess datakälla, ange egenskapen Kolumnbredd för kolumnen Filter till 0 (noll) för att dölja den visuellt och sedan ange egenskapen Bunden kolumn till 1 för att ange indexet i den andra kolumnen. I egenskapen Filter i själva formuläret kan du sedan lägga till kod enligt följande för att aktivera ett formulärfilter med hjälp av värdet för vad som är markerat i kombinationsrutekontrollen:

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

   Användare av formuläret kan sedan filtrera formulärets poster efter ett visst ortsnamn eller välja <Alla> för att visa alla poster för alla orter.

Överst på sidan