Brug en foreningsforespørgsel til at samle flere forespørgsler i ét resultat

I eksemplet fra forrige afsnit, der bruger Northwind-databasen, kombineres kun data fra to tabeller. Du kan dog nemt kombinere tre eller flere tabeller i en foreningsforespørgsel. Hvis du f.eks. bygger videre på det forrige eksempel, vil du måske også medtage navnene på medarbejderne i forespørgselsresultatet. Du kan udføre denne opgave ved at tilføje en tredje forespørgsel og kombinere med den forrige SQL-sætning med et ekstra UNION-nøgleord som dette:

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 får vist resultatet i dataarkvisning, vises alle medarbejdere på en liste med eksempelfirmaets navn, hvilket nok ikke er til stor gavn. Hvis du vil have, at feltet skal angive, om en person er ansat internt, kan du fra en leverandør eller fra en kunde inkludere en fast værdi i stedet for firmanavnet. Her kan du se, hvordan SQL'en ser ud:

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ådan ser resultatet ud i dataarkvisningen. Access viser disse fem eksempelposter:

Forespørgslen ovenfor kan reduceres yderligere, da Access kun læser navnene på outputfelterne fra den første forespørgsel i en foreningsforespørgsel. Her kan du se, at vi har fjernet outputtet fra den anden og tredje forespørgselssektion:

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

Det er kun tilladt at sortere én gang i en Access-foreningsforespørgsel, men hver forespørgsel kan filtreres enkeltvist. Vi bygger videre på den forrige sektions foreningsforespørgsel og viser her et eksempel, hvor vi har filtreret hver forespørgsel ved at tilføje en WHERE-delsætning.

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

Skift til dataarkvisning for at få vist resultater, der ser ud som herunder:

Hvis forespørgslerne, der skal forenes, er meget forskellige, kan der opstå en situation, hvor et outputfelt skal kombinere data fra forskellige datatyper. Hvis dette er tilfældet, så vil foreningsforespørgslen som oftest returnere resultaterne som en tekstdatatype, idet denne datatype kan indeholde både tekst og tal.

For at forstå, hvordan dette fungerer, så bruger vi foreningsforespørgslen Produkttransaktioner i Northwind-eksempeldatabasen. Åbn eksempeldatabasen, og åbn derefter forespørgslen Produkttransaktioner i dataarkvisning. De seneste ti poster bør svare til dette output:

Lad os antage, at du vil opdele feltet Antal i to – Køb og Salg. Lad os også antage, at du vil have en fast nulværdi for feltet uden værdi. Her kan du se, hvordan SQL'en ser ud for denne foreningsforespørgsel:

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; 

Hvis du skifter til dataarkvisning, får du vist de sidste ti poster nu, som vist herunder:

I forlængelse af dette eksempel – hvad hvis felterne med nul skal være tomme? Du kan ændre SQL'en, så den viser ingenting i stedet for nul ved at tilføje nøgleordet Null, således:

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 måske har oplevet ved at skifte til dataarkvisning, så får du nu et uventet resultat. I kolonnen Køb er alle felter blevet ryddet:

Dette skyldes, at Access fastlægger datatyperne for felterne fra den første forespørgsel. I dette eksempel er Null ikke et tal.

Så hvad sker der, hvis du forsøger at indsætte en tom streng for den tomme værdi af felter? SQL'en for dette forsøg kan se ud som følgende:

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 skifter til dataarkvisning, kan du se, at Access henter værdierne for Køb, men har konverteret værdierne til tekst. Du kan se, at det er tekstværdier, fordi de er venstrejusteret i dataarkvisningen. En tom streng i den første forespørgsel er ikke et tal, hvilket er grunden til, at du kan se de pågældende resultater. Du vil også bemærke, at Sælg-værdierne er konverteret til tekst, fordi posterne for køb indeholder en tom streng.

Hvordan kan du løse dette problem?

En løsning er at tvinge forespørgslen til at forvente, at feltværdien er et tal. Det kan gøres med følgende udtryk:

IIf(False, 0, Null)

Kontrollér betingelsen: Falsk kan aldrig være Sand, derfor vil udtrykket altid returnere Null, men Access evaluerer stadig begge outputindstillinger og beslutter, om outputtet er numerisk eller Null.

Vi kan bruge dette udtryk på følgende måde i vores fungerende eksempel:

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;

Bemærk, at det ikke er nødvendigt at ændre den anden forespørgsel.

Hvis du skifter til dataarkvisning, får du nu vist et korrekt resultat:

Du kan også opnå det samme resultat ved at benytte en metode, hvor du foranstiller forespørgslerne i foreningsforespørgslen med endnu en forespørgsel:

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

For hvert felt returnerer Access faste værdier af datatypen, du definerer. Du vil selvfølgelig ikke have, at outputtet fra denne forespørgsel indvirker på resultaterne, så derfor skal du føje en WHERE-delsætning til Falsk:

WHERE False

Dette er et lille trick, da det altid er falsk, og derefter returnerer forespørgslen ikke noget. Når denne sætning kombineres med den eksisterende SQL, så ender vi med den følgende fuldførte sætning:

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;

Der gælder det særlige forhold ved en foreningsforespørgsel, at et sæt poster kan kombineres med én post, der indeholder summen af ét eller flere felter.

