Zlepšenie čitateľnosti súhrnných údajov pomocou krížového dotazu

Použitím návrhového zobrazenia pri vytváraní krížového dotazu môžete použiť neobmedzený počet zdrojov záznamov (tabuľky a dotazy). Jednoduchosť návrhu však môžete zachovať vytvorením najprv dotazu Select, ktorý vráti všetky požadované údaje, a následným použitím tohto dotazu, ako jediného zdroja záznamu pre krížový dotaz. Ďalšie informácie o vytváraní krížového dotazu nájdete v časti Pozrite tiež.

Keď vytvárate krížový dotaz v návrhovom zobrazení, používate pri tom riadky Celkom a Krížová tabuľka v mriežke návrhu. Pomocou týchto riadkov určíte, hodnoty ktorých polí sa použijú ako hlavičky stĺpcov, hlavičky riadkov a pre ktoré hodnoty polí sa vypočíta súčet, počet alebo iná funkcia.

1. Nastavenia v týchto riadkoch určujú, či sa stĺpec použije pre hlavičky riadkov, hlavičky stĺpcov alebo pre súhrnné hodnoty.

2. Toto nastavenie zobrazuje hodnoty poľa ako hlavičky riadkov.

3. Toto nastavenie zobrazuje hodnoty poľa ako hlavičky stĺpcov.

4. Tieto nastavenia vracajú súhrnné hodnoty.

Vytvorenie dotazu

1. Na karte Vytvoriť kliknite v skupine Dotazy na položku Návrh dotazu.

   

2. V dialógovom okne Zobrazenie tabuľky kliknite dvakrát na všetky tabuľky a dotazy, ktoré chcete použiť ako zdroj záznamov.

   Ak používate viac zdrojov záznamov, uistite sa, že sú tabuľky alebo dotazy spojené prostredníctvom polí, ktoré majú spoločné. Ďalšie informácie o spájaní tabuliek a dotazov nájdete prostredníctvom časti Pozrite tiež.

3. Zatvorte dialógové okno Zobrazenie tabuľky.

4. Na karte Návrh v skupine Typ dotazu kliknite na položku Krížový.

5. V okne návrhu dotazu kliknite dvakrát na každé pole, ktoré chcete použiť ako zdroj pre hlavičky riadkov. Môžete vybrať najviac tri polia.

6. V mriežke návrhu dotazu vyberte v riadku Krížová tabuľka položku Záhlavie riadka pre každé pole, ktoré ste pridali ako hlavičku riadkov.

   Do riadka Kritériá môžete zadať výraz na obmedzenie výsledkov pre toto pole. Môžete použiť aj riadok Zoradiť a zadať spôsob zoradenia pre dané pole.

7. V okne návrhu dotazu kliknite dvakrát na pole, ktoré chcete použiť ako zdroj pre hlavičky stĺpcov. Môžete vybrať len jedno pole.

8. V mriežke návrhu dotazu vyberte pre toto pole v riadku Krížová tabuľka položku Záhlavie stĺpca.

   Do riadka Kritériá môžete zadať výraz na obmedzenie výsledkov pre pole hlavičiek stĺpcov. Zadanie výrazu pre kritériá však v prípade poľa hlavičiek stĺpcov neobmedzí počet stĺpcov, ktoré vráti krížový dotaz. Namiesto toho určí, ktoré stĺpce budú obsahovať údaje. Predpokladajme napríklad, že máte pole hlavičiek stĺpcov, ktoré môže obsahovať tri hodnoty: červená, zelená a modrá. Ak pre pole hlavičiek stĺpcov použijete kritérium ='modrá', krížový dotaz bude aj naďalej zobrazovať stĺpce pre červenú a zelenú, ale len stĺpec pre hodnotu „modrá“ bude obsahovať údaje.

   Ak chcete určiť, ktoré hodnoty sa budú zobrazovať ako záhlavia stĺpcov, zadajte zoznam pevných hodnôt pomocou vlastnosti dotazu Záhlavia stĺpcov. Ďalšie informácie nájdete v nasledujúcej časti.

9. V okne návrhu dotazu dvakrát kliknite na pole, ktoré chcete použiť na výpočet súhrnných hodnôt. Na výpočet súhrnných hodnôt môžete použiť len jedno pole.

10. V mriežke návrhu dotazu vyberte v riadku Celkom poľa pre súhrnné hodnoty agregačnú funkciu, ktorá sa má použiť na výpočet hodnôt.

11. V riadku Krížová tabuľka poľa pre súhrnné hodnoty vyberte položku Hodnota.

    Do poľa súhrnných hodnôt môžete zadať kritériá určujúce spôsob zoradenia.

12. Prejdite na kartu Návrh a v skupine Výsledky kliknite na položku Spustiť.

Zadanie pevných hodnôt pre záhlavia stĺpcov

Ak chcete zadať pevné hodnoty, ktoré sa budú používať ako hlavičky stĺpcov, môžete na to použiť vlastnosť dotazu Záhlavia stĺpcov.

1. Otvorte krížový dotaz v návrhovom zobrazení.

2. Ak sa nezobrazuje hárok vlastností, stlačte kláves F4.

3. V hárku vlastností (hneď nad kartou Všeobecné) skontrolujte, či je Typ výberu nastavený na hodnotu Dotaz – vlastnosti. Ak nie, kliknite na prázdne miesto nad mriežkou návrhu dotazu.

4. V hárku vlastností na karte Všeobecné zadajte do vlastnosti Záhlavia stĺpcov zoznam hodnôt, ktoré chcete použiť ako hlavičky stĺpcov. Hodnoty oddeľte čiarkami.

   Záhlavia stĺpcov nepodporujú niektoré znaky (napríklad väčšinu interpunkčných znamienok). Ak v zozname hodnôt použijete tieto znaky, Access ich nahradí znakom podčiarknutia (_).

Syntax SQL pre krížový dotaz

Krížový dotaz je v jazyku SQL vyjadrený ako príkaz TRANSFORM. Príkaz TRANSFORM má nasledujúcu syntax:

Transformovať aggfunction
selectstatement
Pivotfield [IN (hodnota1[, hodnota2[, ...]])]

Príkaz TRANSFORM pozostáva z týchto častí:

Časť	Popis
aggfunction	Agregačná funkcia SQL, ktorá pracuje s vybratými údajmi.
príkazselect	Príkaz SELECT.
pivotfield	Pole alebo výraz, ktorý chcete použiť na vytvorenie záhlaví stĺpcov v množine výsledkov dotazu.
hodnota1, hodnota2	Pevné hodnoty, ktoré sa používajú na vytvorenie záhlaví stĺpcov.

V zobrazení SQL sa nevyskytuje obmedzenie počtu tabuliek ani dotazov, ktoré môžete použiť ako zdroje záznamov pre krížový dotaz. Jednoduchosť návrhu však môžete zachovať vytvorením dotazu Select, ktorý vráti všetky požadované údaje, ktoré chcete v krížovom dotaze použiť, a následným použitím tohto dotazu ako jediného zdroja záznamu. Ďalšie informácie o vytváraní krížového dotazu nájdete v časti Pozrite tiež.

1. Prejdite na kartu Vytvoriť a v skupine Ďalšie kliknite na položku Návrh dotazu.

2. Zatvorte dialógové okno Zobrazenie tabuľky.

3. Na karte Návrh kliknite v skupine Zobrazenia na položku Zobraziť a potom kliknite na položku Zobrazenie SQL.

4. Na karte Objekt SQL zadajte alebo prilepte nasledujúce príkazy SQL:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Do prvého riadka zadajte za príkaz TRANSFORM výraz, ktorý sa má použiť na výpočet súhrnných hodnôt, napríklad Sum([Množstvo]).

   Ak ako zdroj záznamov používate viac ako jednu tabuľku alebo dotaz, zahrňte názov tabuľky alebo dotazu do jednotlivých názvov polí, napríklad Sum([Výdavky].[Množstvo]).

6. Do druhého riadka zadajte za príkaz SELECT zoznam polí alebo výrazov polí, ktoré chcete v záhlaví riadkov použiť. Položky v zozname oddeľte bodkočiarkami, napríklad [Rozpočet].[ID_Oddelenia]; [Výdavky].[Typ].

7. Do tretieho riadka zadajte za príkaz FROM zoznam tabuliek a dotazov, ktoré používate ako zdroje dotazov, napríklad Rozpočet, Výdavky.

8. Do štvrtého riadka zadajte za príkaz GROUP BY rovnaký zoznam polí, ktorý ste použili v klauzule SELECT v kroku 6.

9. Do piateho riadka zadajte za príkaz PIVOT názov poľa alebo výraz, ktorý chcete pre záhlavie stĺpca použiť, napríklad PIVOT [Rozpočet].[Rok].

