Přehlednější zobrazení souhrnných dat pomocí křížového dotazu

Vytvoření křížového dotazu v návrhovém zobrazení dovoluje použít libovolný počet zdrojů záznamů (tabulky a dotazy). Pokud dáváte přednost co nejjednoduššímu návrhu, vytvořte nejdřív výběrový dotaz, který vrátí všechna požadovaná data, a ten potom použijte dotaz jako jediný zdroj záznamů pro křížový dotaz. Další informace o vytváření výběrových dotazů naleznete v oddílu Viz také.

Při tvorbě křížového dotazu v návrhovém zobrazení určujete pomocí řádků Celkem a Křížový dotaz v návrhové mřížce, které hodnoty polí budou použity v záhlaví sloupců, které hodnoty polí se zobrazí v záhlaví řádků a které hodnoty polí budou sloužit k výpočtům součtu, průměru, počtu a dalších funkcí.

1. Nastavení těchto řádků určuje, zda je pole záhlavím řádku, sloupce nebo souhrnnou hodnotou.

2. Toto nastavení zobrazuje hodnoty pole v záhlaví řádků.

3. Toto nastavení zobrazuje hodnoty pole v záhlaví sloupců.

4. Toto nastavení vypočte souhrnné hodnoty.

Vytvoření dotazu

1. Na kartě Vytvoření klikněte ve skupině Dotazy na Návrh dotazu.

   

2. V dialogovém okně Zobrazit tabulku poklikejte na každou tabulku nebo dotaz, které chcete použít jako zdroj záznamů.

   Pokud používáte více zdrojů záznamů, ověřte si, jestli jsou společná pole tabulek nebo dotazů spojená. Další informace o spojování tabulek a dotazů naleznete v oddílu Viz také.

3. Zavřete dialogové okno Zobrazit tabulku.

4. Na kartě Návrh klikněte ve skupině Typ dotazu na položku Křížový.

5. V návrhovém okně dotazu poklikejte na každé pole, které chcete použít jako zdroj pro záhlaví řádků. Pro záhlaví řádků můžete vybrat až tři pole.

6. V návrhové mřížce dotazu klikněte v řádku Křížový na položku Záhlaví řádku, a to pro všechna záhlaví řádků.

   Do řádku Kritéria můžete zadat výraz, kterým omezíte počet zobrazených výsledků pro dané pole. Pomocí řádku Řadit můžete nastavit řazení daného pole.

7. V návrhovém okně dotazu poklikejte na pole, které chcete použít jako zdroj záhlaví sloupců. Pro záhlaví sloupců lze vybrat pouze jedno pole.

8. V návrhové mřížce dotazu klikněte v řádku Křížový na pole Záhlaví sloupce odpovídající příslušnému záhlaví sloupce.

   Zadáním výrazu do řádku Kritéria můžete omezit výsledky pro pole záhlaví sloupce. Použití výrazu kritéria s polem záhlaví sloupce ale neomezuje počet sloupců vrácených křížovým dotazem. Místo toho omezuje, které sloupce obsahují data. Předpokládejme například, že máte pole záhlaví sloupce, které má tři možné hodnoty: červenou, zelenou a modrou. Pokud u pole záhlaví sloupce použijete kritérium ='blue' , křížová tabulka stále zobrazuje sloupec pro červenou a sloupec pro zelenou, ale pouze sloupec pro modrý obsahuje data.

   Pokud chcete omezit počet hodnot zobrazených v záhlaví sloupců, můžete zadat seznam pevných hodnot pomocí vlastnosti Záhlaví sloupců daného dotazu. Další informace najdete v následující části.

9. V návrhovém okně dotazu poklikejte na pole, které chcete použít k výpočtu souhrnných hodnot. Výpočet souhrnných hodnot lze provést pouze pro jedno pole.

10. V návrhové mřížce dotazu klikněte v řádku Celkem daného pole souhrnných hodnot na agregační funkci, kterou chcete použít k výpočtu hodnot.

