In diesem Artikel wird erläutert, wie Sie Spitzenwertabfragen und Summenabfragen verwenden, um die neuesten oder frühesten Datumsangaben in einer Gruppe von Datensätzen zu finden. Dies kann Ihnen helfen, eine Vielzahl geschäftlicher Fragen zu beantworten, z. B. wann ein Kunde zuletzt eine Bestellung bestellt hat oder welche fünf Quartale nach Ort am besten für den Umsatz waren.
Inhalt dieses Artikels
Übersicht
Sie können Daten bewerten und die elemente mit der höchsten Rangfolge überprüfen, indem Sie eine Spitzenwertabfrage verwenden. Eine Spitzenwertabfrage ist eine Auswahlabfrage, die eine angegebene Anzahl oder einen bestimmten Prozentwert vom Anfang der Ergebnisse zurückgibt, z. B. die fünf am häufigsten verwendeten Seiten auf einer Website. Sie können eine Spitzenwertabfrage für jede Art von Werten verwenden – sie müssen keine Zahlen sein.
Wenn Sie Ihre Daten gruppieren oder zusammenfassen möchten, bevor Sie sie rangieren, müssen Sie keine Spitzenwertabfrage verwenden. Angenommen, Sie müssen die Verkaufszahlen für ein vorgegebenes Datum für jede Stadt ermitteln, in der Ihr Unternehmen tätig ist. In diesem Fall werden die Städte zu Kategorien (Sie müssen die Daten pro Stadt suchen), weshalb Sie also eine Gesamtsummenabfrage verwenden.
Wenn Sie eine Spitzenwertabfrage verwenden, um Datensätze zu suchen, die die neuesten oder frühesten Datumsangaben in einer Tabelle oder Gruppe von Datensätzen enthalten, können Sie eine Vielzahl von Geschäftlichen Fragen beantworten, z. B. die folgenden:
-
Wer hat in letzter Zeit die meisten Umsätze gemacht?
-
Wann hat ein Kunde zuletzt eine Bestellung getätigt?
-
Wann sind die nächsten drei Geburtstage im Team?
Um eine Spitzenwertabfrage zu erstellen, erstellen Sie zunächst eine Auswahlabfrage. Sortieren Sie die Daten dann nach Ihrer Frage – ganz gleich, ob Sie nach dem oberen oder unteren Rand suchen. Wenn Sie die Daten gruppieren oder zusammenfassen müssen, verwandeln Sie die Auswahlabfrage in eine Gesamtsummenabfrage. Sie können dann eine Aggregatfunktion verwenden, z. B. Max oder Min, um den höchsten oder niedrigsten Wert zurückzukehren, oder First oder Last, um das früheste oder letzte Datum zurückzukehren.
In diesem Artikel wird davon ausgegangen, dass die verwendeten Datumswerte den Datentyp "Datum/Uhrzeit" haben. Wenn sich ihre Datumswerte in einem Textfeld befinden, .
Erwägen, einen Filter anstelle einer Spitzenwertabfrage zu verwenden
Ein Filter ist normalerweise besser, wenn Sie ein bestimmtes Datum berücksichtigen. Um zu bestimmen, ob Sie eine Spitzenwertabfrage erstellen oder einen Filter anwenden sollten, berücksichtigen Sie folgende Punkte:
-
Wenn Sie alle Datensätze zurückgeben möchten, in denen das Datum vor oder später als ein bestimmtes Datum liegt, verwenden Sie einen Filter. Um beispielsweise die Datumsangaben für Verkäufe anzuzeigen, die zwischen April und Juli liegen, wenden Sie einen Filter an.
-
Wenn Sie eine bestimmte Anzahl von Datensätzen zurückgeben möchten, die die neuesten oder neuesten Datumsangaben in einem Feld enthalten, und Sie die genauen Datumswerte nicht kennen oder keine Rolle spielen, erstellen Sie eine Spitzenwertabfrage. Wenn Sie beispielsweise die fünf besten Umsatzquartale anzeigen möchten, verwenden Sie eine Spitzenwertabfrage.
Weitere Informationen zum Erstellen und Verwenden von Filtern finden Sie im Artikel Anwenden eines Filters zum Anzeigen ausgewählter Datensätze in einer Access-Datenbank.
Vorbereiten von Beispieldaten zusammen mit den Beispielen
In den Schritten in diesem Artikel werden die Daten in den folgenden Beispieltabellen verwendet.
Tabelle "Mitarbeiter"
Nachname |
Vorname |
Adresse |
Ort |
CountryOrR egion |
Geburtsdatum |
Einstellungsdatum |
Bott |
Jörg |
Hauptstraße 1 |
Berlin |
Deutschland |
05-Feb-1968 |
10-Jun-1994 |
Potra |
Cristina |
Montagsweg 123 |
Kiel |
Deutschland |
22-Mai-1957 |
22-Nov-1996 |
Probst |
David |
3122 75th Ave. S.W. |
Frankfurt am Main |
Deutschland |
11-Nov-1960 |
11-Mrz-2000 |
Casselman |
Kevin |
Beispielallee 234 |
München |
Deutschland |
22-Mrz-1964 |
22-Jun-1998 |
Preis |
Per |
Donnerstagsgasse 345 |
Hamburg |
Deutschland |
05-Jun-1972 |
05-Jan-2002 |
Ralls |
Kim |
Beispielgasse 123 |
Frankfurt am Main |
Deutschland |
23-Jan-1970 |
23-Apr-1999 |
Rienstra |
Martina |
Mittwochsallee 345 |
Essen |
Deutschland |
14-Apr-1964 |
14-Okt-2004 |
Rivas |
Gretchen |
Donnerstagsplatz 123 |
Dortmund |
Deutschland |
29-Okt-1959 |
29-Mrz-1997 |
Die Tabelle "EventType"
TypeID |
Ereignistyp |
1 |
Produkteinführung |
2 |
Unternehmensveranstaltung |
3 |
Privatveranstaltung |
4 |
Spendenaktion |
5 |
Messeshow |
6 |
Vortrag |
7 |
Konzert |
8 |
Ausstellung |
9 |
Straßenveranstaltung |
Die Tabelle "Kunden"
CustomerID |
Firma |
Kontakt |
1 |
Contoso, Ltd. Grafik |
Kai Axford |
2 |
Tailspin Toys |
Heike Adams |
3 |
Fabrikam |
Karen Berg |
4 |
Wingtip Toys |
Lucio Iallo |
5 |
A. Datum |
Mandar Samant |
6 |
Adventure Works |
Markus Zulechner |
7 |
Design Institute |
Jaka Stele |
8 |
School of Fine Art |
Milena Doumanova |
Die Tabelle "Veranstaltungen"
EventID |
Ereignistyp |
Kunde |
Ereignisdatum |
Preis |
1 |
Produkteinführung |
Contoso, Ltd. |
4/14/2011 |
10.000 € |
2 |
Unternehmensveranstaltung |
Tailspin Toys |
4/21/2011 |
8.000 € |
3 |
Messeshow |
Tailspin Toys |
01.05.2011 |
25.000 € |
4 |
Ausstellung |
Graphic Design Institute |
5/13/2011 |
4.500 € |
5 |
Messeshow |
Contoso, Ltd. |
5/14/2011 |
55.000 € |
6 |
Konzert |
School of Fine Art |
5/23/2011 |
12.000 € |
7 |
Produkteinführung |
A. Datum |
6/1/2011 |
15.000 € |
8 |
Produkteinführung |
Wingtip Toys |
6/18/2011 |
21.000 € |
9 |
Spendenaktion |
Adventure Works |
6/22/2011 |
1.300 € |
10 |
Vortrag |
Graphic Design Institute |
6/25/2011 |
2.450 € |
11 |
Vortrag |
Contoso, Ltd. |
04.07.2011 |
3.800 € |
12 |
Straßenveranstaltung |
Graphic Design Institute |
04.07.2011 |
5.500 € |
Hinweis: Bei den Schritten in diesem Abschnitt wird vorausgesetzt, dass sich die Tabellen "Kunden" und "Veranstaltungstyp" auf der Seite "1" von 1: n-Beziehungen mit der Tabelle "Veranstaltungen" befinden. In diesem Fall nutzt die Tabelle "Veranstaltungen" dieselben Felder "CustomerID" und "TypeID". Ohne diese Beziehungen funktionieren die in den nächsten Abschnitten beschriebenen Gesamtsummenabfragen nicht.
Einfügen der Beispieldaten in Excel-Arbeitsblätter
-
Starten Sie Excel. Eine leere Arbeitsmappe wird geöffnet.
-
Drücken Sie UMSCHALT+F11, um ein Arbeitsblatt einfügen zu können (sie benötigen vier).
-
Kopieren Sie die Daten aus jeder Beispieltabelle in ein leeres Arbeitsblatt. Fügen Sie die Spaltenüberschriften (die erste Zeile) ein.
Erstellen von Datenbanktabellen aus den Arbeitsblättern
-
Wählen Sie die Daten aus dem ersten Arbeitsblatt aus, einschließlich der Spaltenüberschriften.
-
Klicken Sie mit der rechten Maustaste auf den Navigationsbereich, und klicken Sie dann auf Einfügen.
-
Klicken Sie auf Ja, um zu bestätigen, dass die erste Zeile Spaltenüberschriften enthält.
-
Wiederholen Sie die Schritte 1 bis 3 für jedes der verbleibenden Arbeitsblätter.
Suchen des aktuellsten datums
In den Schritten in diesem Abschnitt werden die Beispieldaten verwendet, um den Vorgang zum Erstellen einer Spitzenwertabfrage zu veranschaulichen.
Erstellen einer einfachen Spitzenwertabfrage
-
Klicken Sie auf der Registerkarte Erstellen in der Gruppe Abfragen auf Abfrageentwurf.
-
Doppelklicken Sie auf die Tabelle Mitarbeiter, und klicken Sie dann auf Schließen.
Wenn Sie die Beispieldaten verwenden, fügen Sie der Abfrage die Tabelle "Mitarbeiter" hinzu.
-
Fügen Sie die Felder, die Sie in Ihrer Abfrage verwenden möchten, dem Entwurfsbereich hinzu. Sie können auf jedes Feld doppelklicken, oder Sie ziehen jedes Feld in eine leere Zelle in der Zeile Feld und legen es dort ab.
Wenn Sie die Beispieltabelle verwenden, fügen Sie die Felder "Vorname", "Nachname" und "Geburtsdatum" hinzu.
-
Klicken Sie in dem Feld, das Ihre oberen oder unteren Werten (das Feld "Geburtsdatum", wenn Sie die Beispieltabelle verwenden) enthält, auf die Zeile Sortieren, und wählen Sie entweder Aufsteigend oder Absteigend aus.
Die Sortierreihenfolge "Absteigend" gibt das jüngste Datum zurück, während "Aufsteigend" das früheste Datum zurückgibt.
Wichtig: Sie müssen in der Zeile Sortieren nur für Felder einen Wert festlegen, die Ihre Datumswerte enthalten. Wenn Sie eine Sortierreihenfolge für ein anderes Feld angeben, gibt die Abfrage nicht die gewünschten Ergebnisse zurück.
-
Klicken Sie auf der Registerkarte Entwurf in der Gruppe Tools auf den Abwärtspfeil neben Alle (die Spitzenwerte-Liste), und geben Sie die Anzahl der Datensätze ein, die Sie anzeigen möchten, oder wählen Sie eine Option in der Liste aus.
-
Klicken Sie auf Ausführen , um die Abfrage auszuführen und die Ergebnisse in der Datenblattansicht anzuzeigen.
-
Speichern Sie die Abfrage als NextBirthDays.
Sie können sehen, dass Sie mit diesem Typ von Spitzenwertabfrage grundlegende Fragen beantworten können (beispielsweise nach der ältesten oder jüngsten Person im Unternehmen). Im nächsten Schritt wird erläutert, wie Sie Ausdrücke und andere Kriterien verwenden, um die Leistung und Flexibilität der Abfrage zu steigern. Die im nächsten Schritt dargestellten Kriterien geben die nächsten drei Mitarbeitergeburtstage zurück.
Hinzufügen von Kriterien zur Abfrage
Bei diesen Schritten wird die abfrage verwendet, die im vorherigen Verfahren erstellt wurde. Sie können zusammen mit einer anderen Spitzenwertabfrage folgen, solange sie tatsächliche Datums-/Uhrzeitdaten und keine Textwerte enthält.
Tipp: Wenn Sie besser verstehen möchten, wie diese Abfrage funktioniert, wechseln Sie in jedem Schritt zwischen Entwurfsansicht und Datenblattansicht. Wenn Sie den tatsächlichen Abfragecode anzeigen möchten, wechseln Sie zur SQL Ansicht. Wenn Sie zwischen Ansichten wechseln möchten, klicken Sie mit der rechten Maustaste auf die Registerkarte am oberen Rand der Abfrage, und klicken Sie dann auf die ansicht, die Sie wünschen.
-
Klicken Sie im Navigationsbereich mit der rechten Maustaste auf die Abfrage NextBirthDays, und klicken Sie dann auf Entwurfsansicht.
-
Geben Sie im Abfrageentwurfsraster in der Spalte rechts neben Geburtsdatum Folgendes ein:
MonthBorn: DatePart("m";[BirthDate]).
Dieser Ausdruck extrahiert den Monat aus "Geburtsdatum" mithilfe der Funktion "DatePart". -
Geben Sie in der nächsten Spalte des Abfrageentwurfsrasters Folgendes ein:
DayOfMonthBorn: DatePart("d";[Geburtsdatum])Dieser Ausdruck extrahiert den Tag des Monats aus BirthDate mithilfe der
Funktion DatePart. -
Aktivieren Sie die Kontrollkästchen in der Zeile Anzeigen für jeden der beiden Ausdrücke, die Sie gerade eingegeben haben.
-
Klicken Sie für jeden Ausdruck auf die Zeile Sortieren, und wählen Sie Aufsteigend aus.
-
Geben Sie in der Zeile Kriterien der Spalte Geburtsdatum den folgenden Ausdruck ein:
Monat([Geburtsdatum]) > Monat(Datum()) ODER Monat([Geburtsdatum])= Monat(Datum()) UND Tag([Geburtsdatum])>Tag(Datum())Dieser Ausdruck führt folgendes
aus:-
Monat( [Geburtsdatum]) > Monat(Datum()) gibt an, dass das Geburtsdatum jedes Mitarbeiters in einem zukünftigen Monat liegt.
-
DerMonat([Geburtsdatum])= Monat(Datum()) und Tag([Geburtsdatum])>Tag(Datum()) gibt an, dass der Geburtstag auf oder nach dem aktuellen Tag fällt, wenn das Geburtsdatum im aktuellen Monat eintritt.
Kurz gesagt: Dieser Ausdruck schließt alle Datensätze aus, bei denen der Geburtstag zwischen dem 1. Januar und dem aktuellen Datum stattfindet.
Tipp: Weitere Beispiele für Abfragekriterienausdrücke finden Sie im Artikel Beispiele für Abfragekriterien.
-
-
Geben Sie auf der Registerkarte Entwurf in der Gruppe Abfrageeinrichtung im Feld Rückgabe den Typ 3ein.
-
Klicken Sie auf der Registerkarte Entwurf in der Gruppe Ergebnisse auf Ausführen .
Hinweis: In Ihrer eigenen Abfrage, die Ihre eigenen Daten verwendet, werden manchmal mehr Datensätze angezeigt, als Sie angegeben haben. Wenn Ihre Daten mehrere Datensätze enthalten, die einen Wert gemeinsam nutzen, der zu den obersten Werten gehört, gibt Ihre Abfrage alle diese Datensätze zurück, auch wenn dies bedeutet, dass mehr Datensätze zurückgegeben werden, als Sie wollten.
Suchen der aktuellsten oder aktuellsten Datumsangaben für Gruppen von Datensätzen
Sie verwenden eine Gesamtsummenabfrage, um die frühesten oder spätesten Datumsangaben für Datensätze zu finden, die in Gruppen fallen, z. B. Nach Ort gruppierende Ereignisse. Eine Gesamtsummenabfrage ist eine Auswahlabfrage, die Aggregatfunktionen verwendet (z. B.Gruppieren nach, Min, Max, Anzahl, Ersterund Letzter), um Werte für jedes Ausgabefeld zu berechnen.
Fügen Sie das Feld ein, das Sie für Kategorien verwenden möchten – nach gruppieren – und das Feld mit Werten, die Sie zusammenfassen möchten. Wenn Sie andere Ausgabefelder enthalten , z. B. die Namen von Kunden, wenn Sie nach Ereignistyp gruppieren, verwendet die Abfrage diese Felder auch, um Gruppen zu erstellen und die Ergebnisse so zu ändern, dass sie Ihre ursprüngliche Frage nicht beantworten. Um die Zeilen mit anderen Feldern zu beschriften, erstellen Sie eine zusätzliche Abfrage, die die Gesamtsummenabfrage als Quelle verwendet, und fügen der Abfrage die zusätzlichen Felder hinzu.
Tipp: Das Erstellen von Abfragen in Schritten ist eine sehr effektive Strategie zum Beantworten fortgeschrittener Fragen. Wenn Sie Probleme haben, eine komplizierte Abfrage zur Arbeit zu bringen, überlegen Sie, ob Sie sie in eine Reihe einfacherer Abfragen aufbrechen können.
Erstellen einer Gesamtsummenabfrage
Bei diesem Verfahren werden die Beispieltabelle Ereignisse und die Beispieltabelle EventType verwendet, um diese Frage zu beantworten:
Wann war das letzte Ereignis jedes Ereignistyps, ohne Konzerte?
-
Klicken Sie auf der Registerkarte Erstellen in der Gruppe Abfragen auf Abfrageentwurf.
-
Doppelklicken Sie auf die Tabellen Ereignisse und Ereignistyp.
Jede Tabelle wird im oberen Abschnitt des Abfrage-Designers angezeigt. -
Doppelklicken Sie in der Tabelle Ereignisse auf das Feld EventType der Tabelle EventType und auf das Feld Ereignisdatum, um die Felder dem Abfrageentwurfsraster hinzuzufügen.
-
Geben Sie im Abfrageentwurfsraster in der Zeile Kriterien des Felds EventType den Wert <>Concert ein.
Tipp: Weitere Beispiele für Kriterienausdrücke finden Sie im Artikel Beispiele für Abfragekriterien.
-
Klicken Sie auf der Registerkarte Entwurf in der Gruppe Einblenden/Ausblenden auf Summen.
-
Klicken Sie im Abfrageentwurfsraster auf die Zeile Summe des Felds "Ereignisdatum", und klicken Sie dann auf Max.
-
Klicken Sie auf der Registerkarte Entwurf in der Gruppe Ergebnisse auf Ansicht, und klicken Sie dann auf SQL-Ansicht.
-
Ersetzen Sie im SQL-Fenster am Ende der SELECT-Klausel direkt nach dem SCHLÜSSELWORT AS MaxOfEventDate durch MostRecent.
-
Speichern Sie die Abfrage als MostRecentEventByType.
Erstellen einer zweiten Abfrage, um weitere Daten hinzuzufügen
Bei diesem Verfahren wird die MostRecentEventByType-Abfrage aus dem vorherigen Verfahren verwendet, um diese Frage zu beantworten:
Wer war der Kunde beim letzten Ereignis jedes Ereignistyps?
-
Klicken Sie auf der Registerkarte Erstellen in der Gruppe Abfragen auf Abfrageentwurf.
-
Doppelklicken Sie auf der Registerkarte Abfragen auf die MostRecentEventByType-Abfrage.
-
Doppelklicken Sie auf der Registerkarte Tabellen auf die Tabelle Ereignisse und die Tabelle Kunden.
-
Doppelklicken Sie im Abfrage-Designer auf die folgenden Felder:
-
Doppelklicken Sie in der Tabelle Ereignisse auf EventType.
-
Doppelklicken Sie in der MostRecentEventByType-Abfrage auf MostRecent.
-
Doppelklicken Sie in der Tabelle Kunden auf Unternehmen.
-
-
Wählen Sie im Abfrageentwurfsraster in der Zeile Sortieren der Spalte EventType die Option Aufsteigend aus.
-
Klicken Sie auf der Registerkarte Entwurf in der Gruppe Ergebnisse auf Ausführen.