Több lekérdezés eredményének együttes megjelenítése egyesítő lekérdezéssel

Az előző szakaszban a Northwind adatbázis használatával készült példában csak két tábla adatait egyesítettük. Az egyesítő lekérdezésekben azonban három vagy több táblát is könnyedén egyesíthet. Az előző példára építve például érdemes lehet szerepeltetni az alkalmazottak nevét a lekérdezés eredményében. A feladat elvégzéséhez vegyen fel egy harmadik lekérdezést, és egyesítse az előző SQL-utasítást egy további UNION kulcsszóval, például ezzel:

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

Amikor Adatlap nézetben tekinti meg az eredményt, az összes alkalmazott a mintacég nevével együtt fog szerepelni a listában, ami valószínűleg nem túl praktikus. Ha azt szeretné, hogy a mező jelezze, hogy egy személy belső alkalmazott, szállító vagy vevő, a cég neve helyett szerepeltethet egy rögzített értéket. Az SQL az alábbiak szerint nézne ki:

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

A lekérdezés eredménye így jelenik meg Adatlap nézetben. Az Access ezt az öt mintarekordot jeleníti meg:

A fenti lekérdezés tovább csökkenthető, mivel az Access az egyesítő lekérdezésekben csak az első lekérdezésből olvassa be a kimeneti mezők nevét. Itt láthatja, hogy eltávolítottuk az eredményt a második és a harmadik lekérdezési szakaszból:

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

Az Access egyesítő lekérdezéseiben csak egyszer engedélyezett a rendezés, de minden lekérdezés egyénileg szűrhető. Az előző szakasz egyesítő lekérdezése alapján itt látható egy példa arra, hogy egy WHERE záradékot hozzáadva hol szűrtük az egyes lekérdezéseket.

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

Adatlap nézetre váltva ehhez hasonló eredmények jelenjenek meg:

Ha az egyesítendő lekérdezések nagyon eltérnek, olyan helyzet fordulhat elő, ahol egy kimeneti mezőnek különböző típusú adatokat kell egyesítenie. Ebben az esetben az egyesítő lekérdezés többnyire szöveges adattípusként fogja visszaadni az eredményt, mivel ez az adattípus szöveget és számokat is tartalmazhat.

Ennek megértéséhez használjuk a Terméktranzakciók egyesítő lekérdezést a Northwind mintaadatbázisban. Nyissa meg a mintaadatbázist, majd a Terméktranzakciók lekérdezést Adatlap nézetben. Az utolsó tíz rekordnak ehhez hasonlónak kell lennie:

Tegyük fel, hogy a Mennyiség mezőt kétfelé szeretné osztani – Vásárlás és Eladás részre. Tegyük fel, hogy nulla értéket szeretne rögzíteni az értéket nem tartalmazó mezőhöz. Ehhez az egyesítő lekérdezéshez így fog kinézni az SQL-utasítás:

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; 

Adatlap nézetre váltva az utolsó tíz rekord most az alábbi módon jelenik meg:

A példát folytatva mi a teendő, ha azt szeretné, hogy a nullával rendelkező mezők üresek legyenek? Az SQL-t úgy módosíthatja, hogy nulla helyett semmit se jelenítsen meg, ha a Null kulcsszót az alábbihoz hasonló módon adja hozzá:

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;

Ahogyan azonban az Adatlap nézetre váltáskor megfigyelhette, nem várt eredményt kap. A Vásárlás oszlopban minden mező törlődik:

Ez azért fordul elő, mert az Access a mezők adattípusát az első lekérdezésből határozza meg. Ebben az esetben a Null nem szám.

Mi történik tehát, ha megpróbál beszúrni egy üres karakterláncot a mezők üres értékéhez? Az SQL szintaxis ebben az esetben a következőképpen nézhet ki:

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;

Adatlap nézetre váltva láthatja, hogy az Access lekéri a Vásárlás értékeit, de az értékeket szövegre konvertálta. Láthatja, hogy ezek szöveges értékek, mivel az Adatlap nézetben balról jobbra igazítottak. Az első lekérdezésben az üres karakterlánc nem szám, és ez az oka ennek az eredménynek. Azt is megfigyelheti, hogy az Eladás értékeit szintén szövegre konvertálja a program, mivel a beszerzési rekordok üres karakterláncot tartalmaznak.

