Kreuztabellen-Abfragen erlauben die Gruppierung von Daten nach zwei Kriterien, die als Spalten- und Zeilenköpfe dargestellt werden. Solange Sie eine solche Abfrage manuell und statisch erstellen, können Sie diese mit aussagekräftigen Spaltenüberschriften versehen. Sobald die Daten jedoch etwa per Parameter gefiltert werden sollen, wird es kompliziert, denn auch die Spaltenüberschriften müssen dann dynamisch angepasst werden. Dieser Beitrag zeigt Möglichkeiten, dieses Problem zu lösen.
Wenn Sie eine Kreuztabelle aufbauen, welche die Umsätze für alle Kunden nach Monaten gruppieren und summieren soll, legen Sie für die Spaltenüberschriften der Kreuztabellen-Abfrage einen Ausdruck wie den folgenden fest:
Bestellung: Monat([Bestelldatum]) & "/" & Jahr([Bestelldatum])
Dies führt im Ergebnis zwar zur korrekten Anzeige der Spaltenüberschriften (s. Bild 1).
Bild 1: Kreuztabellen-Abfrage mit unsortierten Spaltenüberschriften
Allerdings werden die Überschriften alphabetisch sortiert und nicht nach Jahr und Monat, wie es eigentlich sein sollte.
Nun: Wir haben es der Kreuztabellen-Abfrage ja auch nicht mitgeteilt. Dies erledigen Sie, indem Sie für die Eigenschaft Fixierte Spaltenüberschriften einen Ausdruck wie den folgenden eintragen (s. Bild 2):
Bild 2: Entwurf der Kreuztabellen-Abfrage mit fixierten Spaltenüberschriften
"7/2011";"8/2011";"9/2011";...
Das Ergebnis stellt uns zunächst zufrieden, denn die Spaltenüberschriften werden nun nach Jahr und Monat sortiert (s. Bild 3).
Bild 3: Kreuztabelle mit korrekt sortierten Spaltenüberschriften
Dabei müssen Sie darauf achten, dass Sie die Spaltenüberschriften den tatsächlich vorhandenen Daten anpassen. Wenn Sie also Daten von 1/2012 bis 12/2012 anzeigen und Spaltenüberschriften für die Monate 7/2012 bis 7/2013 angeben, dann werden zwar die Spalten für 7/2012 bis 6/2013 angezeigt, aber es sind nur die Spalten 1/2012 bis 6/2012 mit Daten gefüllt. Die übrigen Spalten bleiben leer, weil ja aufgrund des Filterkriteriums keine Daten vorliegen. Dies macht auch deutlich, dass die Spaltenüberschriften ungeachtet der enthaltenen Daten angelegt werden.
Wie aber erhalten wir nun sortierte Spaltenüberschriften für beliebig gefilterte Daten
Die erste Möglichkeit ist es, den Ausdruck für die Spaltenüberschriften so anzupassen, dass er trotz alphabetischer Sortierung die richtige Reihenfolge liefert. Dazu muss das Jahr nach vorn gestellt werden und der Monat immer mit zwei Stellen angezeigt werden. Der Ausdruck für die Spaltenüberschriften sieht dann so aus:
Bestellung: Jahr([Bestelldatum]) & "/" & Format(Monat([Bestelldatum]);"00")
Das Ergebnis finden Sie in Bild 4. Die Abfrage zeigt die Spaltenüberschriften nun in der richtigen Reihenfolge an. Allerdings könnte ein Kunde damit unzufrieden sein, weil er sich höchste Perfektion wünscht. Und wenn Sie, wie ich, dem Kunden immer mitteilen, dass man mit Access praktisch jede Anforderung erfüllen kann, möchten Sie vielleicht wissen, wie dieses Problem gelöst wird.
Bild 4: Richtig sortierte Spaltenüberschriften, allerdings im Format yyyy/mm
Basis-Abfrage
Um gleich einen überschaubareren SQL-Ausdruck zu erhalten, erstellen Sie zunächst eine Basis-Abfrage, die alle benötigten Daten für die Kreuztabellen in einer Abfrage zusammenfasst.
Diese sieht wie in Bild 5 aus und enthält die Felder KundeID, Firma der Tabelle tblKunden, das Feld Bestelldatum der Tabelle tblBestellungen sowie einen Ausdruck zur Berechnung des Umsatzes auf Basis der Felder Einzelpreis, Anzahl und Rabatt der Tabelle tblBestelldetails:
Bild 5: Basisabfrage für die Kreuztabelle
Umsatz: [Einzelpreis]*[Anzahl] *(1-ZCurrency([Rabatt]))
Auf Basis dieser Abfrage erstellen wir die Kreuztabellen-Abfrage. Legen Sie eine neue Abfrage an und fügen Sie die Abfrage qryUmsaetzeNachKundeUndMonat_Base als Datenherkunft hinzu.
Wandeln Sie die Abfrage in der Entwurfsansicht mit dem Kontextmenüeintrag Abfragetyp|Kreuztabellen-Abfrage in eine Kreuztabelle um. Fügen Sie alle Felder der Datenherkunft zum Entwurfsraster hinzu. Das Feld Bestelldatum ersetzen Sie durch den folgenden Ausdruck:
Bestellung: Monat([Bestelldatum]) & "/" & Jahr([Bestelldatum])
Danach stellen Sie den Wert in der Zeile Kreuztabelle für die einzelnen Felder wie folgt ein:
- KundeID und Firma: Zeilenüberschrift
- Bestellung: Spaltenüberschrift
- Umsatz: Wert (hier stellen Sie außerdem den Wert in der Zeile Funktion auf Summe ein)
Die Abfrage sieht nun wie in Bild 6 aus.
Bild 6: Aufbau der Kreuztabelle
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo