Auswertungen mit Kreuztabellen – Karsten´s Homepage

Was wäre eine Datenbank ohne Abfragen?

Dabei spielen Auswahlabfragen wohl die Hauptrolle. Wenn es um die Analyse Ihrer Access-Daten geht, stoßen Auswahl ab – fragen aber schnell an ihre Grenzen. Da haben Kreuztabellenabfragen wesentlich mehr zu bieten. Ob Sie Umsatzstatistiken über mehrere Quartale oder Jahre benötigen oder wissen möchten, welche Umsätze die Vertriebsmitarbeiter bei welchen Kunden gemacht haben: alles kein Problem mit Kreuztabellenabfragen. Wir zeigen, wie Ihnen Kreuztabellen bei der Auswertung und Analyse Ihrer Datenbestände helfen.

Was Kreuztabellenabfragen gegenüber Auswahlabfragen auszeichnet, veranschaulicht am besten ein Beispiel.

Inhaltlich geht es in der Beispieldatenbank um Bestellinformationen, die sich aus Bestelldetails wie Bestelldatum und -menge sowie aus Personal-, Kunden- und Artikelinformationen zusammensetzen.

Nehmen Sie für das Beispiel an, dass Sie herausfinden wollen, wie viel Umsatz die Vertreter bei den einzelnen Kunden gemacht haben.

In einer Auswahlabfrage könnten Sie zu diesem Zweck die Mitarbeiter, die Kunden und die Bestellsummen ausgeben lassen. Wenn Sie dabei die Bestellsummen pro Kunde zusammenfassen, erhalten Sie eine Abfrage wie „0_qryBestellsummen“ in der Beispieldatenbank.

So haben Sie zwar die gewünschten Zahlen, besonders übersichtlich ist die Liste aber nicht, da Sie die Umsätze der einzelnen Mitarbeiter kaum miteinander vergleichen können. Ein wesentlich aussagekräftigeres Ergebnis erzielen Sie mit einer Kreuztabellenabfrage, wie die Abfrage „1_qryVertreterumsatz“ beweist.

Die Umsätze der Mitarbeiter lassen sich in diesem Fall auf einen Blick erfassen. Das liegt daran, dass Kreuztabellenabfragen die Spaltenüberschriften dynamisch aus Feldinhalten erzeugen und dann die passenden Werte in das Raster aus Zeilen- und Spaltenüberschriften eintragen. So lassen sich hier die Mitarbeiterumsätze schnell den einzelnen Firmen zuordnen.

Der Aufwand zum Anlegen einer Kreuztabelle ist nicht größer als der für eine Auswahlabfrage. Besonders komfortabel geht es mit dem Kreuztabellenabfrage-Assistenten.

Beachten Sie aber, dass der Assistent eine Datenbasis voraussetzt, die alle Informationen für die Kreuztabelle enthält. Nur selten werden Sie mit einer Tabelle auskommen. Häufiger werden Sie die Datenbasis erst mithilfe einer Auswahlabfrage aus mehreren Tabellen zusammenstellen müssen. Das trifft auch auf die Beispieldatenbank zu. Hier liefert die Abfrage „_qryBestellungen“ die benötigten Werte.

Die folgende Anleitung zeigt, wie Sie den Kreuztabellenabfrage-Assistenten einsetzen, um eine Auswertung wie in der Abfrage „1_qryVertreterumsatz“ anzulegen:

In Access 2003, 2002/XP, 2000 und 97 wechseln Sie auf das Datenbankregister „Abfragen“ und klicken auf die Schaltfläche NEU.
Beim Einsatz von Access 2007 wechseln Sie in der Multifunktionsleiste auf „Erstellen“ und klicken auf ABFRAGE-ASSISTENT.
Im daraufhin angezeigten Dialogfenster markieren Sie den Eintrag „Kreuztabellenabfrage-Assistent“, was Sie mit OK bestätigen.
Nun bestimmen Sie die Datenbasis für die Kreuztabelle. In diesem Fall aktivieren Sie im Bereich ANZEIGEN zu – nächst die Option ABFRAGEN und dann markieren Sie die Abfrage „_qry Bestellungen“. Anschließend fahren Sie mit einem Klick auf WEI TER fort.
Als nächstes legen Sie die Zeilenüberschriften der Kreuztabelle fest.
Hier genügen die Inhalte des Feldes „Vertreter“. Doppelklicken Sie auf den Feldnamen, um ihn in die Liste AUSGEWÄHLTE FELDER zu übernehmen. Anschließend klicken Sie auf WEITER.
Markieren Sie das Feld, aus dem die Spaltenüberschriften gebildet werden sollen. Im Beispiel ist es das Feld „Firma“. Danach klicken Sie wieder auf WEITER.
Wählen Sie das Feld aus, das die auszugebenden Werte enthält. Hier sind Sie an der „Bestellsumme“ interessiert. Je nach Feldtyp erscheint rechts neben der Feldliste eine Reihe von FUNKTIONEN, mit denen Sie Berechnungen mit dem Wertefeld anstellen können.
Im Beispielfall soll die Summe aller Bestellungen pro Vertreter und Firma gebildet werden. Je nach Bedarf aktivieren oder deaktivieren Sie das Kontrollkästchen GESAMTSUMME JEDER ZEILE BERECHNEN. In der Beispielabfrage erhalten Sie so die Gesamtbestellsumme jedes Vertreters.
Nachdem Sie mit WEITER zur letzten Seite des Assistenten gegangen sind, weisen Sie der Abfrage einen Namen zu und klicken schließlich auf FERTIG STELLEN, um sich das Ergebnis anzuschauen.

Natürlich können Sie eine Kreuztabelle auch nachträglich bearbeiten oder ohne den Assistenten erstellen.

Dazu öffnen Sie eine bestehende oder eine neue Ab frage in der Entwurfs an sicht. Bei einer neuen Abfrage müssen Sie zuerst die Datenbasis auswählen und anschließend ABFRAGE-KREUZTABELLENABFRAGE aufrufen.

Zur Veranschaulichung erweitern Sie die Abfrage „1_qryVertreterumsatz“ um eine Gliederung des Umsatzes nach Jahren:

Öffnen Sie „1_qryVertreterumsatz“ in der Entwurfsansicht. Beachten Sie, dass im unteren Bereich die zwei Zeilen „Funktion“ und „Kreuztabelle“ angezeigt werden.
Lassen Sie die Jahresangabe der Bestelldaten in einem neuen Abfragefeld ausgeben, indem Sie in einer neuen Spalte der Zeile „Feld“ Folgendes eingeben:

Jahr: Jahr([Bestelldatum])
Access setzt die Zeile „Funktion“ automatisch auf den Wert „Gruppierung“. Das ist in Ordnung, da Sie alle Bestellungen eines Jahres zusammenfassen wollen und nicht an den Jahreszahlen jeder einzelnen Bestellung interessiert sind.

Sie müssen nur noch die Verwendung in der „Kreuztabelle“ einstellen. Da zu öffnen Sie die Dropdown-Liste in der entsprechenden Zeile und wählen in diesem Fall „Zeilenüberschrift“ an.

Das Resultat dieser Erweiterung entspricht der Abfrage „2_qry Umsatz ProJahr“ aus der Beispieldatenbank.

Es bietet sich an, die Abfrage „2_qry – UmsatzProJahr“ mit einem Parameter zu versehen, um interaktiv die Umsätze eines bestimmten Jahres abzufragen.

Auch das ist bei Kreuztabellen kein Problem:

Öffnen Sie die gewünschte Abfrage in der Entwurfsansicht.
Erstellen Sie den Abfrageparameter, indem Sie in Access 2003, 2002/XP, 2000 oder 97 das Menü ABFRAGEPARAMETER aufrufen. In Access 2007 klicken Sie auf EINBLENDEN/AUSBLENDEN-PARAMETER.
Geben Sie in der Spalte „Parameter“ die Bezeichnung ein, die in der Eingabeaufforderung erscheinen soll – beispielsweise „Welches Jahr?“ – und wählen Sie rechts daneben den geeigneten „Felddatentyp“ aus – zum Beispiel „Integer“. Klicken Sie auf OK, um das Dialogfenster wieder zu schließen.
In einer neuen Feldspalte des Abfrageentwurfs definieren Sie nun das Feld oder den Ausdruck, auf das/den sich der Abfrageparameter beziehen soll. Um das Jahr der Bestellungen einzugrenzen, geben Sie folgenden Ausdruck ein:Jahr([Bestelldatum])
Da Sie den Ausdruck nicht zum Gruppieren verwenden wollen, ändern Sie die Auswahl aus der Zeile „Funktion“ in „Bedingung“.
Geben Sie die in Schritt 3 verwendete Parameterbezeichnung in die Zeile „Kriterien“ ein. Schließen Sie den Text in eckige Klammern ein, damit er als Parameter erkannt wird.

