Kokkuvõtlike andmete lugemise hõlbustamine ristpäringu abil

Ristpäringut Kujundusvaates luues saate kasutada ükskõik kui mitut kirjeallikat (tabeleid ja päringuid). Kuid saate hoida kujunduse lihtsa, luues esmalt valikupäringu, mis tagastab kõik soovitud andmed, ja seejärel kasutada seda päringut ristpäringu ainukese kirjeallikana. Lisateavet valikupäringu loomise kohta leiate peatükist Vt ka.

Ristpäringu koostamisel Kujundusvaates tuleb kujundusruudustiku ridade Kokku ja Ristpäring abil määrata, millistest väljaväärtustest saavad veerupäised, millistest reapäised ja milliseid välju soovite summeerida, milliste keskmist arvutada, milliseid loendada või muul viisil arvutada.

1. Nende ridade sätted määratlevad, kas väli on reapäis, veerupäis või kokkuvõtlik väärtus.

2. See säte kuvab välja väärtused reapäistena.

3. See säte kuvab välja väärtused veerupäistena.

4. Need sätted loovad kokkuvõtlikud väärtused.

Päringu loomine

1. Klõpsake menüü Loo jaotises Päringud nuppu Päringu kujundus.

   

2. Topeltklõpsake dialoogiboksis Tabeli kuvamine igat tabelit või päringut, mida soovite kasutada kirjeallikana.

   Kui kasutate mitut kirjeallikat, veenduge, et tabelite või päringute ühised väljad oleks ühendatud. Tabelite ja päringute ühendamise kohta leiate lisateavet jaotisest Vt ka.

3. Sulgege dialoogiboks Tabeli kuvamine.

4. Klõpsake menüü Kujundus jaotises Päringutüüp nuppu Ristpäring.

5. Topeltklõpsake päringu kujundusaknas igat välja, mida soovite kasutada reapäiste allikana. Reapäiste jaoks saate valida kuni kolm välja.

6. Valige päringu kujundusruudustikus real Ristpäring iga reapäise välja puhul väärtus Reapäis.

   Rea Kriteeriumid tulemite piiramiseks saate sisestada sellele reale avaldise. Välja sortimisjärjestuse saate määrata rea Sordi abil.

7. Topeltklõpsake päringu kujundusaknas välja, mida soovite kasutada veerupäiste allikana. Veerupäiste jaoks saate valida ainult ühe välja.

8. Valige päringu kujundusruudustikus real Ristpäring veerupäise välja puhul väärtus Veerupäis.

   Veerupäise välja tulemite piiritlemiseks saate sisestada reale Kriteeriumid avaldise. Kriteeriumiavaldise kasutamine veerupäise väljal ei piira siiski ristpäringuga tagastatavate veergude arvu. See piirab hoopis andmeid sisaldavate veergude arvu. Oletame näiteks, et teil on veerupäise väli, millel on kolm võimalikku väärtust: punane, roheline ja sinine. Kui rakendate veerupäise väljale kriteeriumi ='sinine', kuvab ristpäring ikkagi punase ja rohelise veeru, kuid ainult sinine veerg sisaldab andmeid.

   Kui soovite piirata veerupäistena kuvatavate väärtuste arvu, saate päringu atribuudi Veerupäised abil määrata fikseeritud väärtuste loendi. Lisateavet leiate järgmisest peatükist.

9. Topeltklõpsake päringu kujundusaknas välja, mille abil soovite arvutada kokkuvõtlikud väärtused. Kokkuvõtlike väärtuste jaoks saate valida ainult ühe välja.

10. Valige päringu kujundusruudustiku kokkuvõtlike väärtuste välja real Kokku kokkuvõttefunktsioon, mille abil neid väärtusi arvutada.

11. Valige kokkuvõtlike väärtuste välja real Ristpäring väärtus Väärtus.

    Te ei saa määrata kokkuvõtlike väärtuste välja kriteeriume ega seda välja sortida.

12. Klõpsake menüü Kujundus jaotises Tulemid nuppu Käivita.

Veerupäiste fikseeritud väärtuste määramine

Kui soovite määrata veerupäistena kasutatavad fikseeritud väärtused, saate seada päringu atribuudi Veerupäised.

1. Avage ristpäring kujundusvaates.

2. Kui atribuudilehte pole näha, vajutage selle kuvamiseks klahvi F4.

3. Veenduge, et atribuudilehe vahekaardi Üldist sätte Valiku tüüp väärtus on Päringu atribuudid. Kui see pole nii, klõpsake päringu kujundusruudustiku kohal tühja ala.

4. Sisestage atribuudilehe vahekaardi Üldist atribuudi Veerupäised väärtusena nende komaga eraldatud väärtuste loend, mida soovite kasutada veerupäistena.

   Mõni märk (nt enamik kirjavahemärke) pole veerupäistes lubatud. Kui kasutate oma väärtusteloendis neid märke, asendab Access iga sellise märgi allkriipsuga (_).

SQL-süntaks ristpäringule

Ristpäringut esitatakse SQL-is kui TRANSFORM-lauset. TRANSFORM-lausel on järgmine süntaks:

TRANSFORM aggfunction
selectstatement
PIVOT-liigendtabeliväli [IN (väärtus1[, väärtus2[, ...]])]

Lause TRANSFORM koosneb järgmistest osadest.

Osa	Kirjeldus
kokkuv.funktsioon	SQL-i kokkuvõttefunktsioon, mida rakendatakse valitud andmetele.
select-lause	SELECT-lause.
liigendväli	Väli või avaldis, mida soovite kasutada päringu tulemikomplekti veerupäiste loomiseks.
väärtus1, väärtus2	Fikseeritud väärtused veerupäiste loomiseks.

SQL-i vaade ei piira tabelite või päringute arvu, mida kasutate ristpäringu kirjeallikatena. Kuid saate aidata hoida kujunduse lihtsa, luues valikupäringu, mis tagastab kõik soovitud andmed, mida soovite kasutada ristpäringus, ja seejärel kasutada seda valikupäringut kirjeallikana. Lisateavet valikupäringu loomise kohta leiate peatükist Vt ka.

1. Klõpsake menüü Loo jaotises Muu nuppu Päringukujundus.

2. Sulgege dialoogiboks Tabeli kuvamine.

3. Klõpsake menüü Kujundus jaotise Vaated nuppu Vaade ning seejärel käsku SQL-i vaade.

