Tento článek vysvětluje, jak v sadě záznamů pomocí dotazů nejvyšších hodnot a dotazů souhrnů najít nejnovější nebo nejnovější data. To vám může pomoct odpovědět na různé obchodní otázky, třeba kdy zákazník naposledy umístil objednávku nebo která pět čtvrtletí byla pro prodeje podle města nejlepší.
V tomto článku
Základní informace
Pomocí dotazu nejvyšších hodnot můžete data seřadit a zkontrolovat položky s nejvyšším pořadím. Dotaz s nejvyšší hodnotou je výběrové dotazy, které vrací určený počet nebo procenta hodnot v horní části výsledků, například pět nejoblíbenějších stránek na webu. Dotaz na nejvyšší hodnoty můžete použít pro libovolný druh hodnot – nemusí to být čísla.
Pokud chcete data před jejich pořadím seskupit nebo shrnout, nemusíte použít dotaz s nejvyššími hodnotami. Předpokládejme například, že potřebujete najít čísla prodejů pro dané datum pro každé město, ve kterém vaše společnost působí. V takovém případě se města stanou kategoriemi (data musíte vyhledat podle města), takže použijete celkový dotaz.
Když k vyhledání záznamů obsahujících nejnovější nebo nejstarší data v tabulce nebo skupině záznamů použijete dotaz s nejvyššími hodnotami, můžete zodpovědět různé obchodní otázky, například tyto:
-
Kdo v poslední době dělá nejvíc prodejů?
-
Kdy zákazník naposledy poslal objednávku?
-
Kdy budou další tři narozeniny v týmu?
Pokud chcete vytvořit dotaz s nejvyšší hodnotou, začněte vytvořením výběrových dotazů. Potom data seřate podle otázky – jestli hledáte horní nebo dolní část. Pokud potřebujete data seskupit nebo shrnout, vyberte dotaz na souhrnný dotaz. Agregační funkci, například Max nebo Min, pak můžete použít k vrácení nejvyšší nebo nejnižší hodnoty, nebo funkce First nebo Last, která vrátí nejdřívější nebo nejnovější datum.
V tomto článku se předpokládá, že hodnoty dat, které používáte, mají datový typ Datum a čas. Pokud jsou hodnoty dat v textovém poli,
Místo dotazu nejvyšších hodnot zvažte použití filtru.
Filtr je obvykle lepší, pokud byste měli na paměti konkrétní datum. Pokud chcete zjistit, jestli máte vytvořit dotaz na nejvyšší hodnoty nebo použít filtr, zvažte následující postup:
-
Pokud chcete vrátit všechny záznamy, ve kterých se datum shoduje, před nebo později než určité datum, použijte filtr. Pokud například chcete zobrazit data prodeje od dubna do července, použijte filtr.
-
Pokud chcete vrátit určité množství záznamů, které mají v poli nejnovější nebo nejaktuálnější data, a neznají jste přesné hodnoty kalendářních dat nebo na datech nezáleží, vytvoříte dotaz na nejvyšší hodnoty. Pokud například chcete zobrazit pět nejlepších prodejních čtvrtletí, použijte dotaz s nejvyššími hodnotami.
Další informace o vytváření a používání filtrů najdete v článku Použití filtru k zobrazení výběrových záznamů v databázi Accessu.
Příprava ukázkových dat na zpracování společně s příklady
Kroky v tomto článku používají data v následujících ukázkových tabulkách.
Tabulka Zaměstnanci
LastName |
Jméno |
Adresa |
Město |
CountryOrr egion |
Datum narození |
Datum platnosti platnosti najmouti |
Klčo |
René |
1 Main St. |
New York |
USA |
05.02.1968 |
10.čen 1994 |
Heloo |
Waleed |
52 1st St. |
Boston |
USA |
22.05.1957 |
22.11.1996 |
Veselý |
Guido |
3122 75th Ave. S.W. |
Seattle |
USA |
11.11.1960 |
11.03.2000 |
Bagel |
Předsudky |
1 Contoso Blvd. |
Londýn |
UK |
22.03.1964 |
22.6.1998 |
Cena |
Julián |
Calle Smith 2 |
Mexico City |
Mexiko |
05-červen-1972 |
05.01.2002 |
Hughes |
Do zaměs |
3122 75th St. S. |
Seattle |
USA |
23.01.1970 |
23.04.1999 |
Riley |
Petr |
67 Big St. |
Tampa |
USA |
14.04.1964 |
14.října 2004 |
Birkby |
Lenka |
2 Nosey Pkwy |
Brno |
USA |
29. října 1959 |
29.03.1997 |
Tabulka EventType
TypeID |
Typ události |
1 |
Uvedení produktu na trh |
2 |
Funkce Corporate |
3 |
Private (funkce) |
4 |
Fund Raiser |
5 |
Veletrh |
6 |
Přednáška |
7 |
Koncert |
8 |
Vystavené |
9 |
Street Fair |
Tabulka Zákazníci
CustomerID |
Společnost |
Kontakt |
1 |
Contoso, Ltd. Grafika |
Jonathan Haas |
2 |
Tailspin Toys |
Ellen Adams |
3 |
Fabrikam |
Carol Philips |
4 |
Wingtip Toys |
Lucio Ialla |
5 |
A. Datum |
Mandar Samant |
6 |
Adventure Works |
Brian Mikšková |
7 |
Design Institute |
Jaka Stele |
8 |
Škola jemného klipartu |
Milena Duomanova |
Tabulka Události
ID události |
Typ události |
Zákazník |
Datum události |
Cena |
1 |
Uvedení produktu na trh |
Contoso, Ltd. |
4/14/2011 |
100 000 Kč |
2 |
Funkce Corporate |
Tailspin Toys |
4/21/2011 |
80 000 Kč |
3 |
Veletrh |
Tailspin Toys |
01.05.11 |
25 000 Kč |
4 |
Vystavené |
Graphic Design Institute |
5/13/2011 |
4 500 Kč |
5 |
Veletrh |
Contoso, Ltd. |
5/14/2011 |
55 000 Kč |
6 |
Koncert |
Škola jemného klipartu |
5/23/2011 |
12 000 Kč |
7 |
Uvedení produktu na trh |
A. Datum |
6/1/2011 |
150 000 Kč |
8 |
Uvedení produktu na trh |
Wingtip Toys |
6/18/2011 |
21 000 Kč |
9 |
Fund Raiser |
Adventure Works |
6/22/2011 |
1 300 Kč |
10 |
Přednáška |
Graphic Design Institute |
6/25/2011 |
2 450 Kč |
11 |
Přednáška |
Contoso, Ltd. |
4.7.2011 |
3 800 Kč |
12 |
Street Fair |
Graphic Design Institute |
4.7.2011 |
5 500 Kč |
Poznámka: Při postupu v této části se předpokládá, že tabulky Customers a Event Type (Zákazníci a Typ události) se nacházejí na straně 1 relace 1:N s tabulkou Events. V tomto případě tabulka Events sdílí pole CustomerID a TypeID. Celkové dotazy popsané v dalších oddílech nebudou bez těchto relací fungovat.
Vložení ukázkových dat do listů aplikace Excel
-
Spusťte aplikaci Excel. Otevře se prázdný sešit.
-
Stisknutím kláves SHIFT+F11 vložte list (budete potřebovat čtyři).
-
Zkopírujte data z každé ukázkové tabulky do prázdného listu. Zahrnovat záhlaví sloupců (první řádek).
Vytvoření databázových tabulek z listů
-
Vyberte data z prvního listu včetně záhlaví sloupců.
-
Klikněte pravým tlačítkem myši na navigační podokno a poté klikněte na příkaz Vložit.
-
Kliknutím na Ano potvrďte, že první řádek obsahuje záhlaví sloupců.
-
Opakujte kroky 1 až 3 pro každý ze zbývajících listů.
Vyhledání nejnovějšího nebo nejnovějšího data
Kroky v této části ukazují proces vytvoření dotazu nejvyšších hodnot pomocí ukázkových dat.
Vytvoření dotazu na základní hodnoty
-
Na kartě Vytvoření klikněte ve skupině Dotazy na Návrh dotazu.
-
Poklikejte na tabulku Zaměstnanci a potom klikněte na Zavřít.
Pokud používáte ukázková data, přidejte do dotazu tabulku Zaměstnanci.
-
Přidejte pole, která chcete v dotazu použít, do návrhové mřížky. Můžete poklikejte na každé pole nebo jednotlivá pole přetáhnout do prázdné buňky v řádku pole.
Pokud použijete ukázkovou tabulku, přidejte pole Jméno, Příjmení a Datum narození.
-
V poli, které obsahuje nejvyšší nebo nejnižší hodnoty (pole Datum narození, pokud používáte ukázkovou tabulku), klikněte na řádek Seřadit a vyberte vzestupně nebosestupně.
Sestupné pořadí řazení vrátí nejnovější datum a vzestupné pořadí řazení vrátí nejdřívější datum.
Důležité informace: Hodnotu v řádku Seřadit je nutné nastavit pouze pro pole obsahující data. Pokud zadáte pořadí řazení pro jiné pole, dotaz nevrátí výsledky, které chcete.
-
Na kartě Návrh klikněte ve skupině Nástroje na šipku dolů vedle položky Vše (seznam Nejvyšší hodnoty) a zadejte počet záznamů, které chcete zobrazit, nebo vyberte některou možnost ze seznamu.
-
Kliknutím na spustit dotaz a zobrazit výsledky v zobrazení Datový list.
-
Uložte dotaz jako NextThDays.
Vidíte, že tento typ dotazu s nejvyššími hodnotami může odpovídat na základní otázky, jako je například to, kdo je nejstarší nebo nejmenší osoba ve společnosti. Další kroky vysvětlují, jak se výrazy a další kritéria používají k přidání mocnosti a flexibility dotazu. Kritéria uvedená v dalším kroku vrátí další tři narozeniny zaměstnanců.
Přidání kritérií do dotazu
Pomocí tohoto postupu se používá dotaz vytvořený v předchozím postupu. Můžete sledovat dotaz na různé nejvyšší hodnoty, pokud obsahuje skutečná data data a času, ne textové hodnoty.
Tip: Pokud chcete lépe pochopit, jak tento dotaz funguje, přepněte v každém kroku mezi návrhovém zobrazením a zobrazením Datový list. Pokud chcete zobrazit skutečný kód dotazu, přepněte do zobrazení SQL. Pokud chcete přepnout mezi zobrazeními, klikněte pravým tlačítkem myši na kartu v horní části dotazu a pak klikněte na zobrazení, které chcete použít.
-
V navigačním podokně klikněte pravým tlačítkem myši na dotaz NextThDays a potom klikněte na návrhové zobrazení.
-
V návrhové mřížce dotazu zadejte ve sloupci napravo od DataNarození toto:
Month Grid: DatePart("m";[DatumNarození]).
Tento výraz extrahuje měsíc z data narození pomocí funkce DatePart. -
Do dalšího sloupce návrhové mřížky dotazu zadejte tento řetězec:
DayOfMonthMi: DatePart("d",[DatumNarození])Tento výraz extrahuje den v měsíci z data narození pomocí funkce
DatePart. -
Zrušte zaškrtnutí políček v řádku Zobrazit pro každý ze dvou výrazů, které jste právě zadali.
-
Klikněte na řádek Seřadit pro každý výraz a potom vyberte Vzestupně.
-
Na řádku Kritéria ve sloupci Datum narození zadejte tento výraz:
Month([Datum narození]) > Month(Date()) OR Month([Datum narození])= Month(Date()) AND Day([Datum narození])>Day(Date())Tento výraz dělá
toto:-
Month( [Datum narození]) > Month(Date()) určuje, že datum narození každého zaměstnance spadá v budoucím měsíci.
-
The Month([Birth Date])= Month(Date()) And Day([Birth Date])>Day(Date()) specifies that if the birth date occurs in the current month, the birthday falls on or after the current day.
Tento výraz ve krátké době vyloučí všechny záznamy, ve kterých datum narození je mezi 1. lednem a aktuálním datem.
Tip: Další příklady výrazů kritérií dotazů najdete v článku Příklady kritérií dotazů.
-
-
Na kartě Návrh ve skupině Nastavení dotazu zadejte do pole Zpět hodnotu 3.
-
Na kartě Návrh klikněte ve skupině Výsledky na tlačítko Spustit .
Poznámka: Ve vlastním dotazu používajícím vlastní data se někdy může zobrazit více záznamů, než jste zadali. Pokud data obsahují více záznamů, které sdílejí hodnotu, která patří mezi nejvyšší hodnoty, dotaz vrátí všechny takové záznamy, i když to znamená vracet více záznamů, než jste chtěli.
Vyhledání nejnovějšího nebo nejpou šlehovnějšího data pro skupiny záznamů
Pomocí součtových dotazů můžete najít nejdřívější nebo nejnovější data záznamů, které spadají do skupin, například události seskupené podle města. Souhrnný dotaz je výběrový dotaz, který k výpočtu hodnot pro jednotlivá výstupní pole používá agregační funkce (například Group By, Min,Max,Count,Firsta Last).
Zahrňte pole, které chcete použít pro kategorie (pro seskupení podle), a pole s hodnotami, které chcete shrnout. Pokud zahrňte i další výstupní pole – třeba jména zákazníků při seskupování podle typu události – dotaz také použije tato pole k seskupení a změní výsledky tak, aby neodpoví na vaši původní otázku. Pokud chcete k řádkům přidat popisek pomocí jiných polí, vytvořte další dotaz, který použije jako zdroj souhrnů dotaz, a přidejte do tohoto dotazu další pole.
Tip: Vytváření dotazů v krocích je velmi efektivní strategie pro odpovídání pokročilejších otázek. Pokud máte se složitým dotazem problémy, zvažte, jestli by se vám ne podařovalo rozdělit do řady jednodušších dotazů.
Vytvoření souhrnného dotazu
Tento postup používá k odpovědi na tuto otázku ukázkovou tabulku Events a ukázkovou tabulku EventType:
Kdy byla poslední událost každého typu události s výjimkou toho, že se jedna o událost nedá zadat?
-
Na kartě Vytvoření klikněte ve skupině Dotazy na Návrh dotazu.
-
Poklikejte na tabulky Events a EventType.
Každá tabulka se zobrazí v horní části návrháře dotazů. -
Poklikejte na pole EventType tabulky EventType a pole EventDate z tabulky Events a přidejte pole do návrhové mřížky dotazu.
-
V návrhové mřížce dotazu zadejte do řádku Kritéria pole EventType výraz <>koncert.
Tip: Další příklady výrazů kritérií najdete v článku Příklady kritérií dotazů.
-
Na kartě Návrh ve skupině Zobrazit nebo skrýt klikněte na Součty.
-
V návrhové mřížce dotazu klikněte v poli DatumUdávky na řádek Souhrn a potom klikněte na Maximum.
-
Na kartě Návrh klikněte ve skupině Výsledky na položku Zobrazení a potom na položku Zobrazení SQL.
-
V okně SQL nahraďte na konci klauzule SELECT za klíčovým slovem AS MaxOfEventDatevýrazem MostRecent.
-
Uložte dotaz jako MostRecentEventByType.
Vytvoření druhého dotazu pro přidání dalších dat
Tento postup používá k odpovědi na tuto otázku dotaz MostRecentEventByType z předchozího postupu:
Kdo byl zákazníkem u nejnovější události každého typu události?
-
Na kartě Vytvoření klikněte ve skupině Dotazy na Návrh dotazu.
-
Na kartě Dotazy poklikejte na dotaz MostRecentEventByType.
-
Na kartě Tabulky poklikejte na tabulku Události a na tabulku Zákazníci.
-
V návrháři dotazu poklikejte na následující pole:
-
V tabulce Events poklikejte na EventType.
-
V dotazu MostRecentEventByType poklikejte na MostRecent.
-
V tabulce Zákazníci poklikejte na Položku Společnost.
-
-
V návrhové mřížce dotazu vyberte v řádku Seřadit ve sloupci EventTypemožnost Vzestupně.
-
Na kartě Návrh klikněte ve skupině Výsledky na tlačítko Spustit.