Hogyan oldható meg ez a rejtvény?

Egy megoldás a lekérdezés utasítása, hogy a mező értékeként számot várjon. Ez az alábbi kifejezéssel végezhető el:

IIf(False, 0, Null)

Az ellenőrizendő False feltétel soha nem lesz True, ezért a kifejezés mindig Null értéket ad vissza, de az Access továbbra is kiértékeli mindkét kimeneti lehetőséget, és megállapítja, hogy a kimenet numerikus vagy Null.

Ezt a kifejezést az alábbiak szerint használhatjuk a példánkban:

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;

Felhívjuk a figyelmét arra, hogy a második lekérdezést nem szükséges módosítani.

Adatlap nézetre váltva most már láthatja a kívánt eredményt.

Ugyanazon eredmény elérésének másik módja a lekérdezések beillesztése az egyesítő lekérdezésbe egy másik lekérdezéssel:

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

Az Access minden mezőhöz az Ön által megadott adattípus rögzített értékeit adja vissza. Természetesen nem szeretné, hogy a lekérdezés kimenete zavarja az eredményeket, ezért az elkerülendő trükk az, ha a WHERE záradékot False (Hamis) értékre adja:

WHERE False

Ez egy kis trükk, mivel egy mindig hamis, és így a lekérdezés nem ad vissza semmit. Ennek az utasításnak a meglévő SQL-utasítással való egyesítése a következő kész utasítást eredményezi:

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;

Az egyesítő lekérdezések speciális esete, amikor egy rekordkészletet egyesít egyetlen rekorddal, amely egy vagy több mező összegét tartalmazza.

Itt láthat egy másik példát, amelyet a Northwind mintaadatbázisban hozhat létre az összegek egyesítő lekérdezésekben való létrehozásának szemléltetésére.

1. Hozzon létre egy új egyszerű lekérdezést a sör (Termékszám=34 a Northwind adatbázisban) beszerzésének megtekintése céljából a következő SQL szintaxis használatával:

   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. Adatlap nézetre váltva négy beszerzést kell látnia:

   Átvételi dátum	Mennyiség
   2006. 01. 22.	100
   2006. 01. 22.	60
   2006. 04. 04.	50
   2006. 04. 05.	300

3. Az összeg kiszámításához hozzon létre egy egyszerű összesítő lekérdezést a következő SQL használatával:

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

4. Adatlap nézetre váltva csak egy rekordot kell látnia:

   Max átvételi dátum	Összmennyiség
   2006. 04. 05.	510

5. Ezt a két lekérdezést egyetlen egyesítő lekérdezésbe kombinálva fűzze a teljes mennyiséget tartalmazó rekordot a beszerzési rekordokhoz:

   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. Adatlap nézetre váltva négy beszerzést kell látnia, amelyek mindegyikét a mennyiséget összegző rekord követ:

   Átvételi dátum	Mennyiség
   2006. 01. 22.	60
   2006. 01. 22.	100
   2006. 04. 04.	50
   2006. 04. 05.	300
   2006. 04. 05.	510

Ez az összegek alapszintű felvétele az egyesítő lekérdezésekbe. Érdemes mindkét lekérdezésben szerepeltetni rögzített értékeket (például Részletek és Összesen), ha vizuálisan el szeretné különíteni az összesített rekordot a többi rekordtól. A rögzített értékek használatáról a Három vagy több tábla vagy lekérdezés egyesítése egyesítő lekérdezésben című szakaszban olvashat.

Az Accessben az egyesítő lekérdezések alapértelmezés szerint csak külön rekordokat tartalmaznak. Mi a teendő azonban akkor, ha az összes rekordot szeretné szerepeltetni? Hasznos lehet egy másik példán szemléltetni ezt.

Az előző szakaszban bemutattuk az összeg létrehozását egyesítő lekérdezésben. Módosítsa az egyesítő lekérdezés SQL szintaxisát, hogy tartalmazza a Termékszám= 48 adatot:

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

Adatlap nézetre váltva kissé félrevezető eredményt láthat:

Egy rekord összesítve természetesen nem eredményezi a mennyiség kétszeresét.

Ezt az eredményt az magyarázza, hogy egy nap kétszer adtak el ugyanolyan mennyiségű csokoládét – a Megrendelés részletei táblában rögzítettek szerint. Alább látható egy egyszerű választó lekérdezés eredménye, amelyben a Northwind mintaadatbázis mindkét rekordja látható:

A korábban említett egyesítő lekérdezésben láthatja, hogy a Megrendeléskód mező hiányzik, és hogy a két mező nem jelenik meg két külön rekordként.

Ha az összes rekordot fel szeretné venni, az SQL-utasításban a UNION helyett használja a UNION ALL kulcsszót. Ez valószínűleg hatással lesz az eredmények rendezésére, ezért érdemes lehet felvenni egy ORDER BY záradékot a sorrendezés meghatározásához. Itt látható az előző példa alapján módosított SQL szintaxis:

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

Adatlap nézetre váltva láthatja az összes részletet, utolsó rekordként pedig az összeget:

Az egyesítő lekérdezések általában rekordforrásként szolgálnak az űrlapokon szereplő kombinált lista vezérlőelemek számára. Ezt a kombinált listát használva kijelölhet egy értéket az űrlap rekordjainak szűréséhez. Ilyen például az alkalmazotti rekordok szűrése a település szerint.

A működés tanulmányozása végett itt láthat egy másik példát, amelyet a Northwind mintaadatbázisban hozhat létre az eset szemléltetésére.

1. Hozzon létre egy egyszerű választó lekérdezést az alábbi SQL szintaxis használatával:

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

2. Adatlap nézetre váltva a következő eredményt kell látnia:

   Város	Szűrő
   Vác	Vác
   Pilisborosjenő	Pilisborosjenő
   Ráckeve	Ráckeve
   Kiskunfélegyháza	Kiskunfélegyháza
   Vác	Vác
   Ráckeve	Ráckeve
   Vác	Vác
   Ráckeve	Ráckeve
   Vác	Vác

3. Ezt az eredményt megnézve előfordulhat, hogy nem lát sok értéket. Bontsa ki a lekérdezést, és alakítsa át egyesítő lekérdezéssé a következő SQL használatával:

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

4. Adatlap nézetre váltva a következő eredményt kell látnia:

   Város	Szűrő
   <Mind>	*
   Pilisborosjenő	Pilisborosjenő
   Kiskunfélegyháza	Kiskunfélegyháza
   Ráckeve	Ráckeve
   Vác	Vác

   Az Access egyesíti a korábban látható kilenc rekordot, a <Mind> és a „*” rögzített mezőértékekkel.

   Mivel ez az egyesítő záradék nem tartalmazza a UNION ALL kulcsszót, az Access csak a külön rekordokat adja vissza, ami azt jelenti, hogy minden várost csak egyszer ad vissza, rögzített azonos értékkel.

5. Most, hogy már rendelkezik egy kész egyesítő lekérdezéssel, amely minden városnevet csak egyszer jelenít meg, és lehetővé teszi az összes város hatékony kijelölését, felhasználhatja azt egy űrlap kombinált listájának rekordforrásaként. Ezt az adott példát modellként használva létrehozhat egy kombinált lista vezérlőelemet egy űrlapon, beállíthatja ezt a lekérdezést a rekordforrásaként, a Szűrő oszlop Oszlopszélesség tulajdonságát 0 (nulla) értékre állíthatja az elrejtéséhez, majd a Kötött oszlop tulajdonságot 1 értékre állítva megjelölheti a második oszlop indexét. Az űrlap Szűrő tulajdonságában ezután kódot vehet fel a következőhöz hasonlóképpen, hogy a kombinált lista vezérlőelemben kijelölt értéket használva aktiváljon egy űrlapszűrőt:

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

   Az űrlap használója ezután szűrheti az űrlaprekordokat egy adott városnévre, vagy a <Mind> elemet választva az összes város összes rekordját felsorolhatja.

Vissza a lap tetejére