4. Tippige või kopeerige menüüs SQL-i objekt järgmine SQL:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Tippige esimesel real, pärast TRANSFORM-i avaldis, mille abil arvutada kokkuvõtlikud väärtused (nt Sum([Kogus]).

   Kui kasutate kirjeallikana rohkem kui üht tabelit või päringut, kaasake tabeli või päringu nimi iga väljanime osana (nt Sum([Kulu].[Summa]).

6. Tippige teisel real pärast SELECT-i väljade loend või välja avaldised, mida soovite reapäistes kasutada. Eraldage loendiüksused komadega, nt [Eelarve].[Osakonna_ID], [Kulu].[Tüüp].

7. Tippige kolmandas reas pärast FROM-i tabelite või päringute loend, mida kirjeallikana kasutate, nt Eelarve, Kulu.

8. Tippige neljandas reas pärast GROUP BY-d sama väljade loend, mida kasutasite SELECT-klauslis (6. juhises).

9. Tippige viiendas reas pärast PIVOT-i välja nimi või avaldis, mida soovite veerupäistes kasutada, nt PIVOT [Eelarve].Aasta].

Mõnikord soovite selle asemel, et kasutada rea- või veerupäistes iga välja väärtust, rühmitada välja andmeväärtused vahemikesse ja seejärel kasutada rea- või veerupäistes neid vahemikke. Oletame näiteks, et kasutate veerupäistes välja „Vanus“. Selle asemel, et kasutada iga vanuse jaoks eraldi veergu, on soovitatav kasutada vanusevahemikke tähistavaid veerge.

Rea- või veerupäistes kasutatavate vahemike loomiseks saate avaldises kasutada funktsiooni IIf.

1. Avage ristpäring kujundusvaates.

2. Paremklõpsake päringu kujundusruudustiku real Väli tühja veergu ja seejärel klõpsake kiirmenüü käsku Suum.

3. Tippige väljale Suum välja pseudonüüm ja selle järele koolon (:).

4. Tippige tekst IIf().

5. Tippige tekstile IIf järgneva sulu sisse võrdlusavaldis, mis määratleb väljaväärtuste esimese vahemiku.

   Oletame näiteks, et loote vahemikke välja Vanus jaoks ja soovite iga vahemiku ulatuseks 20 aastat. Esimese vahemiku võrdlusavaldis on [Vanus]<21.

6. Tippige võrdlusavaldise järele koma ja seejärel jutumärkidesse vahemiku nimi. Teie sisestatud nimi on selle vahemiku väärtuste ristpäringu päis.

   Näiteks tippige avaldise [Vanus]<21 järele koma ja seejärel tekst "0–20 aastat".

7. Tippige vahemiku nime järele (väljapoole jutumärke) koma ja seejärel tehke ühte järgmistest toimingutest.

   • Järgmise vahemiku loomiseks tippige tekst IIf() ja seejärel korrake 5., 6. ja 7. juhise toiminguid.

   • Viimase vahemiku puhul tippige ainult vahemiku nimi.

     Näiteks täielik pesastatud IIf-avaldis, mis eraldab välja Vanus 20 aasta pikkusteks vahemikeks, võiks välja näha järgmine (loetavuse parandamiseks on lisatud reapiirid):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Märkus.: Avaldise hindamisel peatab Access hindamise niipea, kui mõni funktsiooni IIf lausetest hinnatakse tõeseks. Te ei pea määrama iga vahemiku alampiiri, kuna kõik väärtused, mis jäävad antud vahemiku alampiirist allapoole, oleks nagunii hinnatud tõesteks.

8. Valige päringu kujundusruudustiku real Kokku väärtus Rühmitusalus.

9. Määrake real Ristpäring, kas soovite kasutada vahemikke rea- või veerupäistena. Arvestage, et saate määrata üks kuni kolm reapäist ja ühe veerupäise.

Mõnikord on kasulik, kui ristpäring küsib käivitamisel sisendit. Oletame näiteks, et kasutate mitut reapäist, millest üks on Riik/regioon. Selle asemel, et kuvada alati iga riigi või regiooni andmed, võib seada päringu küsima nime ja seejärel kuvama andmed vastavalt kasutaja sisestatud teabele.

Parameetriviiba saate lisada mis tahes reapäiseväljale.

1. Avage ristpäring Kujundusvaates.

2. Tippige selle reapäisevälja reale Kriteeriumid, mille puhul on vaja kasutajasisendit, nurksulgudes küsimuse tekst. Küsimuse tekst kuvatakse päringu käivitamisel viibana.

   Näiteks kui tipite reale Kriteeriumid teksti [Milline riik või regioon?], kuvatakse päringu käivitamisel dialoogiboks, mis sisaldab küsimust "Milline riik või regioon?", sisestusvälja ja nuppu OK.

   Näpunäide.: Kui soovite, et teie parameeter oleks paindlik, ühendage oma avaldis tehtemärgi Like abil metamärkidega. Näiteks kriteeriumi [Milline riik või regioon?] kasutamise asemel võite kasutada parameetrit Like [Milline riik või regioon?]&"*", et see vastaks suuremale hulgale võimalikele tulemitele. Tehtemärgi Like kasutamine ei muuda parameetriviiba ilmet.

3. Klõpsake menüü Kujundus jaotises Kuva/peida nuppu Parameetrid.

4. Sisestage dialoogiboksi Päringu parameetrid veergu Parameetrid sama parameetriviip, mida kasutasite real Kriteeriumid. Kaasake nurksulud, kuid ärge kaasake ühendatud metamärke ega tehtemärki Like.

5. Valige veerust Andmetüüp parameetri andmetüüp. Andmetüüp peaks kattuma reapäise välja andmetüübiga.

Kui väli, mille abil arvutate oma ristpäringu kokkuvõtlikke väärtusi, sisaldab tühiväärtusi, siis kõik kokkuvõttefunktsioonid ignoreerivad neid väärtusi. Mõne kokkuvõttefunktsiooni puhul võib see mõjutada tulemeid. Näiteks keskmise arvutamiseks tuleb liita kõik väärtused ja jagada tulem väärtuste arvuga. Kui väli sisaldab tühiväärtusi, siis neid väärtusi ei loeta väärtuste arvu osaks.

Mõnel juhul on soovitatav asendada kõik tühiväärtused nullidega, et need kaasataks kokkuvõtlikesse arvutustesse. Tühiväärtused saate nullidega asendada funktsiooni Nz abil.

Funktsiooni Nz süntaks

Nz ( variant [, valueifnull ] )

Funktsiooni Nz süntaksil on järgmised argumendid.

Argument	Kirjeldus
Variant	Nõutav. Muutuja andmetüüp Variant.
valueifnull	Valikuline (v.a juhul, kui seda kasutatakse päringus). Variant, mis pakub tagastatava väärtuse, kui argument variant on Null. See argument võimaldab tagastada mõne muu väärtuse peale nulli või nullpikkusega stringi. Märkus.: Kui kasutate funktsiooni Nz päringu avaldises argumendita valueifnull, kuvatakse selle tulemusel tühiväärtusi sisaldavatel väljadel nullpikkusega string.

1. Paremklõpsake kujundusvaates avatud päringu kujundusruudustikus välja Väärtus.

2. Klõpsake kiirmenüü käsku Suum.

3. Ümbritsege väljal Suum väljanimi või avaldis sulgudega ja seejärel tippige sulgude ette tekst Nz.

4. Tippige parema sulu sisse väärtus , 0.

   Näiteks kui kasutasite funktsiooni Nz väljal nimega Kaotsiläinud tunnid, et muuta tühiväärtused nullideks, näeb teie lõpetatud avaldis välja järgmine:

Nz([Hours Lost], 0)

• Ristpäring võiks olla võimalikult lihtne.    Kui reakombinatsioonide arv kasvab, võib ristpäringute lugemine muutuda raskeks. Ärge kasutage rohkem reapäiseid kui vaja.

• Kaaluge ristpäringu koostamist etappidena.    Ärge piirduge ainult tabelite kasutamisega. Sageli on võimalik alustada kokkuvõttepäring koostamisest ja seejärel kasutada seda päringut ristpäringu kirjeallikana.

• Valige veerupäise välja hoolikalt.    Ristpäringu andmelehti on seda hõlpsam lugeda, mida väiksem on veerupäiste arv. Kui olete otsustanud, milliseid välju soovite kasutada päistena, võiksite veerupäiste alusena kasutada välju, millel on vähim erinevaid väärtusi. Näiteks kui teie päring arvutab väärtuse vanuse ja soo järgi, on soovitatav kasutada veerupäiste alusena sugu, mitte vanust, kuna sool on tavaliselt vähem võimalikke väärtusi kui vanusel.

• WHERE-klauslis alampäringu kasutamine    Saate kasutada ristpäringus alampäringut WHERE-klausli osana.

Lehe algusse