Niekedy je vhodné namiesto použitia jednotlivých hodnôt pre záhlavie riadka alebo stĺpca zoskupiť tieto hodnoty poľa do rozsahov a potom tieto rozsahy použiť v záhlaviach riadkov a stĺpcov. Predpokladajme napríklad, že ako záhlavia stĺpcov používate pole Vek. Namiesto stĺpca pre každú hodnotu veku pravdepodobne radšej použijete stĺpce, ktoré predstavujú vekové kategórie.

Na vytvorenie rozsahov pre záhlavia riadkov alebo stĺpcov môžete použiť výraz s funkciou IIf.

1. Otvorte krížový dotaz v návrhovom zobrazení.

2. V mriežke návrhu dotazu kliknite v riadku Pole pravým tlačidlom myši na prázdny stĺpec a potom kliknite v kontextovej ponuke na príkaz Lupa.

3. V dialógovom okne Lupa zadajte alias poľa a za ním dvojbodku (:).

4. Zadajte IIf().

5. Do zátvoriek funkcie IIf zadajte porovnávací výraz, ktorý určuje prvý rozsah pre hodnoty v poli.

   Predpokladajme napríklad, že vytvárate rozsahy pre pole Vek a chcete vytvoriť vekové kategórie s intervalom 20 rokov. Porovnávací výraz pre prvý rozsah je [Vek]<21.

6. Za porovnávacím výrazom napíšte čiarku a potom zadajte názov (uzavretý v úvodzovkách) pre tento rozsah. Zadaný názov sa bude zobrazovať v hlavičke krížového dotazu pre hodnoty, ktoré spadajú do zadaného rozsahu.

   Príklad: za výraz [Vek]<21 napíšte čiarku (,) a potom "0-20 rokov".

7. Napíšte čiarku za názov rozsahu (za úvodzovkami) a potom použite jeden z nasledujúcich postupov:

   • Ak chcete vytvoriť ďalší rozsah, zadajte IIf() a zopakujte kroky 5, 6 a 7.

   • V prípade posledného rozsahu zadajte len názov rozsahu.

     Dokončený vnorený výraz IIf, ktorý rozdeľuje pole Vek na 20-ročné intervaly, môže vyzerať napríklad takto (zlomy riadkov sú pridané kvôli väčšej prehľadnosti):

     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"))))
     

     Poznámka: Pri vyhodnocovaní tohto výrazu sa Access zastaví vtedy, keď sa príkaz IIf vyhodnotí ako true. Nemusíte zadávať spodnú hranicu každého rozsahu, pretože všetky hodnoty z nižšieho rozsahu by sa už skôr vyhodnotili ako true.

8. V mriežke návrhu dotazu vyberte v riadku Celkom hodnotu Zoskupiť podľa.

9. V riadku Krížová tabuľka zadajte, či sa majú tieto rozsahy použiť ako hlavičky riadkov alebo stĺpcov. Zapamätajte si, že môžete zadať až tri hlavičky riadkov, ale iba jednu hlavičku stĺpcov.

V prípade potreby môžete navrhnúť krížový dotaz tak, aby zobrazoval pri spustení výzvu na zadanie vstupných hodnôt. Predpokladajme napríklad, že používate viac hlavičiek riadkov, pričom jedna hlavička je Krajina alebo oblasť. Namiesto zobrazenia údajov pre všetky krajiny alebo oblasti chcete, aby sa najprv dotaz spýtal na názov krajiny a potom zobrazil výsledky podľa hodnoty, ktorú zadal používateľ.

Výzvu na zadanie parametra môžete pridať do ľubovoľného poľa hlavičiek riadkov.

1. Otvorte krížový dotaz v návrhovom zobrazení.

2. Do riadka Kritériá poľa hlavičiek riadkov, pre ktoré chcete zobrazovať výzvu na zadanie vstupnej hodnoty zo strany používateľa, zadajte text otázky uzatvorený v hranatých zátvorkách. Text otázky sa zobrazí ako výzva pri spustení dotazu.

   Ak zadáte napríklad otázku [Ktorá krajina alebo oblasť?] do riadka Kritériá, pri spustení dotazu sa zobrazí dialógové okno, ktoré bude obsahovať otázku „Ktorá krajina alebo oblasť?“, textové pole na zadanie vstupnej hodnoty a tlačidlo OK.

   Tip: Ak chcete, aby bol parameter flexibilný, použite zreťazenie výrazu so zástupnými znakmi a operátorom Like. Namiesto kritéria [Ktorá krajina alebo oblasť?] môžete použiť výraz Like [Ktorá krajina alebo oblasť]&"*", čím umožníte parametru nájsť zhodu so širším rozsahom vstupných hodnôt. Použitie operátora Like nezmení vzhľad výzvy na zadanie parametra.

