Mõnikord võib juhtuda, et soovite ühe tabeli või päringu kirjed kombineerida ühe või mitme muu tabeli kirjetega üheks tulemiks. Seda ühispäring Accessis teebki.
Ü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õib abi olla töötava näite uurimisest Northwindi Accessi mallis. Näidismalli Põhjatuule saate otsida Accessi alustuslehelt, valides käsu Fail>uus. Koopia saate alla laadida ka otse Northwindi näidismallilt.
Pärast seda, kui Access avab Northwindi andmebaasi, sulgege esmalt sisselogimise dialoogiboks ja laiendage seejärel navigeerimispaan. Kõigi andmebaasiobjektide korraldamiseks tüübi järgi valige navigeerimispaani ülaserv ja seejärel valige Objekti tüüp . Järgmisena laiendage jaotist Päringud ja 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. See tähendab, et ühispäringute koostamiseks ega redigeerimiseks ei saa kasutada Accessi graafilist päringukoosturit. Kui avate navigeerimispaanil ühispäringu, avab Access selle ja kuvab tulemid andmelehevaates. Pange tähele, et menüü Avaleht jaotises Vaated pole ühispäringutega töötamisel kujundusvaade saadaval. Saate aktiveerida ainult andmelehevaate ja SQL-i vaate.
Ühispäringu näite uurimise jätkamiseks klõpsake selle määratleva SQL süntaksi vaatamiseks nuppu Home Views SQL View (Avakuva>>SQL-i vaade). Sellel joonisel oleme lisanud lisavahed SQL , et saaksite hõlpsalt vaadata erinevaid osi, mis moodustavad ühispäringu.
Vaatame SQL üksikasjalikult selle Northwindi andmebaasi ühispäringu süntaksit.
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 UNION märksõna, mis käsib Accessis need kaks kirjekomplekti kombineerida.
Selle SQL lause viimane osa määratleb ühendatud kirjete järjestuse lause abil ORDER BY . Selles näites järjestab Access kõik kirjed välja Tellimuse kuupäev 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
Kuigi ühispäringu saate luua süntaksi otse SQL-i vaates kirjutadesSQL, võib teil olla lihtsam seda valikupäringute abil osadena koostada. 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 ühendada. Selles etapis tuleb ühispäringu loomiseks laused kopeerida ja kleepida SQL .
- Klõpsake menüü Loo jaotises Päringud nuppu Päringu kujundus.
- Klõpsake menüü Kujundus jaotises Päring nuppu Ühispäring. Access peidab päringu kujundusakna ja kuvab SQL-i vaate objekti vahekaardi. Praegusel hetkel on vahekaart tühi.
- Klõpsake esimese valikupäringu vahekaarti, mida soovite ühispäringusse liita.
- Klõpsake menüüs Avaleht nuppu Kuva SQL-i>vaade.
- Kopeerige
SQLvalikupäringu lause. Klõpsake varem looma hakatud ühispäringu vahekaarti. -
SQLKleepige valikupäringu lause ühispäringu SQL-i vaate objekti vahekaardile. - Kustutage valikupäringu
SQLlause lõpust semikoolon (;). - Kursori ühe rea võrra alla viimiseks vajutage sisestusklahvi (Enter) ja tippige
UNIONseejärel uuele reale. - Klõpsake järgmise valikupäringu vahekaarti, mida soovite ühispäringusse liita.
- Korrake juhiseid 5–10, kuni olete kopeerinud ja kleepinud kõik
SQLvalikupäringute laused ühispäringu SQL-i vaate aknasse. Ärge kustutage semikoolonit ega tippige midagi viimase valikupäringu lausele järgnevatSQL. - 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 liigne semikoolon ja lisage
UNIONmärksõna. Seejärel saate tulemeid vaadata andmelehevaates.Lisage ühte päringusse järjestusklausel ja kleepige
ORDER BYlause SQL-i vaates ühispäringusse. 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 ühendab ja sordib selle ühispäringu näite nimesid, 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 olete väga tuttav kirjutamissüntaksiga SQL , saate ühispäringu jaoks kirjutada oma SQL lause otse SQL-i vaates. 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.
Kolme või enama tabeli või päringu kombineerimine ühispäringuks
Eelmise jaotise näites, mis kasutab Northwindi andmebaasi, kombineeritakse 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 tulemit andmelehevaates, loetletakse kõik töötajad näidisettevõtte nimega, mis tõenäoliselt pole eriti kasulik. Kui soovite, et see väli näitaks, kas isik on ettevõttesisene töötaja, tarnija või kliendi töötaja, saate ettevõtte nime asemel kaasata fikseeritud väärtuse .
SQL Välja näeb välja 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:
|
|
|
|
|---|---|---|
| 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 |
Päringut saate veelgi vähendada, kuna Access loeb väljundväljade nimed ette ainult ühispäringu esimesest päringust. Siin eemaldatakse teise ja kolmanda päringujaotise väljund:
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];
Filtreerimine ühispäringutes
Accessi ühispäringus on järjestamine lubatud ainult üks kord, kuid saate iga päringut ükshaaval filtreerida. Võttes aluseks eelmise jaotise ühispäringu, on siin näide, mis filtreerib iga päringut klausli lisamise WHERE teel.
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:
|
|
|
|
|---|---|---|
| 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 |
Erinevate andmetüüpide kasutamine
Kui ühispäringud on väga erinevad, võib tekkida olukord, kus väljundväli peab kombineerima eri andmetüüpide 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:
|
|
|
|
|
|
|---|---|---|---|---|
| 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 tükeldada välja Kogus kaheks väljaks: Osta ja Müü. Oletame ka, et soovite väärtuseta välja jaoks fikseeritud nullväärtust.
SQL Ühispäring näeb välja selline:
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:
|
|
|
|
|
|
|
|---|---|---|---|---|---|
| 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äärtustega väljad oleksid tühjad? Võtmesõna lisamisega Null saate muuta SQL nulli asemel mitte millegi kuvamise võimalust, nagu on näidatud siin.
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:
|
|
|
|
|
|
|
|---|---|---|---|---|---|
| 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 juhtub siis, kui proovite lisada tühja stringi väljade tühja väärtuse jaoks? See SQL katse võib välja näha selline:
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.
|
|
|
|
|
|
|
|---|---|---|---|---|---|
| 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?
Üks lahendus on sundida päringut eeldama, et välja väärtus on arv. Seda saate teha järgmise avaldisega.
IIf(False, 0, Null)
Kontrollitav Falsetingimus pole kunagi True, seega tagastab avaldis alati väärtuse Null. Siiski hindab Access mõlemat väljundi suvandit ja käsitleb väljundit arvulisena või 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;
Teist päringut pole vaja muuta.
Kui aktiveerite andmelehevaate, näetegi nüüd soovitud tulemust:
|
|
|
|
|
|
|
|---|---|---|---|---|---|
| 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
See on väike trikk. Kuna tingimus on alati väär, ei tagasta 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.
Selles näites tagastab Northwindi andmebaasi kombineeritud päring 100 kirjet, samas kui kaks individuaalset päringut tagastavad 58 ja 43 kirjet kokku 101 kirje kohta. See erinevus ilmneb seetõttu, et kaks kirjet pole kordumatud. Lisateavet selle stsenaariumi lahendamise kohta funktsiooni abil UNION ALLleiate teemast Eristatavate kirjetega töötamine ühispäringutes, kasutades funktsiooni UNION ALL.
Summade liitmine ühispäringus
Ühispäringu erikasutus on kirjekomplekti kombineerimine ü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. Samuti võite soovida mõlemasse päringusse kaasata fikseeritud väärtusi, näiteks "Detail" (Üksikasjad) ja "Total" (Kogusumma), et kogusummakirjed muudest kirjetest visuaalselt eraldada. Fikseeritud väärtuste kasutamisest leiate ülevaate jaotisest Kolme või enama tabeli või päringu kombineerimine ühispäringuks.
Eristatavate kirjetega töötamine ühispäringutes, kasutades võtmesõna UNION ALL
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 seda ühispäringu SQL kaasamiseks 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:
|
|
|
|---|---|
| 22.01.2006 | 100 |
| 22.01.2006 | 200 |
Loomulikult ei tagasta üks kirje kogukogust kaks korda.
Näete seda tulemust, sest ühel päeval müüdi sama šokolaadikogust kaks korda, nagu on kirjas tabelis Ostutellimuse üksikasjad. Siin on lihtne valikupäringu tulem, mis näitab mõlemat Põhjatuule näidisandmebaasi kirjet:
|
|
|
|
|---|---|---|
| 100 | Northwind Traders Chocolate | 100 |
| 92 | Northwind Traders Chocolate | 100 |
Varem märgitud ühispäringus näete, et väli Ostutellimuse ID pole kaasatud ja et need kaks välja ei moodusta kahte eraldi kirjet.
Kui soovite kaasata kõik kirjed, kasutage UNION ALL i SQLasemel UNION i. Tõenäoliselt mõjutab see tulemite sortimist, seega võiksite sortimisjärjestuse määramiseks lisada ORDER BY ka klausli. Eelmise näite põhjal on muudetud SQL järgmised muudatused.
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:
|
|
|
|
|---|---|---|
| 22.01.2006 | 100 | |
| 22.01.2006 | 100 | |
| 22.01.2006 | Total (Kokku) | 200 |
Ühispäringu kasutamine vormi kirjete filtreerimiseks liitboksi juhtelemendi kaudu
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 järgmise
SQLsüntaksi abil: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äringu laiendamiseks ja ühispäringuks teisendamiseks tehke järgmist
SQL.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 <Kõik> * Bellevue Bellevue Kirkland Kirkland Redmond Redmond Seattle Seattle Access ühendab üheksa varem kuvatud kirjet fikseeritud väljaväärtustega <Kõik> ja "*". Kuna see ühisklausel ei sisalda
UNION ALL, tagastab Access ainult eristatavad kirjed. See tähendab, et iga linn tagastatakse ainult üks kord fikseeritud identsete väärtustega.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.
FilterSeejärel saate vormi enda atribuudile liitboksi juhtelemendis valitud väärtuse abil vormifiltri aktiveerimiseks lisada järgmise koodi:Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'" Me.FilterOn = TrueSeejärel saab vormi kasutaja filtreerida vormikirjed kindla linna nime järgi või valida kõigi linnade kõigi kirjete loetlemiseks käsu <Kõik> .