L2: Vytvoření tabulky a datové typy v MS Access

Začátkem lekce si opět vysvětlíme pár pojmů:

• Tabulka - úložiště dat, které se ukládá podle předem stanovených pravidel.
  Tato pravidla jsou součástí tabulky a musíme je definovat dříve, než data začneme ukládat.
  Představit si ji můžeme jako tabulku na papíře, kde ve sloupcích jsou hodnoty stejného typu (jméno, příjmení, rok narození) a v řádcích jsou hodnoty, které popisují jednoho určitého člověka: Filip, Peterka, 2001.
• Pole, položka, atribut, - jsou synonyma. Zde reprezentují jednotlivé údaje, např. jméno nebo příjmení nebo rok narození. Pole mají dále nastavené vlastnosti, např. rok narození musí obsahovat pouze číslice a být větší než 1900 a menší nebo rovno roku aktuálnímu.

Vytvoření tabulky

Tabulku vytvoříme v návrhovém zobrazení, kde nastavíme nejen datové typy polí, ale také jejich další vlastnosti. Spustíme MS Access a otevřeme naši databázi db1. Ve vodorovném menu vybereme položku VYTVOŘENÍ a ze skupiny funkcí Tabulky vybereme Návrh tabulky.

Otevře se prázdná Tabulka1 (název je generován automaticky a bude námi změněn) s formulářem pro zadávání polí, jejich typů a vlastností. Zjistíme, že v horním vodorovném menu přibyla funkce NÁVRH a s ní se otevřel nový pás karet.

Do sloupce Název pole zadáme Cislo, a otevřeme seznam v poli Datový typ, vybereme položku Číslo.
Svoji pozornost přeneseme do dolní části obrazovky, kde upřesníme vlastnosti pole Cislo. Z nabídky vybereme Velikost pole a ze seznamu vybereme dlouhé celé číslo.
Z dalších vlastností nastavíme Počet desetinných míst na 0 a přechodem na vlastnost Ověřovací pravidlo lze v pravém konci řádku otevřít formulář Tvůrce výrazů.

Předpokládejme, že číslo pracovníka je pětimístné, chceme tedy omezit čísla v tomto rozsahu. Zapíšeme logický výraz v anglické notaci s tím, že předmětný název pole (zde Cislo) chybí.

Zapíšeme >9999 and <100000 a tlačítkem OK formulář Tvůrce výrazů zavřeme. Nápovědu můžeme nalézt v níže zobrazených třech polích Tvůrce výrazů, zde byl použit případ pro logické operátory.

Ve Vlastnosti pole dále nastavíme

• Ověřovací text: Zadejte celé, pětimístné číslo
• Je nutno zadat: ano
• Indexovat: ano (bez duplicity)

Zarovnání textu ponecháme nebo nastavíme vpravo, tak se naplňují čísla. Výsledné nastavení vidíme na obrázku

Tím je zadáno a nastaveno jedno pole z tabulky, kterou vytváříme. Pro úplnou tabulku pracovníka ještě potřebujeme jeho jméno, příjmení, rok narození a datum nástupu do firmy. Při vytváření těchto polí budeme pokračovat podobně.

Do druhého řádku, do položky Název pole, napíšeme Jmeno, Datový typ zvolíme Krátký text a ve Vlastnostech pole nastavíme položky takto:

• Velikost pole: 100
• Je nutno zadat: ano
• Indexovat: ne
• Komprese kódu Unicode: ano

Do třetího řádku, do položky Název pole, napíšeme Prijmeni. Datový typ a Vlastnosti pole nastavíme shodně s polem Jmeno.

Do čtvrtého řádku, do položky Název pole napíšeme, RokNarozeni. Datový typ nastavíme na Číslo a Vlastnosti pole nastavíme takto:

• Velikost pole: celé číslo
• Je nutno zadat: ano
• Indexovat: ne

Pro nás poslední, pátý řádek v položce Název pole vyplníme slovem Nastup. Datový typ nastavíme na Datum a čas. Vlastnosti pole nastavíme takto (předpokládáme, že firma vznikla 1.7.2013):

