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);



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])).





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 TrueDoCmd.SetWarnings

False…DoCmd.OpenQuery „qryProdejci“…DoCmd.SetWarnings

TrueDoCmd.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 StringSet 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 .



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ávanho pole.