Kreuztabellen in Berichten

Martin Hoffmann, Düsseldorf

Access bietet sowohl dem Einsteiger als auch dem Profi zahlreiche Hilfsmittel und Assistenten, um komplexe Berichte sehr leicht gestalten zu können. Es gibt Assistenten für fast jeden Zweck. Aber in Access gibt es keine Berichte, bei denen die Spaltenanzahl von den Daten abhängt, wie es bei Kreuztabellen vorkommt. Sie können zwar einen Bericht auf Basis einer Kreuztabelle in Access anlegen, wenn aber in der Kreuztabelle neue Spalten hinzukommen, fehlen diese anschließend im Bericht. In diesem Beitrag lernen Sie, wie Sie solche Berichte entwerfen – ohne Datenverlust.

Damit wir uns ganz auf die Programmierung des Berichts konzentrieren können, ist das Datenbankmodell für die Beispieldatenbank sehr einfach gehalten. Das Modell besteht lediglich aus der Tabelle tblUmsätze (siehe Bild 1), die als Basis für die Kreuztabelle dient.

Bild 1: Aufbau der Tabelle tblUmsätze

In der Tabelle werden die Umsätze mit Eingangsdatum gespeichert. Jeder Umsatz kann dabei einer Kategorie zugeordnet werden. Der Einfachheit halber wurde das Feld Kategorie nicht als Nachschlagefeld mit einer gesonderten Tabelle tblKategorien angelegt, sondern lediglich als Textfeld.

Ebenso einfach wie die Datenbasis ist auch die Eingabe der Umsätze gehalten.

Die Eingabe erfolgt über das Formular frmUmsätzeErfassen. Die Umsätze werden direkt in der Datenblattansicht eingegeben.

Bild 2: Eingabe der Umsätze

Dabei wird ein Kombinationsfeld verwendet, durch das sich die doppelte Eingabe der Kategorien verhindern lässt.

Das Kombinationsfeld zeigt alle bereits in der Tabelle tblUmsätze vorhandenen Kategorien über die folgende Abfrage an:

Bild 3: Die Auswahlabfrage für die Kreuztabelle

Bild 4: Gruppierungsabfrage für die Kreuztabelle

Bild 5: Die Kreuztabelle in der Entwurfsansicht …

SELECT DISTINCT [tblUmsätze].[Kategorie] FROM tblUmsätze ORDER BY [tblUmsätze].[Kategorie]; 

Damit eine neu eingegebene Kategorie direkt im Kombinationsfeld zur Auswahl steht, wird die Datensatzherkunft des Kombinationsfeldes nach Speicherung des Datensatzes aktualisiert. Dazu wird im Formularereignis Nach Aktualisierung der folgende Programmcode eingetragen:

Private Sub Form_AfterUpdate()
    Me!Kategorie.Requery
End Sub

Bild 2 zeigt das Formular mit einigen Beispieldatensätzen.

Die Daten für den Bericht werden in einer Kreuztabelle aufbereitet. Dabei sollen die Umsätze pro Kategorie monatsweise und in Summe aufgeführt werden. Gehen Sie dabei in den folgenden Schritten vor:

Legen Sie zunächst eine neue Auswahlabfrage auf Basis der Tabelle tblUmsätze an.

Definieren Sie die Spalten der Abfrage wie in Bild 3 zu sehen. Das Feld Umsatz ist zweimal als Ausgabefeld vorhanden, da es in der Kreuztabelle sowohl für die Monats- als auch für die Kategoriesummen benötigt wird. Zur Bestimmung des Monats benutzen Sie den Ausdruck

Monat: Format([Eingangsdatum];"jjjj/mm")

Sortieren Sie die Abfrage nach den Feldern Kategorie und Monat.