Formát: datum (krátké). Zde si všimneme, že uvedené datum ve výběrovém seznamu není hodnotou, ale pouze vzor, jak bude datum zobrazeno.

Pokračujeme:

• Ověřovací pravidlo: >=1.7.2013
• Je nutno zadat: ano
• Indexovat: ne

Když opouštíme položku Ověřovací pravidlo, program hlásí chybu.

Datum a jeho interpretace je další nesrovnalost mezi nastavenou češtinou (ale i jinými jazyky) a užívanou angličtinou ve vnitřním prostředí MS Access. U datumu budeme narážet častěji, teď musíme vyléčit náš problém. Klepneme v chybovém hlášení na OK a vrátíme se do Vlastnosti pole / Ověřovací pravidlo.

Abychom se s prostředím programu dohodli, musíme naspat >=#1.7.2013#, tedy doplnit znaky # před a za česky napsané datum. Tyto vyrobíme podržení klávesy Alt–pravý a stisknutím klávesy X. Výsledek už MS Access pochopí správně.

Změníme ještě položku Je nutno zadat na Ano. Tím máme definované pole v tabulce a můžeme provést závěrečnou operaci, tedy nastavení názvu tabulky a její uložení v databázi. Klepneme pravým tlačítkem myši na ouško, kde je dosud vygenerovaný název Tabulka1 a v otevřeném menu vybereme funkci Zavřít.

Program zobrazí dotaz Chcete uložit změny v návrhu objektu Tabulka1? klepneme na Ano, program zobrazí výzvu Uložit jako. Do vstupního pole zapíšeme název tb1Pracovnik a klepneme na tlačítko OK.

Musíme projít ještě jedním upozorněním Není definován primární klíč... V rámci tohoto hlášení klepneme na tlačítko Ne.

L3: Práce s tabulkou v MS Access

Vysvětlení pojmů

• Záznam - pod tímto pojmem budeme rozumět jeden řádek tabulky, který je ucelenou logickou informací (z hlediska naší tabulky tb1Pracovnik o jednom pracovníkovi), např. jeden záznam v naší tabulce je:

  Cislo: 12345, Jmeno: Johana, Prijmeni: Kotoučová, RokNarozeni: 1988, Nastup: 3.8.2016.

Zadávání dat

V levém sloupci poklepeme na název tabulky tb1Pracovník a v pravém okně se otevře tabulka se zobrazením pro vstup hodnot do polí tabulky. Do prvního řádku zadáme vymyšlené hodnoty, např. 10521, Zita, Švandová, 1995, 2.2.2017.

Do druhého řádku tabulky do pole Cislo vložíme 101, ukončíme klávesou Enter. Zobrazí se chybové hlášení, které jsme zadali do vlastnosti Ověřovací text, tedy Zadejte pětimístné číslo. Hlášení uvolníme klepnutím na OK a vrátíme se do pole Cislo. Na další pole se přesuneme vložením pětimístného čísla.

Vlastnost Formát

Pětimístné číslo je již hůře čitelné, pokud jsou číslice psané bez mezer, proto si vytvoříme klasický český pohled na číslo. Vrátíme se k návrhovému zobrazení tabulky tb1Pracovnik. Ve vodorovném menu v záhlaví okna klepneme na funkci DOMŮ, na ikoně Zobrazení klepneme na šipku dolů a otevře se nám menu, z něhož vybereme funkci Návrhové zobrazení.

Touto volbou, společně s nastavenou nulou v položce Počet desetinných míst, dostaneme požadované zobrazení. Opět klepneme na ikonu Zobrazení, tentokrát na obrázek, nebo z otevřeného menu vybereme položku Datový list. Před navrácením se do původního formuláře klade program výzvu Nejprve musíte uložit tabulku. Chcete ji uložit nyní? Klepneme na Ano a vrátíme se do pořizování dat, zobrazení čísla je v českém standardu, např. číslo 10101 je zobrazeno 10 101.

Je třeba zdůraznit, že databáze má uloženo číslo 10101 pořád stejně a zobrazení s mezerou je jenom formátování pro obrazovku. Pokud by někdo požadoval jiný formát, bude se měnit jenom zobrazení, ale číslo zůstane uloženo beze změny.

