W tym artykule wyjaśniono, jak używać zapytań o najwyższe wartości i zapytań sum w celu znalezienia najnowszych lub najwcześniejszych dat w zestawie rekordów. Może to ułatwić udzielenie odpowiedzi na różne pytania biznesowe, na przykład kiedy klient ostatnio złożył zamówienie lub które pięć kwartałów najlepiej odpowiadało na sprzedaż według miast.
W tym artykule
Omówienie
Za pomocą zapytania zwracania najwyższych wartości można klasyfikować dane i przeglądać elementy sklasyfikowane jako pierwsze. Zapytanie zwraca najwyższą wartość, które zwraca określoną liczbę lub procent wartości z górnej części wyników, na przykład pięć najpopularniejszych stron w witrynie sieci Web. Zapytania zwracania najwyższych wartości można użyć w przypadku dowolnego rodzaju wartości — nie muszą to być liczby.
Jeśli chcesz pogrupować lub podsumować dane przed ich klasyfikacją, nie musisz używać zapytania zwracania najwyższych wartości. Załóżmy na przykład, że należy znaleźć numery sprzedaży dla danej daty dla każdego miasta, w którym firma działa. W takim przypadku miasta staną się kategoriami (należy znaleźć dane dla poszczególnych miast), więc należy użyć zapytania sumowego.
W przypadku wyszukiwania rekordów zawierających najpóźniejsze lub najwcześniejsze daty w tabeli lub grupie rekordów za pomocą kwerendy zwracaącej najwyższe wartości można odpowiedzieć na różne pytania biznesowe, takie jak:
-
Kto ostatnio robił najwięcej sprzedaży?
-
Kiedy klient ostatnio złozył zamówienie?
-
Kiedy w zespole są trzy następne urodziny?
Aby utworzyć zapytanie zwracane najwyższą wartość, zacznij od utworzenia zapytania wybierającego. Następnie posortuj dane według pytania — niezależnie od tego, czy szukasz początku, czy od dołu. Jeśli chcesz pogrupować lub podsumować dane, zamień zapytanie wybierające w zapytanie podsumowujące. Następnie można użyć funkcji agregowania, takiej jak Maksimum lub Minimum, aby zwrócić najwyższą lub najniższą wartość, albo funkcji First lubLast, aby zwrócić najwcześniejszą lub najpóźniejszą datę.
W tym artykule założono, że wartości dat, których używasz, mają typ danych Data/Godzina. Jeśli wartości dat znajdują się w polu tekstowym,
Rozważ użycie filtru zamiast zapytania zwracania najwyższych wartości
Filtr jest zazwyczaj lepszy, jeśli masz konkretną datę. Aby określić, czy należy utworzyć zapytanie zwracające najwyższe wartości czy zastosować filtr, rozważ następujące kwestie:
-
Aby zwrócić wszystkie rekordy, dla których data jest do określonej daty, jest wcześniejsza lub późniejsza niż data, użyj filtru. Aby na przykład wyświetlić daty sprzedaży od kwietnia do lipca, zastosuj filtr.
-
Jeśli chcesz zwrócić określoną ilość rekordów, które mają najnowsze lub najpóźniejsze daty w polu, i nie znasz dokładnych wartości dat lub nie mają one znaczenia, tworzysz zapytanie zwracane o najwyższe wartości. Aby na przykład wyświetlić pięć najlepszych kwartałów sprzedaży, użyj zapytania zwracania najwyższych wartości.
Aby uzyskać więcej informacji na temat tworzenia i używania filtrów, zobacz artykuł Stosowanie filtru do wyświetlania wybranych rekordów w bazie danych programu Access.
Przygotowywanie przykładowych danych do śledzenia wraz z przykładami
W procedurach opisanej w tym artykule są wykorzystywane dane z poniższych tabel przykładowych.
Tabela Pracownicy
Nazwisko |
Imię |
Adres |
Miasto |
KrajOrR egion |
Data urodzenia |
Data zatrudnienia |
Bochenek |
Tomasz |
ul. Klonowa 15 |
Szczecin |
Polska |
05.02.1968 |
10.06.94 |
Ciesielska |
Ewa |
ul. Studzińskiego 52 |
Poznań |
Polska |
22.05.1957 |
22.11.96 |
Chmiela |
Korzun |
ul. Niezapominajki 75 |
Wrocław |
Polska |
11.11.1960 |
11.03.00 |
Jankowski |
Marcin |
ul. Złota 1 |
Lublin |
Polska |
22.03.1964 |
22.06.98 |
Kretowicz |
Marcin |
ul. Kosmonautów 2 |
Gdańsk |
Polska |
05.06.1972 |
05.01.02 |
Olecka |
Aneta |
ul. Niezapominajek 75 |
Wrocław |
Polska |
23.01.1970 |
23.04.99 |
Czupta |
Wojciech |
ul. Fistaszkowa 67 |
Toruń |
Polska |
14.04.1964 |
14.10.04 |
Wiśniewska |
Aneta |
ul. Grzebieniowa 2 |
Białystok |
Polska |
29.10.1959 |
29.03.97 |
Tabela EventType
TypeID |
Typ zdarzenia |
1 |
Wprowadzenie produktu na rynek |
2 |
Funkcja firmowa |
3 |
Funkcja Private |
4 |
Fund Raiser |
5 |
Targi |
6 |
Wykład |
7 |
Koncert |
8 |
Wyeksona |
9 |
Ulica |
Tabela Kontrahenci
CustomerID |
Firma |
Kontakt |
1 |
Contoso, Ltd. Grafika |
Jonathan Haas |
2 |
Tailspin Toys |
Ellen Adams |
3 |
Fabrikam |
Anna Philips |
4 |
Wingtip Toys |
Lucio Iallo |
5 |
A. Datum |
Mandar Samant |
6 |
Adventure Works |
Brian Burke |
7 |
Design Institute |
Jaka Stele |
8 |
School of Fine Art |
Milena Duomanova |
Tabela Zdarzenia
Identyfikator_zdarzenia |
Typ zdarzenia |
Klient |
Data zdarzenia |
Cena |
1 |
Wprowadzenie produktu na rynek |
Contoso, Ltd. |
4/14/2011 |
10 000 zł |
2 |
Funkcja firmowa |
Firma I |
4/21/2011 |
8 000 zł |
3 |
Targi |
Firma I |
2011-05-01 |
25 000 zł |
4 |
Wyeksona |
Firma G |
5/13/2011 |
4500 zł |
5 |
Targi |
Contoso, Ltd. |
5/14/2011 |
55 000 zł |
6 |
Koncert |
School of Fine Art |
5/23/2011 |
12 000 zł |
7 |
Wprowadzenie produktu na rynek |
A. Datum |
6/1/2011 |
15 000 zł |
8 |
Wprowadzenie produktu na rynek |
Wingtip Toys |
6/18/2011 |
21 000 zł |
9 |
Fund Raiser |
Adventure Works |
6/22/2011 |
1300 zł |
10 |
Wykład |
Firma G |
6/25/2011 |
2450 zł |
11 |
Wykład |
Contoso, Ltd. |
2011-07-04 |
3800 zł |
12 |
Ulica |
Firma G |
2011-07-04 |
5 500 zł |
Uwaga: W procedurach w tej sekcji założono, że tabele Klienci i Typ zdarzenia znajdują się po stronie "jeden" relacji jeden-do-wielu z tabelą Events. W tym przypadku tabela Zdarzenia udostępnia pola CustomerID i TypeID. Zapytania sum opisane w następnych sekcjach nie będą działać bez tych relacji.
Wklejanie przykładowych danych do arkuszy programu Excel
-
Uruchom program Excel. Zostanie otworzony pusty skoroszyt.
-
Naciśnij klawisze SHIFT+F11, aby wstawić arkusz (będą potrzebne cztery).
-
Skopiuj dane z każdej przykładowej tabeli do pustego skoroszytu. Uwzględnij nagłówki kolumn (pierwszy wiersz).
Tworzenie tabel bazy danych z arkuszy
-
Zaznacz dane w pierwszym skoroszycie z uwzględnieniem nagłówków kolumn.
-
Kliknij prawym przyciskiem myszy okienko nawigacji, a następnie kliknij polecenie Wklej.
-
Kliknij przycisk Tak, aby potwierdzić, że pierwszy wiersz zawiera nagłówki kolumn.
-
Powtórz kroki od 1 do 3 dla każdego z pozostałych arkuszy.
Znajdowanie najnowszej lub najświeższej daty
W procedurach w tej sekcji są wykorzystywane przykładowe dane w celu zilustrowania procesu tworzenia zapytania zwracania najwyższych wartości.
Tworzenie podstawowego zapytania zwracającego najwyższe wartości
-
Na karcie Tworzenie w grupie Zapytania kliknij pozycję Projekt zapytania.
-
Kliknij dwukrotnie tabelę Pracownicy, a następnie kliknij przycisk Zamknij.
Jeśli używasz przykładowych danych, dodaj tabelę Pracownicy do zapytania.
-
Dodaj pola, których chcesz użyć w zapytaniu, do siatki projektu. Możesz kliknąć dwukrotnie każde pole lub przeciągnąć i upuścić poszczególne pola na puste komórki w wierszu Pole.
Jeśli używasz tabeli przykładowej, dodaj pola Imię, Nazwisko i Data urodzenia.
-
W polu zawierającym najwyższe lub najniższe wartości (w tabeli przykładowej jest to pole Data urodzenia) kliknij wiersz Sortuj i wybierz opcję Rosnąco lub Malejąco.
Kolejność sortowania malejąco zwraca najnowszą datę, a kolejność sortowania rosnąca zwraca najwcześniejszą datę.
Ważne: Wartość w wierszu sortowania należy ustawić tylko dla pól zawierających daty. Jeśli określisz kolejność sortowania dla innego pola, zapytanie nie zwróci chętnych wyników.
-
Na karcie Projektowanie w grupie Narzędzia kliknij strzałkę w dół obok pozycji Wszystkie (lista Najwyższe wartości), a następnie wprowadź liczbę rekordów, które chcesz wyświetlić, lub wybierz opcję z listy.
-
Kliknij przycisk Uruchom , aby uruchomić zapytanie i wyświetlić wyniki w widoku arkusza danych.
-
Zapisz zapytanie jako NextBirthDays.
Jak widać, ten typ zapytania zwracającego najwyższe wartości pozwala odpowiedzieć na podstawowe pytania, na przykład kto jest najstarszą lub najmłodszą osobą w firmie. W następnych krokach wyjaśniono, w jaki sposób używać wyrażeń i innych kryteriów, aby zapewnić skuteczność i elastyczność zapytania. Kryteria pokazane w kolejnym kroku zwracają daty trzech najbliższych urodzin pracowników.
Dodawanie kryteriów do zapytania
W tych krokach zostało utworzone zapytanie utworzone w poprzedniej procedurze. Zapytanie zwracane według najwyższych wartości można obserwować, o ile zawiera ono rzeczywiste dane daty/godzin, a nie wartości tekstowe.
Porada: Aby lepiej zrozumieć działanie tego zapytania, na poszczególnych etapach możesz przełączać się między widokiem projektu a widokiem arkusza danych. Jeśli chcesz wyświetlić rzeczywisty kod zapytania, przełącz się do widoku SQL. Aby przełączać się między widokami, kliknij prawym przyciskiem myszy kartę u góry zapytania, a następnie kliknij odpowiedni widok.
-
W okienku nawigacji kliknij prawym przyciskiem myszy zapytanie NextBirthDays, a następnie kliknij polecenie Widok projektu.
-
W siatce projektu zapytania w kolumnie po prawej stronie pola DataUrodze wprowadź następujące wartości:
MonthPart: DatePart("m",[DataUrodze]).
To wyrażenie wyodrębnia miesiąc z daty urodzenia przy użyciu funkcji DatePart. -
W następnej kolumnie siatki projektu zapytania wprowadź następującą wartość:
DayOfMonth Przeksięg: DatePart("d",[DataUrodzeń])To wyrażenie wyodrębnia dzień miesiąca z pola DataUrodze przy użyciu funkcji
DatePart. -
Wyczyść pola wyboru w wierszu Pokazywanie dla każdego z dwóch wprowadzonych wyrażeń.
-
Kliknij wiersz sortowania dla każdego wyrażenia, a następnie wybierz pozycję Rosnąco.
-
W wierszu Kryteria kolumny Data urodzenia wpisz następujące wyrażenie:
Month([DataUrodze]) > Month(Date()) OR Month([DataUrodze])= Month(Date()) AND Day([DataUrodze])>Day(Date())To wyrażenie
działa następująco:-
Month([DataUrodze]) > Month(Date()) określa, że data urodzenia każdego pracownika przypada w przyszłym miesiącu.
-
Month([DataUrodze])= Month(Date()) And Day([DataUrodze])>Day(Date()) określa, że jeśli data urodzenia przypada w bieżącym miesiącu, urodziny przypadają w dniu bieżącym lub po nim.
Krótko mówiąc, to wyrażenie wyklucza wszystkie rekordy, w których urodziny występują między 1 stycznia a datą bieżącą.
Porada: Aby uzyskać więcej przykładów wyrażeń kryteriów zapytań, zobacz artykuł Przykłady kryteriów zapytań.
-
-
Na karcie Projektowanie w grupie Konfiguracja kwerendy wpisz 3 w polu Zwracanie.
-
Na karcie Projektowanie w grupie Wyniki kliknij przycisk Uruchom .
Uwaga: We własnym zapytaniu korzystającym z własnych danych czasami może być więcej rekordów niż określono. Jeśli dane zawierają wiele rekordów, które mają jedną wartość należącą do najwyższych wartości, zapytanie zwróci wszystkie takie rekordy, nawet jeśli to spowoduje zwrócenie większej liczby rekordów, niż potrzebno.
Znajdowanie najnowszych lub najnowszych dat dla grup rekordów
Zapytanie sum pozwala znaleźć najwcześniejsze lub najpóźniejsze daty dla rekordów, które należą do grup, takich jak zdarzenia pogrupowane według miasta. Zapytanie sumy to zapytanie wybierające, które używa funkcji agregowania (takich jak Grupuj według, Mw,Maksimum,Licznik,Pierwszei Ostatnie) do obliczania wartości dla każdego pola wyjściowego.
Uwzględnij pole, którego chcesz użyć dla kategorii — według których chcesz pogrupować — oraz pole z wartościami, które chcesz podsumować. Jeśli uwzględnisz inne pola wyjściowe — na przykład nazwy klientów podczas grupowania według typu zdarzenia — zapytanie użyje tych pól także do grupowania grup, zmieniając wyniki tak, aby nie odpowiadali na Twoje pierwotne pytanie. Aby dodać etykiety wierszy przy użyciu innych pól, należy utworzyć dodatkowe zapytanie używające zapytania sumowego jako źródła i dodać do tego zapytania dodatkowe pola.
Porada: Tworzenie zapytań krok po krokach to bardzo skuteczna strategia odpowiadania na bardziej zaawansowane pytania. Jeśli masz problem ze znalezieniem skomplikowanego zapytania do działania, zastanów się, czy możesz je rozbić na serię prostszych zapytań.
Tworzenie kwerendy podsumowującej
W tej procedurze do odpowiedzi na to pytanie zastosowano tabelę przykładową Events i tabelę przykładową EventType:
Kiedy miało miejsce ostatnie wydarzenie każdego typu z wyjątkiem koncertów?
-
Na karcie Tworzenie w grupie Zapytania kliknij pozycję Projekt zapytania.
-
Kliknij dwukrotnie tabele Events i EventType.
Każda tabela zostanie wyświetlona w górnej sekcji projektanta zapytań. -
Kliknij dwukrotnie pole EventType tabeli EventType i pole EventDate z tabeli Events, aby dodać pola do siatki projektu zapytania.
-
W siatce projektu zapytania w wierszu Kryteria pola EventType wprowadź tekst <>Koncert.
Porada: Aby uzyskać więcej przykładów wyrażeń kryteriów, zobacz artykuł Przykłady kryteriów zapytań.
-
Na karcie Projektowanie w grupie Pokazywanie/ukrywanie kliknij pozycję Sumy.
-
W siatce projektu zapytania kliknij wiersz sumy pola EventDate, a następnie kliknij pozycję Maksimum.
-
Na karcie Projektowanie w grupie Wyniki kliknij pozycję Widok, a następnie kliknij pozycję Widok SQL.
-
W oknie SQL na końcu klauzuli SELECT, zaraz za słowem kluczowym AS, zastąp pozycję MaxOfEventDate na mostRecent.
-
Zapisz zapytanie jako MostRecentEventByType.
Tworzenie drugiego zapytania w celu dodania większej liczby danych
Aby odpowiedzieć na to pytanie, w tej procedurze użyto zapytania MostRecentEventByType z powyższej procedury:
Kto był klientem w najnowszym zdarzeniu każdego typu?
-
Na karcie Tworzenie w grupie Zapytania kliknij pozycję Projekt zapytania.
-
Na karcie Zapytania kliknij dwukrotnie zapytanie MostRecentEventByType.
-
Na karcie Tabele kliknij dwukrotnie tabelę Zdarzenia i tabelę Klienci.
-
W projektancie zapytań kliknij dwukrotnie następujące pola:
-
W tabeli Events kliknij dwukrotnie pozycję EventType (Typ zdarzenia).
-
W zapytaniu MostRecentEventByType kliknij dwukrotnie pozycję MostRecent.
-
W tabeli Klienci kliknij dwukrotnie pozycję Firma.
-
-
W siatce projektu zapytania w wierszu Sortujkolumny EventType wybierz pozycję Rosnąco.
-
Na karcie Projektowanie w grupie Wyniki kliknij przycisk Uruchom.