Aktivieren Sie über den Menübefehl Ansicht ( Funktionen die Gruppierungsfunktionen.

Wählen Sie für die Felder Kategorie und Monat die Funktion Gruppierung und für die Umsatzfelder die Funktion Summe aus (siehe Bild 4). (

Die Daten sind nun bereits richtig in der Abfrage zusammengestellt. Jetzt fehlt nur noch die Darstellung der Daten in Form einer Kreuztabelle, bei der die Monate als Spalten- und die Kategorien als Zeilenüberschriften angezeigt werden.

ändern Sie dazu den Abfragetyp über den Menüpunkt Abfrage ( Kreuztabellenabfrage von Auswahlabfrage in Kreuztabellenabfrage. In der Zeile Kreuztabelle wählen Sie jetzt die Funktion der einzelnen Felder aus.

Setzen Sie bei den Spalten Kategorie und UmsatzProKategorie den Eintrag Zeilenüberschrift. Für das Feld Monat verwenden Sie die Funktion Spaltenüberschrift und beim Feld SummeVonUmsatz die Funktion Wert (siehe Bild 5).

Bild 6 zeigt das Ergebnis der Abfrage in der Datenblattansicht.

Bild 6: … und in der Datenblattansicht

Im Beispiel sind die Monate Januar, Februar und März mit Umsätzen belegt. Hier wird noch einmal deutlich, dass beim Hinzufügen von neuen Datensätzen in der Tabelle tblUmsätze zum Beispiel für die Monate April und Mai im Ergebnis der Kreuztabellenabfrage automatisch neue Spalten hinzukommen.

Bild 7: Starrer Auto-Bericht mit fehlenden Daten

Wenn Sie jetzt einen Bericht auf Basis der vorhandenen Daten erstellen, würde Access die Spalten fest anlegen. Bild 7 zeigt als Beispiel einen Bericht, der mit dem Datenbestand aus Bild 2 entworfen wurde.

Allerdings wurden seitdem neue Umsätze im September in einer neuen Kategorie eingegeben.

Dabei fällt auf, dass die Kategorie Administration zwar mit der Kategoriesumme aufgeführt ist, aber der Monat mit dem Umsatz fehlt.

Auch die Summe bei der Kategorie fzg-boerse stimmt nicht, weil die Umsätze aus September zwar zur Gesamtsumme addiert wurden, die Spalte für den Monat September aber auch hier nicht angezeigt wird.

Vorgehensweise

Um den Bericht so flexibel zu programmieren, dass immer alle Daten der Tabelle tblUmsätze berücksichtigt werden und der Bericht zur Laufzeit dynamisch angepasst wird, sind ein paar Vorüberlegungen nötig:

Bei jedem öffnen des Berichts müssen Sie im Detailbereich so viele Steuerelemente anlegen, wie die Kreuztabellenabfrage Spalten enthält.

Gleiches gilt für die Spaltenüberschriften und -summen.

Damit Sie nicht bei jedem öffnen des Berichts die dynamisch hinzugefügten Felder wieder löschen müssen, verwenden Sie eine Berichtsvorlage, in der alle fixen Steuerelemente untergebracht sind. Darunter fallen zum Beispiel die Textfelder für die Kategorie und den UmsatzProKategorie. Aber auch die Berichtsüberschrift, Linien, der Seitenfuß mit Seitenanzahl etc. sind darin enthalten. Als Basis für den eigentlichen Bericht verwenden Sie dann eine Kopie dieser Vorlage.

Der Bericht kann nicht mehr direkt aus dem Datenbankfenster oder über einen Standardbefehl (zum Beispiel DoCmd.OpenReport) aufgerufen werden, da je nach vorhandenen Daten gegebenenfalls Spalten in der Datenherkunft fehlen, was zu einem Fehler führen würde. Der Bericht muss über eine separate Funktion aufgerufen werden, die den Bericht jedes mal wieder aus der Vorlage passend zum aktuellen Datenbestand erzeugt.

''Alten Bericht löschen
DoCmd.DeleteObject acReport, "repUmsätze"
''Bericht von der Vorlage kopieren
DoCmd.CopyObject CurrentDb().Name, "repUmsätze", _    acReport, "repUmsätze_Vorlage"
''Bericht versteckt in der Entwurfsansicht öffnen
DoCmd.OpenReport "repUmsätze", acViewDesign

Quellcode 1

Bild 8: Die Berichtsvorlage in der Entwurfsansicht

Neue Steuerelemente können nur in der Entwurfsansicht eines Berichts mittels VBA hinzugefügt werden. Folglich muss der Bericht zunächst in der Entwurfsansicht geöffnet, anschließend um die fehlenden Felder ergänzt und dann in der Vorschau angezeigt werden.

Die Berichtsvorlage

Im ersten Schritt erstellen Sie die Berichtsvorlage. Am einfachsten legen Sie dazu einen neuen leeren Bericht auf Basis der Kreuztabellenabfrage qryUmsätze an. Platzieren Sie dann die fixen Steuerelemente, die unabhängig von der Spaltenanzahl immer gleich bleiben. In unserem Beispiel sind das die Textfelder Kategorie und UmsatzProKategorie im Detailbereich, die mit den gleichnamigen Datenfeldern der Abfrage verknüpft sind. Zusätzlich wurden zwei Bezeichnungsfelder als überschriften im Seitenkopf eingefügt (siehe Bild 8).

Die Felder können Sie noch nach Ihren Vorstellungen formatieren.

Wahlweise ergänzen Sie den Bericht wie zuvor beschrieben noch um weitere Elemente wie einen Titel im Berichtskopf. Speichern Sie den fertigen Bericht unter dem Namen repUmsätze_Vorlage ab.

Die Funktion GenerateReport

Nachdem Sie die Vorlage gespeichert haben, benötigen Sie eine Funktion, mit der Sie den neuen Bericht erstellen. Hierzu dient die öffentliche Funktion GenerateReport:

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