Bruke en unionsspørring til å kombinere flere søk i ett resultat

I eksemplet fra den forrige inndelingen som brukte Northwind-databasen, ble bare data fra to tabeller kombinert. Du kan imidlertid kombinere tre eller flere tabeller på en enkel måte i en unionsspørring. Du kan for eksempel, ved å fortsette på det forrige eksemplet, inkludere navnen på de ansatte i spørringsutdataene. Dette gjør du ved å legge til en tredje spørring og kombinere den forrige SQL-setningen med et nytt UNION-nøkkelord 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 viser resultatet i dataarkvisning, vil alle ansatte bli oppført med eksempelet på firmanavn, noe som sannsynligvis ikke er veldig nyttig. Hvis du vil at feltet skal angi om en person er en internt ansatt, fra en leverandør eller fra en kunde, kan du inkludere en fast verdi i stedet for firmanavnet. Slik vil SQL-setningen 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];

Slik vises resultatet i dataarkvisning. Access vises disse fem eksempelpostene:

Spørringen ovenfor kan reduserer ytterligere da Access bare leser navnene på utdatafeltene fra den første spørringen i en unionsspørring. Her kan du se at vi har fjernet utdataene fra inndelingene til andre og tredje spørring:

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

Bestilling er bare tillatt én gang, men hver spørring kan filtreres enkeltvis, i en unionsspørring i Access. Ved å fortsette på unionsspørringen fra forrige inndeling ser du her et eksempel hvor vi har filtrert hver spørring ved å legge til en WHERE-setning.

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

Bytt til dataarkvisning, og du ser da resultater som ligner på disse:

Hvis spørringene til unionen er svært forskjellige, kan det hende du støter på en situasjon der et utdatafelt må kombinere data fra forskjellige datatyper. I så fall vil unionsspørringen oftest returnere resultatene som en tekstdatatype, siden denne datatypen kan inneholde både tekst og tall.

Hvis du vil forstå hvordan dette fungerer, bruker vi unionsspørringen Produkttransaksjoner i eksempeldatabasen for Northwind. Åpne eksempeldatabasen, og åpne deretter Produkttransaksjoner-spørringen i dataarkvisning. De siste ti postene skal ligne på disse utdataene:

La oss anta at du ønsker Antall-feltet delt i to – Kjøp og Salg. La oss også anta at du ønsker å ha en fast nullverdi for feltet uten verdi. Slik vil SQL-setningen se ut for denne unionsspørringen:

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 bytter til dataarkvisning, ser du de ti siste postene oppført som følger:

Hvis vi fortsetter med dette eksemplet, hva om du ønsker at feltene med null skal være tomme? Du kan endre SQL-setningen slik at den bare viser null ved å legge til Null-nøkkelordet som følger:

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;

Du har nå imidlertid et uventet resultat, som du kanskje la merke til da du byttet til dataarkvisning. I kolonnen Kjøp er innholdet i hvert felt fjernet:

Årsaken til dette er at Access fastslår datatypene for feltene fra den første spørringen. I dette eksemplet er ikke Null et tall.

Hva skjer så hvis du prøver å sette inn en tom streng for den tomme verdien i feltene? SQL-setningen for dette forsøket kan se ut som følger:

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 bytter til dataarkvisning, ser du at Access henter Kjøp-verdiene, men at verdiene ble konvertert til tekst. Du ser at dette er tekstverdier da de er venstrejustert i dataarkvisning. Den tomme strengen i den første spørringen er ikke et tall, og derfor ser du disse resultatene. Du legger også merke til at Salg-verdiene konverteres til tekst, fordi kjøpspostene inneholder en tom streng.

Hva gjør du for å løse dette?

Én løsning kan være å tvinge spørringen til å forvente at feltverdien skal være et tall. Dette kan oppnås med uttrykket:

IIf(False, 0, Null)

Betingelsen som skal kontrolleres, Usann, blir aldri Sann, og derfor returnerer uttrykket alltid Null. Men Access evaluerer fremdeles begge utdataalternativene og fastslår om utdataene er numerisk eller Null.

Slik kan vi bruke dette uttrykket i vårt 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;

Vær oppmerksom på at du ikke trenger å endre den andre spørringen.

Hvis du bytter til dataarkvisning, ser du nå alternativet vi ønsker:

En alternativ metode for å oppnå samme resultat er å starte spørringene i unionsspørringen med enda en spørring:

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

Access returnerer faste verdier for datatypen du definerer, for hvert felt. Du ønsker selvsagt ikke at utdataene i denne spørringen skal samhandle med resultatene, så du bør derfor inkludere en WHERE-setning og angi den til Usann.

WHERE False

Dette er et lite triks siden denne verdien alltid er usann, og spørringen kommer ikke til å returnere noe. Du kombinerer denne setningen med den eksisterende SQL-setningen, og vi får en fullført setning som følger:

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;

Et spesialtilfelle for en unionsspørring er at du kan kombinere et sett med poster som inneholder summen av ett eller flere felter.

Her ser du et annet eksempel som du kan opprette i eksempeldatabasen for Northwind, for å illustrere hvordan du inkluderer en totalsum i en unionsspørring.