11. V řádku Křížový příslušného pole souhrnných hodnot klikněte na položku Hodnota.

    V poli souhrnných hodnot nelze používat kritéria ani toto pole řadit.

12. Na kartě Návrh klikněte ve skupině Výsledky na tlačítko Spustit.

Zadání pevných hodnot pro záhlaví sloupců

Chcete-li v záhlaví sloupců použít pevně zadané hodnoty, můžete nastavit vlastnost Záhlaví sloupců.

1. Otevřete křížový dotaz v návrhovém zobrazení.

2. Pokud se nezobrazuje seznam vlastností, zobrazte ho stisknutím klávesy F4.

3. Na listu vlastností nad kartou Obecné zkontrolujte, zda je v poli Typ výběru zadána hodnota Vlastnosti dotazu. Pokud není, klikněte do prázdného místa v prostoru nad návrhovou mřížkou dotazu.

4. Na listu vlastností zadejte na kartě Obecné do vlastnosti Záhlaví sloupců čárkou oddělený seznam hodnot, které chcete použít v záhlaví sloupců.

   Některé znaky (například většina interpunkčních znamének) nejsou v záhlaví sloupců povoleny. Pokud tyto znaky použijete v seznamu hodnot, Access nahradí každý takový znak podtržítkem (_).

Syntaxe jazyka SQL pro křížový dotaz

Křížový dotaz se v jazyce SQL vyjadřuje příkazem TRANSFORM. Příkaz TRANSFORM má následující syntaxi:

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

Příkaz TRANSFORM se skládá z těchto částí:

Část	Popis
aggfunction	Agregační funkce SQL, která pracuje na vybraných datech
selectstatement	Příkaz SELECT
pivotfield	Pole nebo Výraz, které chcete použít k vytvoření záhlaví sloupců v sadě výsledků dotazu.
hodnota1, hodnota2	Pevné hodnoty, které se použijí k vytvoření záhlaví sloupců.

Zobrazení SQL neomezuje počet tabulek nebo dotazů, které můžete použít jako zdroje záznamů pro křížový dotaz. Návrh ale můžete usnadnit vytvořením výběrového dotazu, který vrátí všechna data, která chcete použít v křížovém dotazu, a pak tento výběrový dotaz použijete jako zdroj záznamů. Další informace o vytváření výběrových dotazů naleznete v oddílu Viz také.

1. Na kartě Vytvořit klikněte ve skupině Jiné na Návrh dotazu.

2. Zavřete dialogové okno Zobrazit tabulku.

3. Na kartě Návrh klikněte ve skupině Zobrazení na položku Zobrazení a potom na položku Zobrazení SQL.

4. Na kartu Objekt SQL zadejte nebo zkopírujte následující příkaz SQL:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Na prvním řádku za příkaz TRANSFORM zadejte výraz, který chcete použít k výpočtu souhrnných hodnot, například Součet([Částka]).

   Pokud jako zdroj záznamů používáte víc tabulek nebo dotazů, zahrňte název odpovídající tabulky či dotazu do názvu konkrétního pole, například Součet([Výdaj].[Částka]).

6. Na druhý řádek za klauzuli SELECT zadejte seznam polí nebo výrazy pole, které chcete použít jako záhlaví řádků. Položky seznamu oddělte čárkami, například [Rozpočet].[ID_oddělení], [Výdaj].[Typ].

7. Ve třetím řádku za klauzuli FROM zadejte seznam tabulek nebo dotazů, které chcete použít jako zdroje záznamů, například Rozpočet, Výdaje.

8. Ve čtvrtém řádku za klauzuli GROUP BY zadejte stejný seznam polí, který jste použili v klauzuli SELECT v kroku 6.

9. V pátém řádku za klauzuli PIVOT zadejte název pole nebo výraz, který chcete použít jako záhlaví sloupců, například PIVOT [Rozpočet].[Rok].

Někdy místo použití každé hodnoty pole pro záhlaví řádků nebo sloupců chcete hodnoty pole seskupit do oblastí a pak je použít pro záhlaví řádků nebo sloupců. Předpokládejme například, že pro záhlaví sloupců používáte pole Věk. Místo použití sloupce pro každý věk můžete raději použít sloupce, které představují věkové rozsahy.

Pomocí funkce IIf můžete ve výrazu vytvořit rozsahy hodnot a použít je jako záhlaví sloupců nebo řádků.

1. Otevřete křížový dotaz v návrhovém zobrazení.

2. V návrhové mřížce dotazu klikněte v řádku Pole pravým tlačítkem na prázdný sloupec a potom v místní nabídce na příkaz Lupa.

3. Do pole Lupa zadejte alias pole a za ním dvojtečku (:).

4. Zadejte příkaz IIf().

5. V závorce za příkazem IIf zadejte porovnávací výraz, který definuje první rozsah hodnot polí.

   Předpokládejme například, že vytváříte oblasti pro pole Věk a chcete, aby každá oblast byla dvacet let. Porovnávaný výraz pro první oblast je [Age]<21.

6. Za porovnávacím výrazem napište čárku a potom v uvozovkách zadejte název rozsahu. Zadaný název se zobrazí v záhlaví křížového dotazu pro hodnoty spadající do příslušného rozsahu dat.

   Za parametr [Věk]<21 například napište čárku a parametr "0–20 let".

7. Za názvem rozsahu napište čárku (za uvozovky) a postupujte některým z následujících způsobů:

   • Chcete-li vytvořit další rozsah, zadejte příkaz IIf() a potom zopakujte kroky číslo 5, 6 a 7.

   • U posledního rozsahu stačí zadat jenom jeho název.

     Úplný vnořený výraz IIf, který by rozděloval pole Věk do rozsahů po dvaceti letech, by například mohl vypadat následujícím způsobem (zalomení řádků bylo přidáno pro snazší čitelnost):

     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: Access ukončí vyhodnocování výrazu, jakmile bude splněna jedna z podmínek funkce IIf. Spodní hranici jednotlivých rozsahů tedy není nutné zadávat, protože hodnoty, které by spadaly pod spodní hranici daného rozsahu, by už byly vyhodnoceny jako pravdivé.

8. V návrhové mřížce dotazu vyberte v řádku Celkem položku Seskupit podle.

9. V řádku Křížová tabulka zadejte, jestli chcete rozsahy použít pro záhlaví řádků nebo sloupců. Nezapomeňte, že pro záhlaví řádků můžete zadat jedno až tři pole, zatímco pro záhlaví sloupců jen jedno pole.

Po spuštění křížového dotazu je možné zobrazit výzvu k zadání vstupních hodnot. Můžete například používat několik záhlaví řádků, mezi nimi pole pro Zemi nebo oblast. Pokud vždy nechcete zobrazovat data ze všech zemí a oblastí, můžete nastavit zobrazení výzvy k zadání názvu země a zobrazit pouze data požadovaná uživatelem.

Výzvu k zadání parametru lze přidat k libovolnému poli záhlaví řádků.

1. Otevřete křížový dotaz v návrhovém zobrazeni.

2. V řádku Kritéria pro pole záhlaví řádku, pro nějž chcete zobrazit výzvu k zadání vstupních dat uživatelem, zadejte text výzvy v hranatých závorkách. Výzva se zobrazí při spuštění dotazu.

   Pokud například v řádku Kritéria zadáte text [Zadejte zemi nebo oblast:], zobrazí se při spuštění dotazu dialogové okno s výzvou: „Zadejte zemi nebo oblast:“ Dále bude obsahovat vstupní pole a tlačítko OK.

   Tip: Chcete-li, aby bylo zadání parametru flexibilní, můžete ve výrazu použít logický operátor Like a zástupné znaky. Namísto kritéria [Zadejte zemi nebo oblast:] tedy například můžete použít kritérium Like [Zadejte zemi nebo oblast:]&"*", aby parametr odpovídal širšímu rozsahu zadání. Použití logického operátoru Like nijak nezmění vzhled výzvy k zadání parametru.

3. Na kartě Návrh klikněte ve skupině Zobrazit/Skrýt na položku Parametry.

4. V dialogovém okně Parametry dotazu zadejte ve sloupci Parametry stejnou výzvu k zadání parametrů, kterou jste použili v řádku Kritéria. Zadejte ji také v hranatých závorkách, ale neuvádějte žádné zástupné znaky ani logický operátor Like.

5. Ve sloupci Datový typ vyberte požadovaný datový typ parametru. Datový typ by měl odpovídat datovému typu pole záhlaví řádku.

Jestliže pole, které použijete k výpočtu souhrnných hodnot v křížovém dotazu, obsahuje prázdné hodnoty, takové hodnoty budou agregačními funkcemi ignorovány. To může ovlivnit výsledky výpočtu některých agregačních funkcí. Například při výpočtu průměru je nutné přičíst všechny hodnoty a vydělit výsledek počtem hodnot. Pokud však pole obsahuje nulové hodnoty, nebudou takové nulové hodnoty započteny v celkovém počtu hodnot.

V některých případech tedy může být vhodné nahradit prázdné hodnoty nulami, aby se tyto hodnoty započetly při agregačních výpočtech. Prázdné hodnoty lze nahradit nulami pomocí funkce Nz.

Syntaxe funkce Nz

Nz ( variant [, valueifnull ] )

Syntaxe funkce Nz obsahuje následující argumenty:

Argument	Popis
variant	Povinný argument. Proměnná Datový typ variant.
valueifnull	Nepovinný argument (pokud není použit v dotazu). Proměnná variant poskytující hodnotu, která má být vrácena, pokud má argument variant hodnotu Null. Tento argument umožňuje vrácení hodnoty jiné než nula nebo řetězec nulové délky. Poznámka: Pokud použijete funkci Nz ve výrazu v dotazu bez použití argumentu valueifnull, bude výsledkem řetězec nulové délky v polích obsahujících prázdné hodnoty.

1. Otevřete dotaz v návrhovém zobrazení a v návrhové mřížce dotazu klikněte pravým tlačítkem do pole Hodnota.

2. V místní nabídce klikněte na příkaz Lupa.

3. Do pole Lupa uveďte do závorek název pole nebo výraz a potom před závorky zadejte příkaz Nz.

4. Těsně před pravou závorku zadejte , 0.

   Pokud například použijete funkci Nz u pole s názvem „Prostoje" a změníte prázdné hodnoty na nuly, bude výsledný výraz vypadat následujícím způsobem:

Nz([Hours Lost], 0)

• V jednoduchosti je krása:    Křížové dotazy se mohou rostoucím počtem kombinací řádků stát velmi nepřehlednými. Nepoužívejte proto více záhlaví řádků, než je nutné.

• Zvažte vytvoření křížového dotazu v postupných krocích:    Neomezujte se pouze na používání tabulek. Často lze začít vytvořením Souhrnný dotaz a potom použít tento dotaz jako zdroj záznamů pro křížový dotaz.

• Pečlivě vybírejte záhlaví sloupců:    Datové listy křížových dotazů bývají přehlednější, pokud počet záhlaví sloupců zůstane relativně nízký. Jakmile se rozhodnete, která pole chcete použít pro záhlaví, vyberte jako záhlaví sloupců ta pole, která obsahují nejnižší počet různých hodnot. Pokud například dotaz vypočítává hodnotu podle věku a pohlaví, je vhodnější použít pro záhlaví sloupců spíše pohlaví než věk, protože pohlaví bude mít pravděpodobně nižší počet možných hodnot než věk.

• V klauzuli WHERE využijte poddotaz    Jako součást klauzule WHERE v křížovém dotazu můžete použít poddotaz.

Začátek stránky