3. Na karte Návrh kliknite v skupine Zobraziť alebo skryť na položku Parametre.

4. V dialógovom okne Parametre dotazu zadajte do stĺpca Parameter rovnakú výzvu na zadanie parametra, akú ste použili v riadku Kritériá. Použite hranaté zátvorky, ale nepoužívajte žiadne zreťazené zástupné znaky ani operátor Like.

5. V stĺpci Typ údajov vyberte typ údajov parametra. Typ údajov by sa mal zhodovať s typom údajov poľa, ktoré ste použili pre hlavičky riadkov.

Ak pole, ktoré používate v krížovom dotaze na výpočet súhrnných hodnôt, obsahuje hodnoty null, tieto hodnoty sa budú ignorovať pri všetkých agregačných funkciách. V prípade niektorých agregačných funkcií tak môže dôjsť k ovplyvneniu výsledkov. Napríklad pri výpočte priemernej hodnoty sa najprv spočítajú všetky hodnoty a potom sa výsledok vydelí počtom hodnôt. Ak však pole obsahuje hodnoty null, tieto hodnoty sa nezapočítajú do počtu hodnôt.

V niektorých prípadoch je lepšie nahradiť hodnoty null nulami, aby sa tieto hodnoty zahrnuli do agregačných výpočtov. Na nahradenie hodnôt null nulami môžete použiť funkciu Nz.

Syntax funkcie Nz

Nz ( variant [, valueifnull ] )

Syntax funkcie Nz má tieto argumenty:

Argument	Popis
variant	Povinné. Premenná pre typ údajov Variant.
valueifnull	Voliteľné (ak sa nepoužíva v dotaze). Typ údajov Variant poskytujúci hodnotu, ktorá sa má vrátiť, ak má argument variant hodnotu null. Tento argument umožňuje vrátiť hodnotu inú ako nula alebo reťazec s nulovou dĺžkou. Poznámka: Ak použijete funkciu Nz vo výraze v dotaze bez použitia argumentu valueifnull, výsledkom bude reťazec s nulovou dĺžkou v poliach, ktoré obsahujú hodnoty null.

1. Otvorte dotaz v návrhovom zobrazení. V mriežke návrhu dotazu kliknite pravým tlačidlom myši na pole Hodnota.

2. V kontextovej ponuke kliknite na príkaz Lupa.

3. Do poľa Lupa zadajte do zátvoriek názov poľa alebo výraz a potom pred zátvorky napíšte Nz.

4. Hneď pred pravú zátvorku zadajte , 0.

   Ak napríklad chcete použiť funkciu Nz na nahradenie hodnôt null za nuly v poli s názvom Nevyužité hodiny, dokončený výraz by mal vyzerať takto:

Nz([Hours Lost], 0)

• Dotazy by mali byť jednoduché    S narastajúcim počtom kombinácií riadkov sa stávajú krížové dotazy zle čitateľné. Nepoužívajte viac hlavičiek riadkov, ako je nevyhnutné.

• Vytvárajte svoje krížové dotazy postupne    Neobmedzujte sa len na používanie tabuliek. Často je výhodné začať vytvorením súhrnný dotaz a tento dotaz potom použiť ako zdroj záznamov pre krížový dotaz.

• Pozorne si vyberte pole pre hlavičky stĺpcov    Údajové hárky krížových dotazov sú čitateľnejšie, keď použijete relatívne malý počet hlavičiek stĺpcov. Keď ste už identifikovali polia, ktoré chcete použiť ako hlavičky, pre hlavičky stĺpcov je najvhodnejšie vybrať pole, ktoré obsahuje najmenej rôznych hodnôt. Ak napríklad dotaz vypočítava hodnoty podľa veku a pohlavia, pre hlavičky stĺpcov zvážte použitie stĺpca s informáciami o pohlaví, lebo zvyčajne obsahuje menší počet hodnôt ako vek.

• Použitie poddotazu v klauzule WHERE    V krížovom dotaze môžete použiť poddotaz ako súčasť klauzuly WHERE.

Na začiatok stránky