dtb4puv :: js-databaze

L7 Praktická řešení v dotazech MS Access

Pomůcka: Zápis speciálních znaků z české klávesnice. Zadávají se držením klávesy pravý Alt a klepnutím na klávesu podle tabulky.

Jiný pohled na parametry dotazu

V předchozí lekci jsme si ukázali použití parametrů v dotazech. Parametr je proměnná, jejíž hodnota se zadává při spuštění dotazu a většinou je použita jako podmínka výběru. Problém nastává, když takový dotaz spustíte jednou za rok a zapomenete kontext, v jakém byl parametr použit. Navíc, ve velkých databázích se stovkami dotazů, je parametr z tohoto důvodu téměř nepoužitelný.

Praktické řešení s tabulkou parametrů

Využijeme toho, že program MS Access je optimalizován pro práci s tabulkami. Vytvoříme si tabulku parametrů, která bude mít vždy jenom jeden záznam a pole v ní budou sloužit k použití v podmínkových výrazech dotazů. Tabulka parametrů má jasné výhody:

Parametr lze zadávat ve formuláři, kde je jeho použití v konkrétním případě řádně objasněno
Jeden parametr lze použít ve více dotazech
Předchozí zadaná hodnota parametru zůstává zachována a zobrazí se při zadávání, takže ji často není třeba opakovaně zadávat.
Pomocí programové funkce dlookup() získáváme hodnoty z tabulky parametrů přímo v místě potřeby a to jak ve většině objektů MS Access, tak i v programu. Takto konstruované a s uvedenou funkcí použité parametry se chovají jako globální proměnné.

Další použití: firma může mít v parametrech zadány základní údaje, které jsou konstantní, v celé úloze snadno dostupné a často se používají v zobrazení nebo tisku. Jedná se o IČO, název firmy, adresu sídla firmy a další.

Vytvoření a naplnění tabulky parametrů

Z menu VYTVOŘENÍ a v pásu karet z ikony Návrh tabulky přejdeme na formulář pořízení polí v nové tabulce. Pro testování zadáme základní firemní údaje a dále název pole qText typu krátký text a dále dvě pole typu datum a čas s názvy qDatumOd a qDatumDo.

Jmenná konvence: pole začínající písmenem q používáme jako parametry, neuchovávají trvalou hodnotu, na rozdíl třeba od IČO a adresy firmy.

Tabulce není třeba přiřazovat indexy a vlastní klíč, vždy bude obsahovat pouze jediný záznam.

Tabulka parametrů

Po uložení a změně názvu tabulky na tb0Params ji naplníme jedním záznamem.

Tabulku uzavřeme a ukážeme si její použití na praktickém příkladu.

Použití tabulky parametrů v dotazech

Jedním ze způsobů použití tabulky parametrů k vytvoření podmínek v dotazech je její přidání na pracovní plochu dotazu, ale bez propojení na ostatní použité tabulky. Pole z tabulky parametrů použijeme ke konstrukci omezujících podmínek.

Z menu VYTVOŘENÍ a v pásu karet z ikony Návrh dotazu přidáme na pracovní plochu tabulky tb0Params a tb1Pracovnik ve formuláři Zobrazit tabulku.

Tabulky zůstanou nepropojeny!

Do pracovní tabulky dotazu přidáváme pole, které chceme zobrazit z tabulky tb1Pracovnik a k poli Nastup do řádku Kritéria: zapíšeme podmínku s použitím polí tabulky tb0Params:

>=[tb0Params].[qDatumOd] And <=[tb0Params].[qDatumDo]

Sestavený dotaz je na obrázku. O jeho správnosti se přesvědčíme zobrazením a uložíme ho pod názvem qry0204.

Podmínky v textových polích

Dosud jsme sestavovali podmínky v numerických polích, kde je jasné ohraničení a z toho plynou jednoduché výrazy pro psaní podmínek a kritérií. Jiný případ jsou textová pole, kde většinou nemá smysl psát: je větší než Kubásek a současně menší než Frutová.

Operátor Like

Pro textové výběry dat, jejich porovnávání a práci s nimi je výhodné používat výběrový operátor Like, který hledá text podle zapsaného vzoru. Umožní např. vybrat jména končící na písmeno ů, obsahující písmeno ó nebo třeba nezačínající na určitou podmnožinu písmen. Taková kouzla nám umožňují zástupné znaky, kterých operátor Like používá celou škálu.

Zástupné znaky a další symboly pro operátor Like

Zástupné znaky a další symboly pro operátor Like

Použití operátoru Like

Následující příklady jsou zapsány tak, jak je zapíšeme do podmínky dotazu v řádku Kritéria:

Like "b*" vyhledá všechny hodnoty pole, začínající písmenem b
Like "#??" vyhledá tříznaková slova začínající číslicí
Like "K[!a-n]*" vyhledá hodnoty pole začínající na písmeno K a druhý znak nesmí být z intervalu a-n.

Vytvoření dotazu s poslední podmínkou je na obrázku. Z našich zaměstnanců dvě příjmení začínají na písmeno K, ale jenom Kubů je vybrán v dotazu, Klidná vybrána není, protože písmeno l je intervalu a-n.

Dotaz s textovou podmínkou

POZOR: operátor Like nerozlišuje při vyhledávání velká a malá písmena, tedy není case-senzitivní. Zápisy Like "*a" nebo Like "*A" jsou z hlediska podmínky v dotazu shodné.

Příklad operátoru Like s tabulkou parametrů

Abychom využili obě praktické výhody, tabulku parametrů a operátor Like, musíme spojit přímý zápis operátoru Like a hodnoty zadané v poli tb0Params.qText do jednoho řetězce. Použijeme konstrukci, která do podmínky přenese hodnotu z pole qText zadanou v parametrech. Toho dosáhneme použitím operátoru zřetězení & podle následujícího vzoru:

Like "*" & [tb0Params].[qText] & "*"

Protože do pole [tb0Params].[qText] jsme zadali jako hodnotu písmeno á, má výsledný řetězec stejnou interpretaci jako bychom zapsali:

Like "*á*"

Návrh dotazu s využitím popsaného spojení řetězců je na obrázku

Zřetězení textových parametrů

Křížový dotaz

Užitečnou a praktickou nadstavbou jazyka SQL v MS Access je křížový dotaz, formálně shodný s Excelovou kontingenční tabulkou.

Vytvoříme si součty bonusů podle oddělení, s členěním podle roku narození.

Tabulku tb1Pracovnik si doplníme několika dalšími záznamy tak, aby bylo několik hodnot shodných ve sloupci RokNarozeni a Oddeleni.

Známým postupem z menu VYTVOŘENÍ klepnutím na ikonu Návrh dotazu program zobrazí formulář Zobrazit tabulku ve kterém vybereme tb1Pracovnik a klepnutím na Přidat ji přeneseme na pracovní plochu. Zavřeme formulář Zobrazit tabulku a v menu NÁVRH v pásu karet klepneme na ikonu Křížový. V pracovní tabulce byly vytvořeny dva nové řádky Souhrn: a Křížová tabulka:.