L6: Dotazy nad více tabulkami v MS Access

Příprava lekce

V naší databázi db1 vytvoříme tabulku tb1Deti se strukturou podle obrázku, pozornost věnujeme zvýrazněným položkám.

Do tabulky tb1Deti vložíme asi pět záznamů tak, aby se pole Cislo v několika případech shodovalo s polem Cislo v tabulce tb1Pracovnik.

Relace

Propojením tabulek dosáhneme zpřís­tupnění hodnot ve více tabulkách současně, to lze využít v dotazech a dalších objektech MS Access. Vytvořením relací vzniká mezi tabulkami přesně definovaný vztah. Relace se vytvoří propojením polí shodného typu ve dvou tabulkách.

Jsou definovány tři typy relací:

• 1 : N. Příkladem je v naší db1 vztah pracovník a dítě. Jeden pracovník může mít jedno dítě, více dětí, nebo nemá dítě žádné. Vidíme, že záznam pracovníka je povinný, řídící, příslušný záznam o dítěti povinný není.
• M : N. Příkladem může být zaměstnání manželského páru, který má děti. Potom nastává případ, kdy jeden pracovník má více dětí a současně jedno dítě patří více pracovníkům. V relační databázi se tento typ relace převádí na dvě relace 1 : N vytvořením třetí, spojovací tabulky.
• 1 : 1. Tato vazba není příliš obvyklá, vytváří se případně z bezpečnostních důvodů, nebo pro archivaci.

Na základě relací lze vynucovat referenční integritu.

Vytvoření relace mezi tabulkami

V menu DATABÁZOVÉ NÁSTROJE klepneme v pásu karet na ikonu Relace.

Otevře se formulář Přidat tabulku se seznamem tabulek. Obě tabulky přidáme na pracovní plochu již známým postupem - klepnutím na název a následně na tlačítko Přidat. Klepneme na Zavřít.

Na pracovní ploše relace lze tabulky přemísťovat myší uchopením, tažením a puštěním.

Pro nás je důležité pole Cislo v obou tabulkách, které je stejného typu a má stejný význam, tedy číslo pracovníka. Proto pro nás to bude propojovací pole.

Zdůrazňuji: typ pole musí být shodný, název může být různý.

Vytvoření nebo upravení relací můžeme provést otevřením formuláře Upravit relace z menu NÁVRH, klepnutím v pásu karet na ikonu Upravit relace.

Snadnější způsob je však myší - a to uchopením, tažením a puštěním pole Cislo z tabulky tb1Pracovnik na pole Cislo z tabulky tb1Deti. Posledním uvedeným postupem se otevře formulář Upravit relace předvyplněný.

Klepnutím na tlačítko Typ spojení se otevře formulář Vlastnosti spojení. My budeme hledat všechny pracovníky, nejenom ty, kteří mají děti. Proto nastavíme přepínač na druhou volbu - Zahrnout všechny záznamy z tabulky tb1Pracovnik a z tabulky tb1Deti jenom ty záznamy, ve kterých jsou spojená pole shodná.

Klepnutím na OK se vrátíme na formulář Upravit relace. Všimneme si možností nastavení referenční integrity a udržování propojených záznamů v kaskádě. To činí sílu relační databáze, ale také nebezpečí, že smažeme záznamy, které by slušelo archivovat, proto pozor na uvedená nastavení. My je ponecháme beze změny. Klepnutím na Vytvořit dokončíme práci na vytvoření relace.

Dotazy nad více tabulkami

Propojením tabulek vznikly podmínky pro výběr a zobrazování dat z obou tabulek současně. Takových propojených tabulek může být v dotazu více než dvě.

Postupem známým z páté lekce přes VYTVOŘENÍ a Návrh dotazu otevřeme návrh dotazu s formulářem Zobrazit tabulku. Obě tabulky tb1Pracovnik a tb1Deti přidáme na pracovní plochu a formulář Zobrazit tabulku zavřeme. Vidíme, že vazbu mezi tabulkami už máme realizovánu podle předchozího nastavení relace.

Dotazy s relací 1 : N

Dotazem najdeme všechny pracovníky, tedy s dětmi i bez dětí v naší evidenci. U pracovníků s dětmi budou děti jmenovitě uvedeny.

Pole dotazu naplníme poli z tabulek tb1Pracovnik.Cislo, tb1Pracovnik.Jmeno, tb1Pracovnik.Prijmeni, tb1Deti.D_Jmeno, tb1Deti.D_Prijmeni a výsledky dotazu zobrazíme v datovém listu, klepnutím v pásu karet na ikonu Zobrazení.

V případě mého zadání hodnot dětí vidíme, že jsou zobrazeni všichni zadaní pracovníci, ale u bezdětných jsou pole dětí prázdná.

Provázané záznamy v relaci 1 : N

Výběr jenom těch pracovníků, kteří mají děti, provedeme vložením podmínky. Vrátíme se do návrhového zobrazení klepnutím na ikonu Zobrazení. Do prvního prázdného sloupce pracovní tabulky přidáme pole Cislo z tabulky tb1Deti. V tomto sloupci nezaškrtneme řádek Zobrazit a do řádku Kritéria: zapíšeme podmínku Not is Null.

V zobrazení datového listu se přesvědčíme, že jsou opravdu vybráni jenom pracovníci s dětmi.

Chybějící hodnoty v relaci 1 : N na straně N

Vybrat pracovníky bez dětí je stejně snadné. Vrátíme se do návrhového zobrazení klepnutím na ikonu Zobrazení. Ve sloupci pracovní tabulky v poli Cislo z tabulky tb1Deti zapíšeme podmínku Is Null na místo předchozí Not is Null.

Dotaz uzavřeme a uložíme jako qry0201.

Chybějící hodnoty v relaci 1 : N na straně 1

Hledáme děti, které nemají odpovídající záznam v tabulce pracovníků (Podotýkám, že správně by k této situaci nemělo dojít!). Protože tabulka na straně 1 vazby 1 : N, tb1Pracovnik, je řídící, je nutno k takové konstrukci vytvořit pomocný dotaz a následně jej použít v dalším dotazu jako tabulku.

Snadnější řešení je proto obrátit vazbu mezi tabulkami v námi vytvořeném dotazu.

Kopie dotazu a jeho úpravy
Finta: než psát téměř shodný dotaz od počátku, je lepší starý dotaz zkopírovat a vložit, následně provést úpravy v návrhovém zobrazení. Klepneme na název dotazu qry0201 v seznamu dotazů, aby se podbarvil a známými kombinacemi kláves Ctrl + C a následně CTRL + V nám program nabídne pojmenování nového dotazu v dialogu Vložit jako, napíšeme tedy qry0202 a ukončíme OK. Máme hotovo téměř celé sestavení dotazu.

Pokračujeme otevřením dotazu qry0202 v návrhovém zobrazení. Klepneme pravým tlačítkem myši na vazbu mezi tabulkami. Vazba se zvýrazní a otevře se menu, ve kterém zvolíme funkci Vlastnosti spojení, tím otevřeme formulář Vlastnosti spojení. Vybereme možnost číslo 2: Zahrnout všechny záznamy z tabulky tb1Deti a z tabulky tb1Pracovnik jenom ty záznamy, ve kterých jsou spojená pole shodná.

Klepnutím na OK se vazba opticky obrátí, šipka směřuje k poli Cislo v tabulce tb1Pracovnik. Zbývá upravit pole a podmínky v pracovní tabulce. Budou nás zajímat pole ID_D a Cislo z tabulky tb1Deti, abychom zjistili chybu v čísle zaměstnance.

Pozor! Podmínka Is Null musí být nastavena na pole Cislo v tabulce tb1Pracovník!

Nastavení můžeme zobrazit a uložit.

Parametry dotazu

Dosud jsme museli podmínky v dotazech psát natvrdo, přímo, a uživatel neměl možnost tuto hodnotu ovlivnit. MS Access dává možnost vytvořit dotaz, kde uživatel může zadat hodnotu podmínky v době zpracování a tím je dotaz flexibilnější.

Z naší tabulky pracovníků chceme vybrat zaměstnance podle roku narození. Známým postupem přes VYTVOŘENÍ a Návrh dotazu otevřeme návrh dotazu s formulářem Zobrazit tabulku. Tabulku tb1Pracovnik přidáme na pracovní plochu a formulář Zobrazit tabulku zavřeme.

Do pracovní tabulky vložíme pole Cislo, Jmeno, Prijmeni, RokNarozeni. Z menu NÁVRH v pásu karet klepneme na ikonu Parametry a program otevře formulář Parametry dotazu. Nastavíme hodnoty RokMin a RokMax s typem Dlouhé celé číslo podle obrázku. Klepneme na OK.

V návrhu dotazu do řádku Kritéria: do pole RokNarozeni napíšeme podmínku >=RokMin, tedy název zadaného parametru. Pro druhé omezení vytvoříme další sloupec z pole RokNarozeni, nevyplníme řádek zobrazovat a do řádku Kritéria: zapíšeme podmínku <=RokMax. Program si doplní hranaté závorky, aby odlišil parametr od přímého vložení hodnoty.

Klepneme na Zobrazení a program zobrazí postupně dialogy Zadat hodnotu parametru pro nastavení obou vložených parametrů. Já jsem zadal RokMin = 1950 a RokMax = 1990.

Dotaz zobrazí vybraná data. Následně dotaz zavřeme a název změníme na qry0204.