{"id":55000041,"date":"2002-02-01T00:00:00","date_gmt":"2021-02-10T19:03:04","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=41"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"Kreuztabellenabfragen_in_der_Praxis","status":"publish","type":"post","link":"https:\/\/access-im-unternehmen.de\/Kreuztabellenabfragen_in_der_Praxis\/","title":{"rendered":"Kreuztabellenabfragen in der Praxis"},"content":{"rendered":"

\"\"<\/p>\n

Autor: André Minhorst, Duisburg<\/b><\/p>\n

Kreuztabellenabfragen nehmen in der Reihe der Abfragen eine ganz besondere Position ein. Herkömmliche Abfragen sehen in der Datenblattansicht wie ganz normale Tabellen aus – sie besitzen Feldnamen als Spaltenüberschriften und jeder Datensatz nimmt eine Zeile ein. Kreuztabellenabfragen hingegen dienen zur Gruppierung und Sortierung von Daten sowie zur Anwendung von Funktionen wie z. B. der Berechnung von Summen, Durchschnittswerten oder Anzahlen. Nicht, dass normale Abfragen das nicht auch könnten, aber sie schaffen das lediglich in einer Dimension. Kreuztabellenabfragen gruppieren Daten nach zwei unterschiedlichen Kriterien und verwenden diese als Spalten- und Zeilenköpfe für die gewünschten, in einem Raster angeordneten Daten.<\/b><\/p>\n

Das Ganze hört sich ziemlich kompliziert an und erfordert auch ein gewisses Hineindenken in die Materie. Nach der Lektüre des vorliegenden Beitrags werden Sie vermutlich darüber ganz anders denken und Kreuztabellenabfragen je nach Anforderung aus dem ärmel schütteln. Das ist deshalb besondersgünstig, da Kreuztabellenabfragen als Grundlage für ein weiteres Thema der vorliegenden Ausgabe von Access im Unternehmen dienen: „Diagramme mit Microsoft Graph“.<\/p>\n

Hinweis<\/h3>\n

Die folgenden Beispiele verwenden die Tabellen der Nordwind-Datenbank, die mit Access mitgeliefert wird. Sie können die Tabellen entweder in eine neue Datenbank importieren oder die Beispiele anhand einer Sicherheitskopie der Nordwind-Datenbank durchführen. Selbstverständlich finden Sie alle Beispiele auf der beiliegenden Heft-CD. <\/p>\n

Daten nach bestimmten Kriterien zu gruppieren und anschließend eine beliebige Aggregatfunktion wie z. B. eine Summierung durchzuführen ist kein großes Problem. So können Sie beispielsweise in der Nordwind-Datenbank Umsätze nach Rechnungen, Kategorien oder Artikelarten sortieren und gruppieren und die Summen für unterschiedliche Gruppierungen ermitteln.<\/p>\n

Schwieriger wird es, wenn die Gruppierung zweidimensional erfolgen soll – wenn Sie etwa Umsätze nach bestimmten Artikeln oder Kategorien einerseits und nach Zeiträumen wie Monaten oder Quartalen andererseits ermittelt möchten.<\/p>\n

Hier kommen die so genannten Kreuztabellenabfragen ins Spiel: Sie bieten die Möglichkeit, bestimmte Felder als Spalten- und als Zeilenköpfe festzulegen und die gewünschten Informationen in aggregierter Form – also z. B. als Summe oder Mittelwert – im Kreuzungspunkt der Spalte und der Zeile anzuzeigen.<\/p>\n

Eine Kreuztabellenabfrage dient in der Regel zur Darstellung der Summe eines oder mehrerer Werte in unterschiedlichen Detaillierungsstufen. Daher stellen Sie nun zunächst eine Abfrage her, die als Grundlage der Summenbildung dient.<\/p>\n

<\/p>\n

<\/b><\/p>\n

Bild 1: Entwurfsansicht der Abfrage, die als Grundlage für die Kreuztabelle dient<\/span><\/b><\/p>\n

<\/p>\n

<\/b><\/p>\n

Bild 2: Auswahl des Kreuztabellenabfrage-Assistenten<\/span><\/b><\/p>\n

<\/p>\n

<\/b><\/p>\n

Bild 3: Auswahl der Datenherkunft<\/span><\/b><\/p>\n

Dazu ziehen Sie zunächst die Tabellen Artikel, Bestellungen und Bestelldetails in die Entwurfsansicht der Tabelle. Anschließend ziehen Sie einige Felder gemäß Bild 1 in das Entwurfsraster. Dabei werden Sie das Feld ganz rechts vergebens in einer der drei Tabellen suchen. Es handelt sich dabei um ein Berechnungsfeld mit dem folgenden Inhalt:<\/p>\n

Gesamtpreis: Zcurrency([Bestelldetails]![Einzelpreis]*[Anzahl]*(1-[Rabatt]))<\/pre>\n
Es berechnet den Gesamtpreis für die angegebene Menge des jeweiligen Artikels und reduziert um einen eventuell vorhandenen Rabatt. Tragen Sie einfach den oben angegebenen Ausdruck in die Zeile Feld einer leeren Spalte ein. <\/p>\n
Am einfachsten können Sie eine Kreuztabelle mit dem entsprechenden Assistenten von Access erstellen. Dazu wechseln Sie im Datenbankfenster zunächst in das Register Abfragen.<\/p>\n
Klicken Sie anschließend auf die Schaltfläche Neu. Daraufhin erscheint der Dialog Neue Abfrage, in dem Sie den Eintrag Kreuztabellenabfrage-Assistent auswählen (siehe Bild 2).<\/p>\n
Daraufhin öffnet sich das erste Fenster des Assistenten. Hier wechseln Sie unter Anzeigen auf die Option Abfragen und wählen als Datenherkunft die soeben erstellte Abfrage aus (siehe Bild 3).<\/p>\n
Im unteren Bereich des Fensters können Sie bereits die Struktur der Kreuztabelle erkennen. Sie müssen sie jetzt nur noch mit Leben füllen.<\/p>\n
<\/p>\n
<\/b><\/p>\n
Bild 4:  Auswahl der gewünschten Felder<\/span><\/b><\/p>\n
<\/p>\n
<\/b><\/p>\n
Bild 5:  Auswahl eines Zeitintervalls<\/span><\/b><\/p>\n
Und damit beginnen Sie direkt im nächsten Schritt (siehe Bild 4): Hier wählen Sie bis zu drei überschriften für die im linken Bereich der Kreuztabelle befindlichen Felder aus.<\/p>\n
Dazu bewegen Sie die Einträge Artikel-Nr und Artikelname in die rechte Liste, indem Sie entweder doppelt auf die gewünschten Einträge klicken oder die Einträge nacheinander markieren und die Schaltfläche mit dem Größer-Zeichen (>) anklicken.<\/p>\n
Nun geht es mit den Spaltenköpfen der übrigen Spalten weiter. Den zur Eingabe verwendeten Schritt aktivieren Sie durch einen Klick auf die Schaltfläche Weiter.<\/p>\n
Da Kreuztabellen eigentlich immer einen Wert für bestimmte Zeiträume oder Daten anzeigen oder aufsummieren, fällt die Auswahl des Feldes für die Spaltenköpfe nicht schwer: Es heißt  Bestelldaten.<\/p>\n
Nach der Auswahl dieses Feldes haben Sie die Möglichkeit, die Größe der gewünschten Intervalle festzulegen, für die Sie die Daten berechnen möchten. Der Assistent stellt fünf unterschiedliche Möglichkeiten zur Verfügung (siehe Bild 5).<\/p>\n
Und nun folgt die Auswahl des in der Kreuztabelle darzustellenden Feldes sowie der gewünschten Berechnungsart (siehe Bild 6).<\/p>\n
Im vorliegenden Beispiel wählen Sie den Eintrag Gesamtpreis und als Funktion Summe. Wenn Sie außer der Berechnung der Summe aller Einnahmen durch die jeweiligen Artikel innerhalb eines Monats auch die Einnahmen über den gesamten Zeitraum ausgeben möchten, aktivieren Sie das Kontrollkästchen neben Gesamtsumme jeder Zeile berechnen.<\/p>\n
Fertig! Nun müssen Sie nur noch einen Namen für die Abfrage festlegen und die Schaltfläche Fertigstellen betätigen.<\/p>\n
Die Datenblattansicht der Kreuztabelle sieht wie in Bild 7 aus. Neben der Gesamtsumme der Erträge eines jeden Artikels finden Sie in den weiteren Spalten die Summen für die einzelnen Monate.<\/p>\n
<\/p>\n
<\/p>\n
<\/b><\/p>\n
Bild 6:  Auswahl des zu berechnenden Feldes<\/span><\/b><\/p>\n
<\/p>\n
<\/b><\/p>\n
Bild 7:  Das Ergebnis einer Kreuztabellenabfrage<\/span><\/b><\/p>\n
<\/p>\n
<\/b><\/p>\n
Bild 8:  Die durch den Assistenten erstellte Abfrage<\/span><\/b><\/p>\n
Leider ist die mit dem Assistenten erstellte Kreuztabellenabfrage nicht ganz perfekt: Die in der Nordwind-Datenbank enthaltenen Bestelldaten erstrecken sich über drei aufeinander folgende Jahre, in der Datenblattansicht der Kreuztabellenabfrage finden sich aber nur zwölf Monate. Da die Abfrage nicht nach Jahren differenziert, summiert sie beispielsweise im Feld Januar alle Erträge jeweils für Januar der Jahre 1994, 1995 und 1996 auf.<\/p>\n
Daher ist eine kleine Nachbearbeitung der Kreuztabellenabfrage dringend erforderlich – außerdem sollten Sie ohnehin einen Blick auf den Entwurf werfen, um ihn zu verstehen und die nächste Kreuztabellenabfrage ohne den Assistenten herzustellen.<\/p>\n
Unterschiede zwischen Kreuztabellen und herkömmlichen Abfragen<\/b><\/p>\n
Im Vergleich zu dem Entwurf einer herkömmlichen Abfrage findet sich auf den ersten Blick zunächst einmal kein großer Unterschied. Erst auf den zweiten Blick offenbart sich die Zeile mit der Bezeichnung Kreuztabelle, in der die Funktionen der einzelnen Felder der Abfrage festgelegt werden können (siehe Bild 8).<\/p>\n
Damit die Kreuztabelle nur die Daten für ein bestimmtes Jahr auflistet, müssen Sie lediglich ein Bedingungsfeld hinzufügen, welches die Datensätze nach dem Jahr filtert. Dazu legen Sie in der Abfrage ein neues Feld an. Tragen Sie in die Zeile Feld den folgenden Ausdruck ein:<\/p>\n
Bestelljahr: Jahr([Bestelldatum])<\/pre>\n
ändern Sie den Eintrag in der Zeile Kreuztabelle auf (nicht angezeigt) ab. Der Eintrag selbst wird anschließend nicht angezeigt.  Schließlich geben Sie in der Zeile Kriterien den folgenden Ausdruck an:<\/p>\n
=1996<\/pre>\n
Wenn Sie nun in die Datenblattansicht wechseln, stellen Sie schnell fest, dass nur noch die Bestellungen des gewünschten Jahres in die Summen einbezogen werden.<\/p>\n
Feldarten einerKreuztabellenabfrage<\/b><\/p>\n
Kreuztabellenabfragen können drei unterschiedliche Feldarten haben: Zeilenüberschrift, Spaltenüberschrift und Wert. <\/p>\n
Zeilenüberschriften sind die Werte, die sich im linken Teil der Tabelle befinden – z. B. Artikel-Nr und Artikelname. <\/p>\n
Dabei bilden nicht die Felder selbst, sondern deren Inhalte wie z. B. bestimmte Artikel die Zeilenköpfe.<\/p>\n
Als Spaltenüberschriften dienen ja – wie bereits erwähnt – zumeist bestimmte Zeiträume oder Daten. Das gewünschte Format müssen Sie selbst festlegen, z. B. mit folgendem Ausdruck:<\/p>\n
Ausdr1: Format([Bestelldatum];\"mmm\")<\/pre>\n
Natürlich können Sie statt Ausdr1 auch einen aussagekräftigeren Namen für das Feld wählen – in der Datenblattansicht erscheint er allerdings nicht.<\/p>\n
Das entscheidende Feld hat in der Zeile Kreuztabelle den Eintrag Wert: Es handelt sich dabei um das Feld, das die Daten der Kreuzungspunkte der Kreuztabelle enthält.<\/p>\n
Berechnungsfunktionen inKreuztabellen<\/b><\/p>\n
Kreuztabellen bieten die gleichen Berechnungsfunktionen wie übliche Abfragen. Eine Auflistung der Funktionen finden Sie in Tab. 1.<\/p>\n
Sortieren von Zeilen- und Spaltenüberschriften<\/b><\/p>\n
Das Sortieren der Zeilen- und Spaltenüberschriften erfolgt zunächst einmal mit den herkömmlichen Mitteln – über den Eintrag, den Sie in der Zeile Sortierung der Abfrage auswählen können. Hier stehen die beiden Möglichkeiten Absteigend und Aufsteigend zur Verfügung.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
Funktion<\/b><\/p>\n<\/td>\n
\n
Wirkung<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Gruppierung<\/p>\n<\/td>\n
\n
Legt fest, nach welchem Feld die Datensätze gruppiert werden<\/p>\n<\/td>\n<\/tr>\n
\n
Summe<\/p>\n<\/td>\n
\n
Bildet die Summe eines Feldes <\/p>\n<\/td>\n<\/tr>\n
\n
Mittelwert<\/p>\n<\/td>\n
\n
Ermittelt den Mittelwert der Werte eines Feldes<\/p>\n<\/td>\n<\/tr>\n
\n
Min<\/p>\n<\/td>\n
\n
Ermittelt den kleinsten Wert eines Feldes<\/p>\n<\/td>\n<\/tr>\n
\n
Max<\/p>\n<\/td>\n
\n
Ermittelt den größten Wert eines Feldes<\/p>\n<\/td>\n<\/tr>\n
\n
Anzahl<\/p>\n<\/td>\n
\n
Ermittelt die Anzahl der Datensätze eines Feldes (mit Ausnahme von Feldern mit Null-Werten)<\/p>\n<\/td>\n<\/tr>\n
\n
StdAbw<\/p>\n<\/td>\n
\n
Ermittelt die Standardabweichung eines Feldes<\/p>\n<\/td>\n<\/tr>\n
\n
Varianz<\/p>\n<\/td>\n
\n
Ermittelt die Varianz eines Feldes<\/p>\n<\/td>\n<\/tr>\n
\n
ErsterWert<\/p>\n<\/td>\n
\n
Gibt den Wert des Feldes für den ersten Datensatzes<\/p>\n<\/td>\n<\/tr>\n
\n
LetzterWert<\/p>\n<\/td>\n
\n
Gibt den Wert des Feldes für den letzten Datensatz<\/p>\n<\/td>\n<\/tr>\n
\n
Ausdruck<\/p>\n<\/td>\n
\n
Gibt an, dass es sich um einen benutzerdefinierten Ausdruck handelt – z. B. zur Summierung zweier Felder<\/p>\n<\/td>\n<\/tr>\n
\n
Bedingung<\/p>\n<\/td>\n
\n
Legt eine Bedingung für die auszuwählenden Datensätze fest<\/p>\n<\/td>\n<\/tr>\n<\/table>\n
Tab. 1: Berechnungsfunktionen in Kreuztabellen <\/p>\n
Bei genauerer Betrachtung fällt aber auf, dass keine dieser Sortierungen auf die Sortierung der Spaltenköpfe in der Beispielkreuztabelle passt: Die Monatskürzel sind nämlich keinesfalls nach dem Alphabet sortiert – weder aufsteigend noch absteigend. Und es soll doch wohl kein Zufall sein, dass die Monate dennoch in der richtigen Reihenfolge angezeigt werden<\/p>\n
<\/p>\n
<\/b><\/p>\n
Bild 9:  Eigenschaften einer Kreuztabellenabfrage<\/span><\/b><\/p>\n
Natürlich ist das nicht dem Zufall, sondern vielmehr der Eigenschaft Fixierte Spaltenüberschriften der Abfrage zu verdanken.<\/p>\n
Mit Hilfe dieser Eigenschaft, die Sie durch einen Doppelklick auf den leeren Bereich im oberen Teil des Abfrageentwurfs anzeigen (siehe Bild 9), können Sie die Reihenfolge feststehender überschriften einstellen.<\/p>\n
Dabei müssen Sie allerdings exakt darauf achten, dass Sie genau die von Access verwendeten Kürzel benutzen. Im Fall der mit dem Assistenten erstellten Abfrage hat der Assistent Ihnen diese Aufgabe bereits abgenommen, normalerweise müssen Sie hier aber selbst Hand anlegen.<\/p>\n
Dazu lassen Sie sich am besten zunächst einmal die unsortierte Tabelle ausgeben, um die genaue Schreibweise der Kürzel – oder auch der ausgeschriebenen Bezeichnungen – der jeweiligen Einheit zu entnehmen. Das ganze funktioniert allerdings nur mit den Spaltenköpfen und nicht mit den Zeilenköpfen.<\/p>\n
Mit Hilfe der folgenden Arbeitsanweisungen können Sie schnell Ihre erste eigene Kreuztabellenabfrage erstellen.<\/p>\n
Diesmal soll die Kreuztabelle allerdings nicht  die Umsätze je Artikel und Monat, sondern die Umsätze je Kategorie und Quartal enthalten.<\/p>\n