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.