Wenn Sie die Abfrage jetzt in der Datenblattansicht öffnen, fragt Access zuerst den Parameter ab.

Geben Sie im Beispiel eine Jahreszahl zwischen „1999“ und „2002“ ein, um sich anschließend die entsprechenden Jahresumsätze ausgeben zu lassen.

Bislang haben Sie immer die Inhalte eines Feldes als Spaltenüberschriften der Kreuztabelle verwendet. Sie können aber auch Ausdrücke als Spaltenüberschriften einsetzen.

Schauen Sie sich dazu die folgende Abbildung an:

Die Spaltenüberschriften geben Intervalle über die Höhe des Bestellvolumens an. Die Werte in der Tabelle selbst sagen aus, wie viele Bestellungen ein Kunde im Bereich zwischen „0 – 100 €“, „100 – 500 €“ usw. getätigt hat.

Wie Sie eine solche Kreuztabellenauswertung erstellen, sehen Sie im Entwurf der Abfrage „4a_qryBestellvolumen“.

Die Felder „Firma“ und „Bestellsumme“ werden in gewohnter Weise als Zeilenüberschrift bzw. Wert eingesetzt. Neu ist hier nur, dass Sie nicht die „Summe“, sondern die „Anzahl“ der Bestellsummen ausgeben lassen.

Die Spaltenüberschriften legen Sie mit einem Ausdruck fest, in dem Sie die Funktion „Schalter“ einsetzen. Die Funktion wertet ihre Argumente paarweise aus:

Der erste Teil entspricht einer Bedingung, die überprüft wird, der zweite Teil ist der Rückgabewert, sofern die Bedingung zutrifft. Gibt es keine Übereinstimmung, wird das nächste Argumenten paar ausgewertet. Auf diese Weise ordnen Sie die Werte der Kreuztabelle genau definierten Intervallen zu.

Als Spaltenüberschriften werden dann die Textwerte „0 – 100 €“, „100 – 500 €“ usw. ausgegeben. Wenn Sie sich die Abfrage „4a_qry Bestellvolumen“ in der Datenblattansicht anschauen, werden Sie schnell ein Manko entdecken:

Die Sortierung der Spaltenüberschriften entspricht nicht der mathematischen Logik.

Das liegt daran, dass Access die Werte immer in Form von Text sortiert und somit die Spalte „1000 – 2000 €“ vor die Spalte „500 – 1000 €“ stellt.

Abhilfe schafft ein manuelles Bearbeiten der Spaltenüberschriften, das Sie wie folgt erreichen:

1. Öffnen Sie die Abfrage, die Sie ändern wollen, in der Entwurfsansicht.

2. Klicken Sie eine freie Fläche im oberen (grauen) Entwurfsbereich mit der rechten Maustaste an und wählen Sie im Kontextmenü den Befehl EIGENSCHAFTEN an.

3. Im Dialogfenster mit den Abfrageeigenschaften geben Sie in das Feld FIXIERTE SPALTENÜBERSCHRIFTEN die Überschriften in der passenden Reihenfolge ein. Sie müssen die Werte jeweils als Text in Anführungszeichen und durch Semikolons getrennt eintragen.

In der Beispieldatenbank können Sie sich die Korrekturen in der Abfrage „4b_qry – Bestellvolumen“ anschauen.

Mit fixierten Spaltenüberschriften können Sie außerdem dafür sorgen, dass eine Kreuztabelle immer die gleiche Anzahl an Spalten umfasst. Access unterschlägt ansonsten Spalten, denen keine Werte zuzuordnen sind.

Wenn es zum Beispiel gar keine Bestellungen gibt, deren Höhe zwischen 0 und 100 Euro liegt, zeigt Access die Spalte in der Abfrage „4a_qry Bestellvolumen“ trotz des Intervalls aus der „Schalter“- Funktion nicht an.

Wie Sie an den Beispielen gesehen haben, lohnt es sich, Kreuztabellen abfragen etwas näher unter die Lupe zu nehmen. Die Auswertungen Ihrer Datenbestände werden sicherlich davon profitieren.