Vlastnost Indexovat

Vlastnost pole Indexovat lze nastavit na hodnoty

• Ne pole nebude indexováno, nebo bude index z pole odstraněn, pokud byl dříve nastaven
• Ano (duplicita povolena) hodnoty v poli se mohou opakovat
• Ano (bez duplicity) každá hodnota v poli musí být jedinečná

Formulář Indexy

Pro detailnější práci s nastavením indexů, nebo pro vytvoření indexu na více polí, je třeba otevřít okno Indexy z návrhového zobrazení tabulky. Zde je možno nastavit tyto další vlastnosti indexů:

• Index může mít více polí, v MS Access až deset, to znamená, že třídění a vyhledávání probíhá podle těchto polí, jak jdou v indexu za sebou. Index musí být označen názvem u prvního vybraného pole.
• Pořadí řazení: vzestupně, sestupně
• Primární: ano, ne. Slouží k nastavení primárního klíče, viz dále
• Jedinečný: ano, ne.
• Ignorovat hodnoty NULL: ano, ne.

Otevřeme si MS Access s databází db1. V seznamu tabulek klepneme pravým tlačítkem myši na název tabulky tb1Pracovnik, z otevřeného menu vybereme Návrhové zobrazení (toto je další způsob, jak otevřít tabulku v návrhovém zobrazení).

Z menu NÁVRH v pásu karet klepneme na ikonu Indexy.

Otevře se okno Indexy: tb1Pracovnik s přehledem o indexech uložených na právě nastavené tabulce. U indexu Cislo nastavíme ve vlastnostech indexu Primární: ano. Na voliči záznamů se zobrazí ikona klíče.

Primární klíč

Primární klíč je zvláštní označení pole, které je současně indexem tabulky, kde každá hodnota v celé tabulce je jedinečná (neopakuje se). Je tedy jednoznačnou identifikací každého záznamu. Hlavní využití je v propojování tabulek. Nastavením pole jako primárního klíče MS Access současně vynutí nastavení dalších hodnot v části Vlastnosti pole:

• Indexovat: Ano bez duplicity
• Je nutno zadat: Ano
• Ignorovat hodnoty NULL: Ne (tuto vlastnost lze nastavit pouze v okně Indexy)

Příkladem je číslo pracovníka, které je v celé firmě jedinečné. Setkáte se také s názvem vlastní klíč.

Pokud nemáme vlastní jednoznačné číslování, MS Access poskytuje výbornou konstrukci typu pole Automatické číslo, databáze záznamy sama očísluje v době jejich zadávání.

Běžný způsob nastavení primárního klíče je v návrhovém zobrazení tabulky, z menu NÁVRH při umístění kurzoru na požadované pole a klepnutí na ikonu Primární klíč na pásu karet. Na voliči záznamů se objeví ikona klíče.

L5: Dotazy v MS Access

Příprava tabulky

Pro numerické operace v dotazech do naší databáze db1 do tabulky tb1Pracovnik doplníme dvě pole:

• Oddeleni, typu Krátký text
• Bonus, typu číslo s vlastností jednoduchá přesnost

Pole naplníme hodnotami a tabulku uzavřeme.

Vytvoření dotazu průvodcem

V dotazu nad naší tabulkou tb1Pracovnik vypočteme součty bonusů podle oddělení.

V menu VYTVOŘENÍ v pásu karet klepneme na ikonu Průvodce dotazem. Otevře se formulář Nový dotaz, na něm zvolíme funkci Průvodce jednoduchým dotazem a klepneme na OK.

Program přejde na formulář Průvodce jednoduchým dotazem. Vybereme v položce Tabulky nebo dotazy hodnotu Tabulka: tb1Pracovnik a v seznamu Dostupná pole vybereme pole Oddeleni a Bonus. Tyto položky tlačítkem ">" přesuneme do seznamu Vybraná pole. Klepneme na Další.

V následujícím formuláři ve výběru Chcete použít dotaz podrobný nebo souhrnný? můžeme zvolit:

• Podrobný - v tomto případě by byla vybrána všechna pole z tabulky a program by přešel na konec průvodce s pojmenováním dotazu
• Souhrnný - tuto možnost však nyní budeme ignorovat.

Klepneme na Možnosti souhrnu.

Otevře se formulář Možnosti souhrnu.

V okně Jaké souhrnné hodnoty chcete vypočítat? v poli Bonus zaškrtneme Součet a v pravém dolním rohu zaškrtneme pole Počítat záznamy v tb1Pracovnik. Klepneme na OK.

Program se vrátí na předchozí formulář Průvodce jednoduchým dotazem, zde klepneme na Další. V dalším okně téhož formuláře v položce Jaký název má mít dotaz? zadáme qry0101.

jmenná konvence: abychom všude poznali, že jde o dotaz, budou názvy dotazů začínat předponou qry.

Ponecháme přepínač Otevřít dotaz pro zobrazení informací a klepneme na Dokončit.

Návrhář dotazů

V předchozí části jsme vytvořili souhrny. V této kapitole budeme vytvářet dotaz podrobný, abychom se seznámili s možnostmi návrháře dotazů detailně.

V menu VYTVOŘENÍ klepneme na ikonu Návrh dotazu na pásu karet.

V menu NÁVRH se nastaví a zvýrazní ikona Výběrový a otevře se formulář Zobrazit tabulku. Ponecháme záložku Tabulky a s kurzorem na názvu tb1Pracovnik klepneme na Přidat. Tabulka tb1Pracovnik se zobrazí na ploše okna a následně klepneme na Zavřít.

Výběr polí do dotazu provedeme jedním ze tří způsobů:

• Dvojklikem na vybraném názvu pole
• Stisknutím levého tlačítka myši na názvu pole a přetažením na požadované místo do pracovní tabulky
• Výběrem ze seznamu na požadovaném názvu pole v pracovní tabulce

Do pracovní tabulky přeneseme všechna pole z tabulky tb1Pracovnik.

Přejmenování názvu pole

V případě, že se bude dotaz zobrazovat uživateli, je možno ve výsledném zobrazení češtinu šišlavou nahradit spisovnou češtinou. Lepší bývá ponechat spisovnou češtinu až na výstupní formuláře a reporty.

Přejmenování provedeme vložením požadovaného názvu ukončeného dvojtečkou před název pole z tabulky tb1Pracovnik. V tomto případě nahradíme zobrazovaný název pole Cislo názvem Číslo pracovníka, celý zápis je potom Číslo pracovníka: Cislo.

Podmínky výběru hodnot

Omezení počtu záznamů zobrazených ve výběru je nejčastějším požadavkem při tvorbě dotazů. Výběr pouze požadované podmnožiny dosáhneme zápisem podmínky do řádku Kritéria: v pracovní tabulce. Podmínky se zapisují ve zkráceném tvaru, jak bylo uvedeno v lekci třetí - Práce s tabulkou v MS Access.

V mém příkladu uvádím omezení na jméno Jan a současně na ročník narození větší než 1990, jde o podmínky logické konjunkce, spojené logickým AND.

Předpokládám, že jste se při zadávání jmen a hodnot do tabulky tb1Pracovnik řídili vlastní fantazií a musíte si podmínky výběru tedy patřičně upravit, aby se ve výsledku dotazu nezobrazil prázdný formulář.

V menu NÁVRH klepnutím na ikonu Zobrazit přímo, nebo výběrem funkce Datový list.

Pro zápis podmínky výběru formou logické disjunkce, tedy logického NEBO, musíme provést zápis každé jedné podmínky na samostatném řádku pracovní tabulky s názvem Nebo: Zápis pro podmínky omezení na jméno Jan, nebo na ročník narození větší než 1990 je uveden na obrázku.

Úkol: Vytvořit v návrháři dotazů podobný výstup, jako jsme vytvořili v průvodci dotazem.

Návod: Použijete ikonu Souhrny a výpočetní funkce podle následujícího obrázku. Pozornost věnujte pojmenování polí a novému řádku Souhrn: