Vahel võib teil tekkida soov loetleda ühe tabeli või päringu kirjed koos mõne muu tabeli kirjetega, et moodustada neist ühe kirjekomplekti ehk loendi kõigi kahes või enamas tabelis leiduvate kirjetega. Accessis aitab seda teha ühispäring.
Ühispäringute mõistmiseks peaksite esmalt olema tuttav lihtsate valikupäringute koostamisega Accessis. Valikupäringute koostamise kohta leiate lisateavet artiklist Lihtsa valikupäringu loomine.
Toimiva ühispäringuga tutvumine
Kui te pole varem ühispäringuid loonud, võiksite esmalt uurida töötavat näidet Northwindi Accessi mallis. Näidismalli Põhjatuule saate otsida Accessi alustamislehel, klõpsates nuppu Fail > Uus või saate koopia otse alla laadida järgmisest asukohast: Northwindi näidismall.
Pärast seda, kui Access on Northwindi andmebaasi avanud, sulgege esmalt kuvatava sisselogimisdialoogi vorm ja laiendage seejärel navigeerimispaan. Kõigi andmebaasiobjektide korraldamiseks nende tüübi järgi klõpsake navigeerimispaani ülaosas nuppu Objekti tüüp . Järgmisena laiendage jaotist Päringud ja teile kuvatakse päring nimega Tootetehingud.
Ühispäringuid on lihtne teistest päringuobjektidest eristada, kuna need on märgitud kahe hulga ühisosa tähistava ikooniga, mis meenutab kahte omavahel ühendatud ringi:
Erinevalt tavalistest valiku- ja toimingupäringutest pole tabelid ühispäringus seotud, mis tähendab, et Accessi graafilist päringukoosturit ei saa kasutada ühispäringute koostamiseks ega redigeerimiseks. See ilmneb siis, kui avate navigeerimispaani kaudu ühispäringu; Access avab selle ja kuvab tulemid andmelehevaates. Menüü Avaleht käsu Vaated all märkate kindlasti, et ühispäringutega töötamisel pole kujundusvaade saadaval. Ühispäringutega töötades saate andmelehevaate ja SQL-i vaate vaheldumisi aktiveerida.
Ühispäringu näitega edasi tutvumiseks valige päringu määratleva SQL-i süntaksi vaatamiseks Avaleht > Vaated > SQL-i vaade. Selles illustratsioonis oleme SQL-i lisanud tühikuid, et teil oleks lihtsam aru saada erinevatest osadest, millest ühispäring koosneb.
Vaatame selle Northwindi andmebaasi ühispäringu SQL-i süntaksit lähemalt.
SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]
UNION
SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
Selle SQL-lause esimene ja kolmas osa on sisuliselt kaks valikupäringut. Need päringud toovad kaks erinevat kirjekomplekt, ühe tabelist Product Orders (Tootetellimused) ja teise tabelist Product Purchases (Tooteostud).
Selle SQL-lause teine osa on võtmesõna UNION, mis ütleb Accessile, et see päring ühendab need kaks kirjekomplekti.
SQL-lause viimane osa määrab ära kombineeritud kirjete järjestuse, kasutades selleks lauset ORDER BY. Selles näites järjestab Access kõik kirjed tellimiskuupäeva välja „Order Date“ järgi laskuvas järjestuses.
Märkus.: Ühispäringud on Accessis alati kirjutuskaitstud: andmelehevaates ei saa te väärtusi muuta.
Ühispäringu loomine valikupäringute loomise ja kombineerimise kaudu
Ehkki ühispäringu saab luua ka SQL-i süntaksi otse SQL-i vaates kirja pannes, võite päringu ka valikupäringute abil osadena koostada, kuna see võib osutuda lihtsamaks. Seejärel saate SQL-i osad kopeerida ja kombineeritud ühispäringuks kleepida.
Kui soovite juhiste lugemise vahele jätta ja selle asemel näidet vaadata, lugege järgmist jaotist Ühispäringu koostamise näite vaatamine.
-
Klõpsake menüü Loo jaotises Päringud nuppu Päringukujundus.
-
Topeltklõpsake tabelit, mis sisaldab välju, mida soovite kaasata. Tabel lisatakse päringu kujundusaknasse.
-
Topeltklõpsake päringu kujundusaknas igat välja, mille soovite kaasata. Väljade valimisel veenduge, et lisate iga valikupäringu jaoks sama arvu välju samas järjekorras. Pöörake tähelepanu väljade andmetüüpidele ja veenduge, et need oleksid teiste liidetavate päringute väljade järjekorras samal kohal olevate väljade andmetüüpidega ühilduvad. Näiteks kui teie esimeses valikupäringus on viis välja, millest esimene sisaldab kuupäeva-/kellaajaandmeid, veenduge, et kõigis muudes koostatavates valikupäringutes oleks samuti viis välja, millest esimene sisaldab kuupäeva-/kellaajaandmeid jne.
-
Väljadele kriteeriumide lisamiseks saate ka tippida vastavad avaldised väljaruudustiku reale Kriteeriumid.
-
Pärast väljade ja nende kriteeriumite lisamist peaksite päringu käivitama ja selle väljundi üle vaatama. Klõpsake menüü Kujundus jaotises Tulemid nuppu Käivita.
-
Lülitage päring kujundusvaatesse.
-
Salvestage valikupäring, kuid ärge seda sulgege.
-
Korrake seda protseduuri iga valikupäringu suhtes, mida soovite liita.
Nüüd, kui olete valikupäringud loonud, on aeg need omavahel kombineerida. Selles etapis loote ühispäringu SQL-lausete kopeerimise ja kleepimise abil.
-
Klõpsake menüü Loo jaotises Päringud nuppu Päringukujundus.
-
Klõpsake menüü Kujundus jaotises Päring nuppu Ühispäring. Access peidab päringu kujundusakna ja kuvab SQL-i vaate objektivahekaardi. Praegu on SQL-i vaate objektivahekaart tühi.
-
Klõpsake esimese valikupäringu vahekaarti, mida soovite ühispäringusse liita.
-
Klõpsake menüü Avaleht nuppu Vaade ning seejärel käsku SQL-i vaade.
-
Kopeerige valikupäringu SQL-lause. Klõpsake varem looma hakatud ühispäringu vahekaarti.
-
Kleepige valikupäringu SQL-lause ühispäringu SQL-i vaate objekti vahekaardile.
-
Kustutage SQL-päringu lõpus olev semikoolon (;).
-
Vajutage kursori järgmisele reale viimiseks sisestusklahvi (Enter) ja tippige uuele reale UNION.
-
Klõpsake järgmise valikupäringu vahekaarti, mida soovite ühispäringusse liita.
-
Korrake juhiseid 5–10, kuni olete kopeerinud ja kleepinud ühispäringu SQL-i vaatesse kõikide valikupäringute SQL-laused. Ärge kustutage semikoolonit viimase valikupäringu viimase SQL-lause tagant, samuti ärge tippige midagi selle järele.
-
Klõpsake menüü Kujundus jaotises Tulemid nuppu Käivita.
Teie ühispäringu tulemused kuvatakse andmelehevaates
Ühispäringu koostamise näite vaatamine
Siin on näide, mille saate Northwindi näidisandmebaasis uuesti luua. Ühispäring kogub inimeste nimed tabelist Customers (Kliendid) ja kombineerib need inimeste nimedega tabelist Suppliers (Hankijad). Kui soovite kaasa mõelda, täitke oma Northwindi näidisandmebaasi eksemplaris järjest siin toodud juhised.
Selle näite koostamiseks tuleb teha järgmist.
-
Looge valikupäringud Päring1 ja Päring2, kasutades andmeallikana vastavalt tabeleid „Customers“ ja „Suppliers“. Kuvatavate väärtustena kasutage välju First Name (Eesnimi) ja Last Name (Perekonnanimi).
-
Looge uus päring nimega Päring3, millel pole esialgu andmeallikat. Seejärel klõpsake menüüs Kujundus nuppu Ühispäring, et muuta see päring ühispäringuks.
-
Kopeerige ja kleepige Päring1 ja Päring2 SQL-laused aknasse Päring3. Eemaldage kindlasti üleliigne semikoolon ja lisage võtmesõna UNION. Seejärel saate tulemeid vaadata andmelehevaates.
-
Lisage ühte päringusse järjestusklausel ja kleepige siis lause ORDER BY ühispäringu SQL-i vaatesse. Võtke arvesse, et ühispäringus (Päring3) eemaldatakse järjestuse lisamisel esmalt semikoolonid ja seejärel tabeli nimi väljanimedest.
-
Lõplik SQL, mis kombineerib ja sordib selles ühispäringunäites nimed, on järgmine:
SELECT Customers.Company, Customers.[Last Name], Customers.[First Name] FROM Customers UNION SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name] FROM Suppliers ORDER BY [Last Name], [First Name];
Kui tunnete end SQL-i süntaksi kirjutamisel väga tugevalt, saate ühispäringu SQL-lause loomulikult ise otse SQL-i vaates kirjutada. Siiski võib teiste päringuobjektide SQL-i kopeerimine ja kleepimine osutuda mugavamaks. Iga päring võib olla palju keerukam kui siin kasutatud lihtsate valikupäringute näited. Seetõttu võiksite iga päringut enne nende ühispäringuks liitmist põhjalikult katsetada. Kui ühispäring ei tööta, saate iga päringut eraldi korrigeerida seni, kuni see töötab, ja siis ühispäringu õige süntaksiga uuesti koostada.
Ühispäringute kohta lisateabe ja näpunäidete saamiseks lugege läbi selle artikli ülejäänud jaotised.
Eelmise jaotise näidetes, kus kasutati Northwindi andmebaasi, kombineeriti ainult kahe tabeli andmed. Ühispäringus saate aga hõlpsasti kombineerida ka kolme või enama tabeli andmeid. Näiteks saaksite eelmises näites kerge vaevaga lisada päringu väljundisse ka töötajate nimed. Selleks lisage kolmas päring ja kombineerige eelmine SQL-lause täiendava UNION-võtmesõnaga, nagu siin näidatud:
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];
Kui vaatate tulemeid andmelehevaates, on kõik töötajad ära toodud näidisettevõtte nimega, mis pole tõenäoliselt väga abiks. Kui soovite, et see väli näitaks, kas inimene on teie enda ettevõtte töötaja, hankija töötaja või kliendi töötaja, saate ettevõtte nime asemel kasutada fikseeritud väärtust. SQL näeks sel juhul välja umbes selline:
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];
Siin näete, kuidas tulem andmelehevaates kuvatakse. Access kuvab need viis näidiskirjet:
Employment (Töösuhe) |
Perekonnanimi |
First Name (Eesnimi) |
In-house (Oma töötaja) |
Freehafer |
Nancy |
In-house (Oma töötaja) |
Giussani |
Laura |
Supplier (Hankija) |
Glasson |
Stuart |
Customer (Klient) |
Goldschmidt |
Daniel |
Customer (Klient) |
Gratacos Solsona |
Antonio |
Kuna Access loeb nimed ainult ühispäringu esimese päringu väljundiväljadelt, saab ülalpool toodud päringut veelgi rohkem lihtsustada. Siin näete, et oleme teise ja kolmanda päringu jaotistest väljundi eemaldanud:
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];
Accessi ühispäringus on järjestamine lubatud ainult üks kord, kuid iga päringut saab omaette filtreerida. Võttes aluseks eelmises jaotises kirjeldatud ühispäringu, on siin näide selle kohta, kuidas oleme iga päringu WHERE-klausli lisamise abil filtreerinud.
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];
Kui avate andmelehevaate, peaksite nägema sarnaseid tulemeid:
Employment (Töösuhe) |
Perekonnanimi |
First Name (Eesnimi) |
Supplier (Hankija) |
Andersen |
Elizabeth A. |
In-house (Oma töötaja) |
Freehafer |
Nancy |
Customer (Klient) |
Hasselberg |
Jonas |
In-house (Oma töötaja) |
Hellung-Larsen |
Anne |
Supplier (Hankija) |
Hernandez-Echevarria |
Amaya |
Customer (Klient) |
Mortensen |
Sven |
Supplier (Hankija) |
Sandberg |
Mikael |
Supplier (Hankija) |
Sousa |
Luis |
In-house (Oma töötaja) |
Thorpe |
Steven |
Supplier (Hankija) |
Weiler |
Cornelia |
In-house (Oma töötaja) |
Zare |
Robert |
Kui ühendatavad päringud on väga erinevad, võib tekkida olukord, kus väljundväljal tuleb kombineerida eri andmetüüpidega andmeid. Sel juhul tagastab ühispäring tulemid enamasti teksti andmetüübiga, kuna see andmetüüp saab hõlmata nii teksti kui ka arve.
Selle paremaks selgitamiseks kasutame Northwindi näidisandmebaas ühispäringut Product Transactions (Tootetehingud). Avage see näidisandmebaas ja seejärel avage päring „Product Transactions“ andmelehevaates. Kümme viimast kirjet peaksid olema umbes järgmised:
Product ID (Toote ID) |
Tellimuse kuupäev |
Company Name (Ettevõtte nimi) |
Transaction (Tehing) |
Quantity (Kogus) |
77 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
60 |
80 |
22.01.2006 |
Supplier D (Hankija D) |
Purchase (Ost) |
75 |
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
125 |
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
200 |
7 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
51 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
80 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
34 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
100 |
80 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
30 |
Oletame, et soovite välja Kogus tükeldada kaheks : Osta ja Müü. Oletame ka, et soovite ilma väärtuseta välja jaoks kasutada fikseeritud nullväärtust. Sql otsib seda ühispäringut järgmiselt.
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;
Kui avate andmelehevaate, näete, et viimased kümme kirjet kuvatakse nüüd nii:
Product ID (Toote ID) |
Tellimuse kuupäev |
Company Name (Ettevõtte nimi) |
Transaction (Tehing) |
Ostmine |
Sell (Müük) |
74 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
20 |
0 |
77 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
60 |
0 |
80 |
22.01.2006 |
Supplier D (Hankija D) |
Purchase (Ost) |
75 |
0 |
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
125 |
0 |
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
200 |
0 |
7 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
0 |
10 |
51 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
0 |
10 |
80 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
0 |
10 |
34 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
0 |
100 |
80 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
0 |
30 |
Mida teha siis, kui soovite, et nullväärtusega väljad oleksid tühjad? Sql-i saate muuta nii, et see ei kuvaks nulli asemel midagi, lisades märksõna Null järgmiselt:
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;
Ent nagu andmelehevaatest näha, annab see mõnevõrra ootamatu tulemuse. Veerus Buy (Ost) on nüüd kõik väljad tühjad:
Product ID (Toote ID) |
Tellimuse kuupäev |
Company Name (Ettevõtte nimi) |
Transaction (Tehing) |
Ostmine |
Sell (Müük) |
74 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
||
77 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
||
80 |
22.01.2006 |
Supplier D (Hankija D) |
Purchase (Ost) |
||
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
||
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
||
7 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
51 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
80 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
34 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
100 |
|
80 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
30 |
Põhjus on selles et Access määratleb väljade andmetüübid esimese päringu põhjal. Käesolevas näites pole märksõna Null arv.
Mis siis juhtub, kui proovite lisada väljade tühja väärtuse korral tühja stringi? Selle katse SQL võib välja näha umbes järgmine:
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;
Andmelehevaates näete, et Access toob veeru Buy (Ost) väärtused, kuid teisendab need väärtused tekstiks. Seda, et tegemist on tekstväärtustega, näitab see, et need on andmelehevaates vasakjoondatud. Neid tulemeid näete seetõttu, et esimese päringu tühi string ei ole arv. Samuti märkate kindlasti, et ka veeru Sell (Müük) väärtused on teisendatud tekstiks, kuna ostukirjed sisaldavad tühja stringi.
Product ID (Toote ID) |
Tellimuse kuupäev |
Company Name (Ettevõtte nimi) |
Transaction (Tehing) |
Ostmine |
Sell (Müük) |
74 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
20 |
|
77 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
60 |
|
80 |
22.01.2006 |
Supplier D (Hankija D) |
Purchase (Ost) |
75 |
|
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
125 |
|
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
200 |
|
7 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
51 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
80 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
34 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
100 |
|
80 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
30 |
Kuidas seda mõistatust lahendada?
Lahendus on panna päring eeldama, et välja väärtus on arv. Seda saab teha järgmise avaldisega:
IIf(False, 0, Null)
Kuna kontrollitav tingimus False (Väär) pole kunagi True (Tõene), tagastab avaldis alati tühiväärtuse Null, ent Access väärtustab siiski mõlemad väljundivariandid ja otsustab, kas väljund on arvuline või tühi (Null).
Selle artikli töönäites saame avaldist kasutada nii:
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õtke arvesse, et teist päringut pole vaja muuta.
Kui aktiveerite andmelehevaate, näetegi nüüd soovitud tulemust:
Product ID (Toote ID) |
Tellimuse kuupäev |
Company Name (Ettevõtte nimi) |
Transaction (Tehing) |
Ostmine |
Sell (Müük) |
74 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
20 |
|
77 |
22.01.2006 |
Supplier B (Hankija B) |
Purchase (Ost) |
60 |
|
80 |
22.01.2006 |
Supplier D (Hankija D) |
Purchase (Ost) |
75 |
|
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
125 |
|
81 |
22.01.2006 |
Supplier A (Hankija A) |
Purchase (Ost) |
200 |
|
7 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
51 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
80 |
20.01.2006 |
Company D (Ettevõte D) |
Sale (Müük) |
10 |
|
34 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
100 |
|
80 |
15.01.2006 |
Company AA (Ettevõte AA) |
Sale (Müük) |
30 |
Teine võimalus sama tulemuse saamiseks on lisada ühispäringu päringute ette veel üks päring:
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
Iga välja korral tagastab Access fikseeritud väärtused, millel on teie määratletud andmetüüp. Kuna te ei soovi, et selle päringu väljund hakkaks tulemusi segama, tuleks siin lisada WHERE-klausel väärtusele False (Väär):
WHERE False
Kuna see väärtus on alati väär, ei tagasta see päring midagi. Selle lause kombineerimine olemasoleva SQL-iga annabki meile tulemuseks lõpliku lause:
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;
Märkus.: Meie näite kombineeritud päring, mis kasutab Northwindi andmebaasi, tagastab 100 kirjet; kaks individuaalset päringut seevastu tagastavad 58 ja 43 kirjet ehk kokku 101 kirjet. Lahknevuse põhjustab asjaolu, et kaks kirjet pole kordumatud. Jaotisest Eristatavate kirjetega töötamine ühispäringutes, kasutades võtmesõna UNION ALL saate teada, kuidas seda stsenaariumi lahendada, kasutades võtmesõna UNION ALL.
Ühispäringus saab kombineerida kirjekomplekti ühe kirjega, mis sisaldab ühe või mitme välja summat.
Siin on järgmine näide, mille saate ise Northwindi näidisandmebaasis luua ja mis annab teile aimu sellest, kuidas ühispäringus summat leida.
-
Looge uus lihtpäring õlleostude vaatamiseks (Northwindi andmebaasis Product ID=34), kasutades järgmist SQL-i süntaksit:
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];
-
Aktiveerige andmelehevaade. Peaksite nägema nelja ostu:
Date Received (Vastu võetud)
Quantity (Kogus)
22.01.2006
100
22.01.2006
60
04.04.2006
50
05.04.2006
300
-
Summa leidmiseks looge lihtne kokkuvõttepäring, kasutades järgmist SQL-i:
SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity FROM [Purchase Order Details] WHERE ((([Purchase Order Details].[Product ID])=34))
-
Aktiveerige andmelehevaade. Peaksite nägema ainult ühte kirjet:
MaxOfDate Received
SumOfQuantity
05.04.2006
510
-
Kombineerige need kaks päringut ühispäringuks, et lisada kogukogusega kirje ostukirjetele:
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];
-
Aktiveerige andmelehevaade. Peaksite nägema nelja ostu; iga ostu summale peaks järgnema koguse kokkuvõttega kirje:
Date Received (Vastu võetud)
Quantity (Kogus)
22.01.2006
60
22.01.2006
100
04.04.2006
50
05.04.2006
300
05.04.2006
510
Kokkuvõtete või summade lisamine ühispäringusse käibki põhimõtteliselt nii. Kui soovite kokkuvõttekirje muudest kirjetest visuaalselt eristada, võite mõlemasse päringusse kaasata fikseeritud väärtused, näiteks „Detail“ (Üksikasi) ja „Total“ (Kokkuvõte). Fikseeritud väärtuste kasutamisest leiate ülevaate jaotisest Kolme või enama tabeli või päringu kombineerimine ühispäringuks.
Vaikimisi kaasatakse Accessis ühispäringutesse ainult eristatavad kirjed. Mida aga teha siis, kui soovite kaasata kõik kirjed? Siin võib abil olla teisest näitest.
Eelmises jaotises näitasime teile, kuidas luua ühispäringus kokkuvõte. Muutke selle ühispäringu SQL-i, et kaasata 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];
Aktiveerige andmelehevaade. Peaksite nägema mõnevõrra eksitavat tulemust:
Date Received (Vastu võetud) |
Quantity (Kogus) |
22.01.2006 |
100 |
22.01.2006 |
200 |
Muidugi ei tagasta üks kirje kokkuvõttes kahekordset kogust.
Põhjus, miks seda tulemust näete, on see, et ühel päeval müüdi sama šokolaadikogus kaks korda, nagu on kirjas tabelis Ostutellimuse üksikasjad. Siin on lihtne valikupäringu tulem, mis näitab mõlemat Northwindi näidisandmebaasi kirjet:
Purchase Order ID (Ostutellimuse ID) |
Product |
Quantity |
100 |
Northwind Traders Chocolate |
100 |
92 |
Northwind Traders Chocolate |
100 |
Nagu näete, ei sisalda varem loodud ühispäring välja „Purchase Order ID“ ning need kaks välja ei moodusta kahte eristatavat kirjet.
Kui soovite kaasata kõik kirjed, kasutage oma SQL-is võtmesõna UNION asemel võtmesõna UNION ALL. Kuna see mõjutab arvatavasti tulemite sortimist, võiksite sortimisjärjestuse määratlemiseks kaasa ka klausli ORDER BY. Eelmisel näitel põhinev muudetud SQL on järgmine:
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];
Aktiveerige andmelehevaade. Peaksite nägema kõiki üksikasju ja lisaks viimase kirjena ka kokkuvõtet:
Date Received (Vastu võetud) |
Kokku |
Quantity (Kogus) |
22.01.2006 |
100 |
|
22.01.2006 |
100 |
|
22.01.2006 |
Total (Kokku) |
200 |
Sageli kasutatakse ühispäringuid vormil liitboksi juhtelemendi kirjeallikana. Selle liitboksi kaudu saate valida vormi kirjete filtreerimiseks soovitud väärtuse. Näiteks saate töötajakirjeid linna alusel filtreerida.
Kui soovite näha, kuidas see töötab, on siin järgmine näide, mille saate selle stsenaariumi illustreerimiseks ise Northwindi näidisandmebaasis luua.
-
Looge lihtne valikupäring, kasutades seda SQL-i süntaksit:
SELECT Employees.City, Employees.City AS Filter FROM Employees;
-
Aktiveerige andmelehevaade. Peaksite nägema järgmist tulemust:
City (Linn)
Filter
Seattle
Seattle
Bellevue
Bellevue
Redmond
Redmond
Kirkland
Kirkland
Seattle
Seattle
Redmond
Redmond
Seattle
Seattle
Redmond
Redmond
Seattle
Seattle
-
See pilt ei pruugi teile anda eriti väärtuslikku teavet. Päringut saate aga laiendada ja teisendada selle ühispäringuks, kasutades järgmist SQL-i:
SELECT Employees.City, Employees.City AS Filter FROM Employees UNION SELECT "<All>", "*" AS Filter FROM Employees ORDER BY City;
-
Aktiveerige andmelehevaade. Peaksite nägema järgmist tulemust:
City (Linn)
Filter
<All>
*
Bellevue
Bellevue
Kirkland
Kirkland
Redmond
Redmond
Seattle
Seattle
Access liidab kõik üheksa varem kuvatud kirjet, kasutades fikseeritud väljaväärtusi <All> ja „*“.
Kuna see ühendamisklausel ei sisalda võtmesõna UNION ALL, tagastab Access ainult eristatavad kirjed, mis tähendab, et iga linn tagastatakse fikseeritud samaste väärtuste korral ainult üks kord.
-
Nüüd, kui teil on olemas lõpetatud ühispäring, kus iga linna nimi kuvatakse ainult üks kord ja mis sisaldab ka kõigi linnade valimise võimalust, saategi seda päringut kasutada vormi liitboksi kirjeallikana. Seda konkreetset näidet mudelina kasutades võiksite vormil luua liitboksi juhtelemendi, määrata selle päringu juhtelemendi kirjeallikaks, määrata veeru Filter atribuudi Column Width (Veeru laius) väärtuseks 0 (null), et see visuaalselt peita, ja seejärel määrata atribuudi Bound Column (Seotud veerg) väärtuseks 1, et osutada teise veeru indeksile. Vormi enda atribuudis Filter saate seejärel lisada näiteks järgmise koodi, et aktiveerida vormifilter liitboksi juhtelemendis valitud väärtuse abil:
Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'" Me.FilterOn = True
Vormi kasutaja saab seejärel vormikirjeid filtreerida, et kuvada kindel linn, või valida <All> (Kõik) kõigi linnade kirjete loetlemiseks.