HTML-Kreuztabelle 1: Basics

Kreuztabellenabfragen sind eine praktische Sache, wenn es darum geht, Kombinationen aus m:n-Beziehungen abzubilden – zum Beispiel die Preise für verschiedene Bauelemente oder Materialien in Abhängigkeit von der Höhe und der Breite des Materials. Dabei benötigen Sie noch nicht einmal eine m:n-Beziehung, die in einer Kreuztabelle darzustellenden Daten können auch aus einer einzigen Tabelle stammen. Der Haken an Kreuztabellen ist, dass diese in der Regel nicht bearbeitet werden können. Wenn Sie also etwa den Preis für ein Bauelement mit einer Höhe von einem Meter und einer Breite von 50 Zentimetern einstellen wollen, müssen Sie wieder die zugrunde liegende Tabelle oder das darauf aufbauende Formular bemühen. Im vorliegenden Beitrag wollen wir zunächst einmal die Grundlage für die Bearbeitung schaffen – indem wir die Daten der Tabelle per HTML in Kreuztabellenform darstellen.

Basistabelle

Als Basistabelle verwenden wir die Tabelle tblMaterialpreise. Diese enthält die Felder MaterialpreisID, Breite, Hoehe und Preis. Die Datentypen können Sie dem Tabellenentwurf aus Bild 1 entnehmen.

Bild 1: Entwurf der Tabelle tblMaterialpreise

Hier sehen Sie auch, dass wir für die beiden Felder Hoehe und Breite einen zusammengesetzten, eindeutigen Index definiert haben. Damit stellen wir sicher, dass für jede Kombination aus Höhe und Breite nur ein Preis festgelegt werden kann – mehr können wir in der Kreuztabelle nicht darstellen.

Daten in Kreuztabellenform ausgeben

Die Daten sollen nun so ausgegeben werden, dass die Spaltenüberschriften die Höhe, die Zeilenüberschriften die Breite und die Zellen selbst den jeweiligen Preis für die Kombination aus Höhe und Breite anzeigen. Dazu fügen wir der Tabelle erst einmal ein paar Datensätze zum Spielen hinzu. Die gefüllte Tabelle sieht dann wie in Bild 2 aus.

Bild 2: Beispieldaten in der Tabelle tblMaterialpreise

Danach wollen wir zuerst einmal die Daten in Kreuztabellenform im Direktbereich des VBA-Editors ausgeben. Erst nachdem wir den grundlegenden Ablauf programmiert haben, nehmen wir die Darstellung in einer HTML-Tabelle hinzu.

Die Ausgabe im Direktbereich soll durch die Prozedur Kreuztabelle erfolgen (siehe Listing 1). Diese definiert eine Variable für das aktuelle Datenbankobjekt (db) sowie drei Recordset-Variablen. Das Recordset rstSpaltenkoepfe nimmt alle Werte des Feldes Hoehe der Tabelle tblMaterialpreise auf. Damit jeder Wert nur einmal vorkommt, fügen wir neben SELECT als zweites Schlüsselwort noch DISTINCT hinzu. Auf diese Weise liefert das Recordset jeden Wert des Feldes Hoehe nur einmal, hier 800, 900 und 1.000. Außerdem sortieren wir die Werte nach der Größe. Damit können wir schon einmal die Spaltenüberschriften in den Direktbereich schreiben. Hier beginnen wir mit einer leeren Spalte, was wir durch die Ausgabe einer leeren Zeichenkette plus dem Komma-Zeichen (,) stellvertretend für einen Tabulator-Schritt erledigen. Dann durchlaufen wir in einer Do While-Schleife alle Datensätze des Recordsets rstSpaltenkoepfe und geben nacheinander alle Werte in einer Zeile aus. Damit Debug.Print nach der Ausgabe eines Wertes nicht in die nächste Zeile springt, hängen wir auch hier jeweils das Komma an. Dadurch wird auch hier ein Tabulator eingefügt statt des üblichen Zeilenumbruchs. Damit wäre die Zeile mit den Spaltenköpfen bereits geschafft. Das Ergebnis sieht bisher so aus:

Public Sub Kreuztabelle()
     Dim db As DAO.Database
     Dim rstSpaltenkoepfe As DAO.Recordset
     Dim rstZeilen As DAO.Recordset
     Dim rstWerte As DAO.Recordset
     Set db = CurrentDb
     Set rstSpaltenkoepfe = db.OpenRecordset("SELECT DISTINCT Hoehe FROM tblMaterialpreise ORDER BY Hoehe", dbOpenDynaset)
     Debug.Print ,
     Do While Not rstSpaltenkoepfe.EOF
         Debug.Print rstSpaltenkoepfe!Hoehe,
         rstSpaltenkoepfe.MoveNext
     Loop
     Debug.Print
     Set rstZeilen = db.OpenRecordset("SELECT DISTINCT Breite FROM tblMaterialpreise ORDER BY Breite", dbOpenDynaset)
     Do While Not rstZeilen.EOF
         Debug.Print rstZeilen!Breite,
         Set rstWerte = db.OpenRecordset("SELECT Preis FROM tblMaterialpreise WHERE Breite = " & rstZeilen!Breite _
             & " ORDER BY Hoehe", dbOpenDynaset)
         Do While Not rstWerte.EOF
             Debug.Print rstWerte!Preis,
             rstWerte.MoveNext
         Loop
         Debug.Print
         rstZeilen.MoveNext
     Loop
End Sub

Listing 1: Ausgabe der Spaltenköpfe, Zeilenköpfe und Preise im Direktbereich

               800           900           1000         

Damit wir danach mit der ersten Zeile mit Daten fortfahren können, folgt nun der Aufruf der Debug.Print-Anweisung ohne Parameter. Damit springen wir direkt in die nächste Zeile. Das zweite Recordset namens rstZeilen füllen wir mit allen Einträgen des Feldes Breite der Tabelle tblMaterialpreise, diesmal aufsteigend sortiert nach dem Feld Breite. Auch hier verwenden wir das DISTINCT-Schlüsselwort, damit jede Breite nur einmal aufgenommen wird. Dieses Recordset enthält nun also jeweils einen Datensatz für jede Breite, hier 800, 900 und 1.000.

Dieses Recordset durchlaufen wir nun in der nächsten Do While-Schleife und wollen in jedem Durchlauf eine komplette Zeile mit dem Zeilenkopf und den Werten für die Kombination aus Spaltenkopf und Zeilenkopf in den Direktbereich schreiben. Den Zeilenkopf geben wir schon einmal per Debug.Print aus, wobei wir dieser als Parameter den Wert rstZeilen!Breite plus einem Komma für einen Sprung zur nächsten Tabulatorposition statt in die nächste Zeile.

Außerdem brauchen wir für jede Zeile noch ein weiteres Recordset, das die Werte für die aktuelle Zeile und die jeweilige Spalte abruft. Dieses heißt rstWerte und enthält alle Werte des Feldes Preis der Tabelle tblMaterialpreise, deren Feld Breite den Wert des aktuellen Spaltenkopfes enthält – wieder sortiert nach dem Feld Hoehe.

Auch diese Werte durchlaufen wir in einer Do While-Schleife und geben in dieser jeweils den Inhalt des Feldes Preis aus – gefolgt von einem Komma, damit kein Zeilen-umbruch, sondern ein Sprung an die nächste Tabulator-Stelle erfolgt. Das Ergebnis nach dem Durchlaufen sieht dann wie folgt aus:

              800           900           1000         
  800           64            72            80           
  900           72            81            90           
  1000          80            90            100   

Gar nicht schlecht für den Start! Allerdings enthält diese Vorgehensweise eine kleine Schwachstelle. Wir ergänzen die Tabelle tblMaterialpreise wie in Bild 3. Wie Sie sehen, haben wir mit 700 eine neue Höhe eingeführt, aber nur Werte für die Breiten 800 und 1.000 angegeben – die Kombination aus 700 und 900 fehlt. Das Ergebnis im Direktfenster sieht dann wie in Bild 4 aus. Der Preis für die Kombination 700 und 900 wird einfach weggelassen und die übrigen Kombinationen für die Höhe von 700 mm werden einfach nach links verschoben.

Bild 3: Erweiterte Beispieldaten in der Tabelle tblMaterialpreise

Bild 4: Ausgabe der neuen Beispieldaten

Alle Kombinationen ohne Ausnahme ausgeben

Wie bekommen wir es also hin, dass alle Kombinationen berücksichtigt werden, auch wenn ein Wert nicht angegeben ist – in der Form, dass die entsprechende Stelle einfach frei gelassen wird

Dazu müssen wir eine kleine Ergänzung vornehmen, und zwar innerhalb der zweiten Do While-Schleife (siehe Listing 2). In dieser fügen wie zunächst eine Anweisung ein, welche den Datensatzzeiger des Recordsets, das wir zum Einfügen der Spaltenköpfe verwendet haben, für jeden Datensatz des Recordsets rstZeilen auf den ersten Datensatz zurücksetzt (MoveFirst). Warum das Weil wir sicherstellen wollen, dass die Markierung im Direktfenster für jeden Spaltenkopf in jeder Zeile auch um eine Tabulatorposition vorrückt – unabhängig davon, ob es auch einen Wert für die Kombination aus den im Spalten- und Zeilenkopf angezeigten Werten gibt. Innerhalb der inneren Do While-Schleife, in der wir bisher einfach nur die Werte des Feldes Preis in den Direktbereich geschrieben haben, fügen wir nun eine If…Then-Bedingung ein, die prüft, ob der Wert des Feldes Hoehe des Recordsets rstSpaltenkoepfe mit dem des Recordsets rstWerte übereinstimmt. Ist dies der Fall, wird der Inhalt des Feldes Preis ausgeben und wir gehen mit der MoveNext-Methode einen Schritt weiter im Recordset rstWerte. Falls nicht, geben wir nur einen Tabulatorschritt im Direktfenster aus. In beiden Fällen bewegen wir den Datensatzzeiger des Recordsets rstSpaltenkoepfe um eine Position weiter. Auf diese Weise geben wir für Kombinationen, für die es keinen Preis gibt, einen Tabulatorsprung im Direktbereich aus. Das Ergebnis sieht nun wie in Bild 5 aus.

Username or Email

Passwort

Angemeldet bleiben