Tipy pro MS Access - práce s dotazy

Výběr pouze určitého počtu záznamů
- K zobrazení určitého počtu záznamů s nejvyšší či nejnižší hodnotou jako

příklad můžeme uvést seznam 20 prodejců s nejvyšší provizí použijte následující

postup: V návrhovém zobrazení zvolte pole, která mají být zobrazena (např.

příjmení prodejce a provize), dále zvolte Řadit sestupně podle pole provize, a

v okně Vlastnosti dotazu zadejte hodnotu 20 v poli Nejvyšší hodnoty (v tomto

poli můžete zadat i údaj v procentech, např. 5 %).

- Pokud chcete, aby uživatel při spuštění dotazu k výběru záznamů měl možnost

zvolit parametr, třeba příjmení prodejce, použijte tento postup: V návrhové

mřížce dotazu v políčku Kritéria u pole prijmeni zapište text (v hranatých

závorkách [Zadejte jméno prodejce:]), který se zobrazí v dialogovém okně pro

zadání parametru před vlastním provedením dotazu (viz obr. 1).

- Access dovoluje vnořovat dotazy SELECT; např. ke zjištění prodejců, kteří

vyhovují podmínkám jiného dotazu (Dotaz2), můžete použít klíčové slovo EXISTS



SELECT prijmeni, skupina FROM Prodejci WHERE EXISTS

SELECT * FROM dotaz2 WHERE Prodejci.Prijmeni=Prijmeni);



Použití dotazů v programovém kódu

- Dotaz uložený v databázi (např. qryProdejci) můžete spustit v programu

příkazem DoCmd.OpenQuery „qryProdejci“ nebo dotaz přímo zapište DoCmd.RunSQL

„DELETE * FROM prodejci WHERE obrat <5000“ nebo Dbs.Execute „SELECT * FROM

prodejci WHERE obrat>0 ORDER BY obrat DESC“ (kde Dbs je proměnná typu Database).

- Nechcete-li, aby se před provedením dotazu objevilo okno s upozorněním a

nutností následného potvrzení (viz obr. 2), použijte metodu SetWarnings příkazu

DoCmd. Probíhá-li dotaz delší dobu, použijte k zobrazení hodin metodu

Hourglass. Ukázka použití by mohla vypadat takto:



DoCmd.Hourglass True

DoCmd. SetWarningsFalse

…

DoCmd.OpenQuery"qryProdejci"

…

DoCmd.Set WarningsTrue

DoCmd. Hourglass False



- Chcete-li otevřít množinu záznamů na základě dotazu, můžete použít metodu

OpenRecordset a jako parametr zadat dotaz SQL; např.:



Dim dbs As DATABASE, rst As Recordset, dotaz As

String

Set dbs = CurrentDb

dotaz = „SELECT * FROM Smlouvy WHERE ([Vysledek] =

\storno\)“

Set rst = dbs.OpenRecordset(dotaz)



- Při použití dotazu, který obsahuje údaj typu datum, musíte tento údaj zapsat

ve formátu používaném v USA (tedy mm/dd/yy). Špatně je tedy příkaz SELECT *

FROM prodejci WHERE ([Datum_nar] >= # 11.1.1968#), správně pak příkaz

([Datum_nar] >= #1/11/1968#\). Pokud v dotazu používáte proměnnou, velmi

užitečné je k převodu formátu datumu použít funkci Format.



Křížové dotazy

- Nejjednodušší způsob, jak vytvořit křížový dotaz, je použití průvodce (v okně

Nový dotaz zvolíte Průvodce křížovým dotazem). V jednotlivých krocích zvolíte

potřebné parametry; definici dotazu můžete samozřejmě dodatečně pozměnit v

návrhovém zobrazení nebo přímo kódu SQL (Access obsahuje příkaz TRANSFORM navíc

oproti normě ANSI SQL).



Agregační funkce

- Velmi užitečné při používání dotazů jsou agregační funkce Avg, Count, Min,

Max, Sum a statistické funkce pro směrodatnou odchylku a rozptyl StDev a Var (a

jejich odhady StDevP a VarP). Při použití těchto funkcí však nelze použít

klauzuli DISTINCT, např. SELECT COUNT(DISTINCT id_prodejce) FROM prodeje pro

zjištění počtu prodejců, kteří uskutečnili alespoň jeden obchod.

- Zadáte-li jako parametr agregační funkce Count název určitého pole (např.

Count([jmeno])), budou do součtu zahrnuty pouze záznamy, jejichž pole jmeno

není prázdné (tedy neobsahuje hodnotu NULL). Chcete-li zjistit počet všech

záznamů, použijte Count(*). Počet všech záznamů s neprázdnými hodnotami v

polích jmeno a prijmeni zároveň pak zjistíte funkcí Count([jmeno]&([prijmeni]).

- Na rozdíl od funkce Count funkce Sum (např. Sum([plat])) do výsledného součtu

zahrne i záznamy s hodnotou NULL.

- Funkce na výpočet aritmetického průměru Avg do výpočtu nezahrne hodnoty NULL.

Při výpočtu průměrného věku obchodních partnerů je použití Avg([vek] v pořádku,

neboť partneři s neznámým stářím prázdnou hodnotou v poli vek nejsou do výpočtu

zahrnuti. Chybou však je výpočet průměrného počtu objednávek prodejců, kdy

hodnota NULL „reprezentuje“ nulový počet objednávek. V tomto případě použijte

funkci Nz; tedy místo Avg([poc_obj]) použijte Avg(Nz([poc_obj])).



Další

- Přidáváte-li záznamy do tabulky, která obsahuje pole typu Automatické číslo,

budou přidané záznamy číslovány od nejvyšší hodnoty zvětšené o jednu tohoto

pole v cílové tabulce (pokud jste ovšem předtím nesmazali záznamy v cílové

tabulce s nejvyššími hodnotami bez provedení následné komprimace).

- Ke zjištění záznamů, jež existují v jedné tabulce a nejsou v tabulce druhé;

např. prodejci, kteří neuzavřeli ani jednu objednávku (tabulky Prodejci a

Objednavky, vazba přes pole ID_prodejce), použijte buď Průvodce vyhledávacím

dotazem (chybějící záznamy), nebo v návrhovém zobrazení vytvořte dotaz (u pole

smlouvy.ID_Prodejce zadejte Zobrazit Ne, Kritéria Null). Jiná možnost je v

zobrazení SQL přímo zapsat příkaz SELECT prodejci.* FROM prodejci LEFT JOIN

objednavky ON Prodejci.ID_Prodejce = Smlouvy.ID_Prodejce WHERE ((smlouvy.

ID_Prodejce IS NULL)).

- Chcete-li zobrazit tzv. vypočítávané pole, např. cenu s DPH, a v tabulce máte

cenu bez DPH (pole Cena), zadejte v mřížce návrhu do políčka Pole text Cena s

DPH: [Cena]*1,22. Text Cena s DPH pak tvoří název vypočítávaného pole.



9 0047/OK