1. Opprett en ny enkel spørring for å vise kjøp av øl (Product ID=34 i Northwind-databasen) ved bruk av 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. Bytt til dataarkvisning, og du skal da se fire kjøp:

   Dato mottatt	Antall
   22.01.2006	100
   22.01.2006	60
   4.04.2006	50
   05.04.2006	300

3. Hvis du ønsker en totalsum, oppretter du en enkel samlespørring ved bruk av følgende SQL-setning:

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

4. Bytt til dataarkvisning, og du skal da se bare én post:

   MaxOfDate Received	SumOfQuantity
   05.04.2006	510

5. Kombiner disse to postene i en unik unionsspørring for å legge til totalantallet i posten for kjøpspostene.

   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. Bytt til dataarkvisning, og du skal se de fire kjøpene med summen for hver, etterfulgt av en post som inneholder totalantallet.

   Dato mottatt	Antall
   22.01.2006	60
   22.01.2006	100
   4.04.2006	50
   05.04.2006	300
   05.04.2006	510

Dette tok for seg det grunnleggende med å legge til summer i en unionsspørring. Du ønsker kanskje også å inkludere faste verdier i begge spørringene som «Detalj» og «totalsum» for å separere totalsum-posten fra de andre postene visuelt. Du kan lese om å bruke faste verdier i inndelingen Kombinere tre eller flere tabeller eller spørringer i en unionsspørring.

Unionsspørringer i Access inneholder bare forskjellige poster som standard. Men hva om du ønsker å inkludere alle postene? Et annet eksempel kan være nyttig.

I den forrige inndelingen lærte du hvordan du opprettet en totalsum i en unionsspørring. Endre SQL-setningen for den unionsspørringen slik at den inkluderer 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];

Bytt til dataarkvisning, og du ser da et litt villedende resultat:

Én post returnerer selvfølgelig ikke to ganger antallet totalt.

Årsaken til at du ser dette resultatet er fordi samme antall sjokolade ble solgt to ganger på samme dag – som du kan se i tabellen Innkjøpsordredetaljer. Her ser du et resultat for en enkel utvalgsspørring som viser begge postene i eksempeldatabasen til Northwind:

I unionsspørringen som ble nevnt tidligere kan du se at feltet Innkjøpsordre-ID ikke er inkludert, og at de to feltene ikke utgjør to forskjellige poster.

Hvis du ønsker å inkludere alle postene, bruker du UNION ALL i stedet for UNION i SQL-setningen. Dette kommer sannsynlig bare til å få innvirkning på sorteringen av resultatene, så du kan også inkludere en ORDER BY-setning for å fastslå sorteringsrekkefølgen. Her ser du den endrede SQL-setningen som ble endret fra 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];

Bytt til dataarkvisning, og du skal se alle detaljene i tillegg til en totalsum som siste post.

Et vanlig brukstilfelle for en unionsspørring er å bruke den som en postkilde for en kombinasjonsbokskontroll i et skjema. Du kan bruke den kombinasjonsboksen til å velge en verdi for å filtrere postene i et skjema. Du kan for eksempel filtrere de ansatte etter bosted.

Hvis du vil se hvordan dette kan fungere, ser du her et annet eksempel som du kan opprette i eksempeldatabasen for Northwind, for å illustrere dette scenarioet.

1. Opprette en enkel utvalgsspørring ved bruk av SQL-syntaks:

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

2. Bytt til dataarkvisning, og du skal da se følgende resultater:

   Poststed	Filter
   Seattle	Seattle
   Bellevue	Bellevue
   Redmond	Redmond
   Kirkland	Kirkland
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle
   Redmond	Redmond
   Seattle	Seattle

3. Det er ikke sikkert du får mye verdi fra disse resultatene. Utvid spørringen, og transformer den til en unik spørring ved bruk av følgende SQL-setning:

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

4. Bytt til dataarkvisning, og du skal da se følgende resultater:

   Poststed	Filter
   <All>	*
   Bellevue	Bellevue
   Kirkland	Kirkland
   Redmond	Redmond
   Seattle	Seattle

   Access utfører en unionspørring av de ni postene, som ble vist tidligere, med faste feltverdier <All> og "*".

   Siden denne unionsetningen ikke inneholder UNION ALL, returnerer Access bare forskjellige poster. Det betyr at hvert poststed returneres bare én gang med faste identiske verdier.

5. Nå som du har en fullført unionsspørring som viser hvert bynavn bare én gang, sammen med et alternativ som effektivt velger alle byer, kan du bruke denne spørringen som postkilde for en kombinasjonsboks i et skjema. Hvis du bruker dette eksemplet som modell, kan du opprette en kombinasjonsbokskontroll i et skjema, angi denne spørringen som postkilde, angi kolonnebreddeegenskapen for filterkolonnen til 0 (null) for å skjule den visuelt, og deretter angi egenskapen Bundet kolonne til 1 for å angi indeksen for den andre kolonnen. I filteregenskapen for selve skjemaet kan du deretter legge til kode, for eksempel følgende, for å aktivere et skjemafilter ved hjelp av verdien for det som ble valgt i kombinasjonsbokskontrollen:

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

   Brukeren av skjemaet kan deretter filtrere skjemapostene etter et spesifikt poststedsnavn, eller velge <All> for å vise alle poster for alle poststedene.

Til toppen av siden