Her er et andet eksempel, som du kan oprette i Northwind-eksempeldatabasen til illustration af, hvordan du får en totalværdi i en foreningsforespørgsel.

1. Opret en ny simpel forespørgsel for at få vist indkøb af øl (Produkt-id=34 i Northwind-databasen) ved hjælp af følgende SQL-syntaks:

   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 skifter til dataarkvisning, bør du se fire køb:

   Modtaget d.	Antal
   22-01-2006	100
   22-01-2006	60
   04-04-2006	50
   05-04-2006	300

3. For at få totalen skal du oprette en simpel aggregeringsforespørgsel ved at bruge den følgende SQL:

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

4. Når du skifter til dataarkvisning, bør du kun se én post:

   MaxOfDate modtaget	SumOfQuantity
   05-04-2006	510

5. Kombiner disse to forespørgsler i en foreningsforespørgsel for at føje posten med den totale mængde til posterne for køb:

   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. Hvis du skifter til dataarkvisning, bør du se de fire køb med summen for hver efterfulgt af en post, der lægger den samlede mængde sammen:

   Modtaget d.	Antal
   22-01-2006	60
   22-01-2006	100
   04-04-2006	50
   05-04-2006	300
   05-04-2006	510

Det dækker det grundlæggende om at føje totaler til en foreningsforespørgsel. Du kan også medtage faste værdier i begge forespørgsler, f.eks. "Detaljer" og "Total", for visuelt at adskille totalposten fra de andre poster. Du kan gennemse ved hjælp af faste værdier i afsnittet Kombiner tre eller flere tabeller eller forespørgsler i en foreningsforespørgsel.

Foreningsforespørgsler i Access inkluderer som standard kun særskilte poster. Men hvad nu, hvis du vil inkludere alle poster? Et andet eksempel kan være nyttigt her.

I det forrige afsnit viste vi, hvordan du opretter en total i en foreningsforespørgsel. Tilpas den pågældende foreningsforespørgsels SQL, så den omfatter 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 skifter til dataarkvisning, bør du se et noget misvisende resultat:

Selvfølgelig returnerer en post ikke to gange den totale mængde.

Du ser dette resultat, fordi den samme mængde chokolade blev solgt to gange på den samme dag – som registreret i tabellen med Indkøbsordredetaljer. Her er en simpel udvælgelsesforespørgsel, der viser begge poster i Northwind-eksempeldatabasen:

I foreningsforespørgslen tidligere kan du se, at feltet Købsordre-id for købet ikke er inkluderet, og at de to felter ikke udgør to særskilte poster.

Hvis du vil inkludere alle poster, skal du bruge UNION ALL i stedet for UNION i SQL'en. Det vil højst sandsynligt få indflydelse på sorteringen af resultaterne, så du kan med fordel indsætte en ORDER BY-delsætning for at definere en sorteringsrækkefølge. Her ses den ændrede SQL opbygget ud fra det forrige eksempel:

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 skifter til dataarkvisning, bør du se alle oplysningerne ud over en total som den sidste post:

En foreningsforespørgsel bruges almindeligvis som postkilden i et kombinationsfeltelement i en formular. Du kan bruge dette kombinationsfelt til at vælge en værdi beregnet til at filtrere formularens poster. Eksempelvis for at filtrere medarbejderposterne efter deres by.

Du kan se, hvordan dette kan fungere med endnu et eksempel, som du kan oprette i Northwind-eksempeldatabasen for at illustrere dette scenarie.

1. Opret en simpel forespørgsel ved hjælp af denne SQL-syntaks:

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

2. Skift til dataarkvisning, og så bør du kunne se de følgende resultater:

   By	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Resultaterne viser nok ikke meget af værdi. Men prøv at udvide forespørgslen og ændre den til en foreningsforespørgsel ved hjælp af den følgende SQL:

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

4. Skift til dataarkvisning, og så bør du kunne se de følgende resultater:

   By	Filter
   <Alle>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access gennemfører en forening af de ni poster, vist tidligere, med de faste feltværdier <All> og "*".

   Da denne foreningsdelsætning ikke indeholder UNION ALL, returnerer Access kun entydige poster, hvilket betyder, at hver by kun returneres én gang med faste identiske værdier.

5. Nu hvor du har fuldført en foreningsforespørgsel, hvor hvert bynavn kun vises én gang sammen med en indstilling, der effektivt vælger alle byer, kan du bruge denne forespørgsel som postkilde for et kombinationsfelt på en formular. Du kan bruge dette specifikke eksempel som en model til at oprette et kombinationsfeltelement på en formular, angive denne forespørgsel som dets postkilde, angive egenskaben Kolonnebredde på filterkolonnen til 0 (nul) for at skjule den visuelt og derefter angive bundkolonnens egenskab til 1 for at vise indekset for den anden kolonne. I egenskaben Filter i selve formularen kan du derefter tilføje kode såsom den følgende for at aktivere et formularfilter ud fra værdien af det, der blev valgt i kombinationsfeltelementet:

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

   Brugeren af formularen kan derefter filtrere formularens poster efter et specifikt bynavn eller vælge <All> for at få vist en liste over alle poster for alle byer.

Til toppen af siden