Kunden nach bestellten Produkten filtern

Kunden nach bestellten Produkten kann jeder filtern, der sich ein wenig mit dem Abfrageentwurf beschäftigt hat. Etwas aufwendiger ist es schon, ein Formular zu erstellen, dass verschiedene Möglichkeiten zum Filtern von Kunden nach den bestellten Produkten bietet. Hier wollen wir beispielsweise ein Produkt auswählen, sodass direkt alle Kunden in einer Liste angezeigt werden, die dieses Produkt bestellt haben. Oder wir gehen noch einen Schritt weiter und wollen Kunden anzeigen, die mindestens eines von mehreren Produkten geordert haben. Um dann vielleicht noch solche Kunden auszuschließen, die bereits ein bestimmtes anderes Produkt besitzen. Also auf ins Abenteuer!

Warum Kunden nach Produkten filtern?

Bevor wir uns an die Arbeit dieses recht aufwendigen Unterfangens machen, wollen wir uns überlegen, wozu wir das Ergebnis überhaupt nutzen können. Mir als Shopbetreiber fällt da direkt ein, Kunden, die ein bestimmtes Produkt erworben haben, über eine neue Version dieses Produkts zu informieren. Oder man möchte dem Kunden mitteilen, dass eine Lizenz ausläuft, damit er diese verlängern kann. Vielleicht wollen wir auch einfach Kunden, die Produkt A bestellt haben, eine Empfehlung zu dem dazu passenden Produkt B geben. Dabei wollen wir dann natürlich nur Kunden anschreiben, die Produkt B noch nicht bestellt haben. Sie sehen: Es lohnt sich, die Kunden nach den bestellten Produkten selektieren zu können.

Voraussetzungen

Als Basis für die Ermittlung von Kunden nach den bestellten Produkten verwenden wir die Beispieldatenbank, die wir bereits in der Beitragsreihe zur Rechnungsverwaltung vorgestellt haben. Den ersten Teil dieser Beitragsreihe finden Sie übrigens unter dem Titel Rechnungsverwaltung: Datenmodell (www.access-im-unternehmen.de/1385).

Der relevante Teil des Datenmodells dieser Datenbank ist in Bild 1 abgebildet.

Bild 1: Tabellen, die am Filtern von Kunden nach Produkten beteiligt sind

Geplante Funktionen für die Lösung

Wir wollen mit dem Formular zum Filtern von Kunden nach bestellten Produkten möglichst flexibel arbeiten können.

Also schauen wir uns zunächst an, welche Filtermöglichkeiten wir abbilden wollen:

• Filtern nach Kunden, die ein bestimmtes Produkt bestellt haben
• Filtern nach Kunden, die eines von mehreren Produkten bestellt haben (Oder-Verknüpfung)
• Filtern nach Kunden, die ein bestimmtes Produkt nicht bestellt haben
• Filtern nach Kunden, die bestimmte Produkte nicht bestellt haben (Und-Verknüpfung)

Die ersten beiden Filtermöglichkeiten sollen außerdem per Und-Verknüpfung mit den letzten beiden Möglichkeiten kombiniert werden.

Einfache Auswahl der Produkte, nach denen gefiltert werden soll

Wir wollen zwei Listenfelder im Formular anzeigen, die unterschiedliche Produkte enthalten:

• Das erste Listenfeld soll die Produkte anzeigen, von denen der Kunde mindestens eines bestellt hat.
• Das zweite Listenfeld soll die Produkte anzeigen, die der Kunde nicht bestellt hat.

Um die beiden Listenfelder zu füllen, bieten wir ein drittes Listenfeld an, das alle Produkte enthält. Aus diesem wählen wir dann die Produkte aus, die den beiden zuvor genannten Listenfeldern hinzugefügt werden sollen.

Um aus diesem Listenfeld komfortabel die gewünschten Produkte auswählen zu können, wollen wir über diesem Listenfeld noch ein Textfeld einfügen, mit dem wir die angezeigten Produkte nach dem eingegebenen Text filtern können.

Unter den Listenfeldern zur Auswahl der zu filternden Produkte fügen wir schließlich noch ein Unterformular ein, das die Kunden anzeigt, welche die den Listenfeldern hinzugefügten Produkte entweder bestellt haben – oder auch nicht.

Unterformular zur Anzeige der Kunden

Als Erstes legen wir das Unterformular sfmKundenNachProduktenFiltern an. Dieses soll die Kunden anzeigen, die den im Hauptformular festgelegten Kriterien entsprechen. Beim Öffnen des Formulars jedoch soll es zunächst alle Kunden anzeigen. Deshalb stellen wir die Eigenschaft Datensatzquelle auf eine Abfrage ein, welche die Tabelle tblKunden als Datenherkunft verwendet und davon die Felder KundeID, AnredeID, Nachname, Vorname und EMail anzeigt, sortiert nach den Feldern Nachname und Vorname (siehe Bild 2).

Bild 2: Abfrage für das Unterformular

Anschließend ziehen wir alle Felder dieser Abfrage aus der Feldliste in den Entwurf des Formulars (siehe Bild 3).

Bild 3: Entwurf des Unterformulars

Damit dieses Formular seine Daten in der Datenblattansicht anzeigt, stellen wir seine Eigenschaft Standardansicht auf den Wert Datenblatt ein. Nun schließen und speichern wir das Unterformular.

Hauptformular zum Filtern der Kunden

Dann legen wir ein weiteres neues Formular namens frmKundenNachProduktenFiltern an und fügen diesem folgende Steuerelemente hinzu:

• Textfeld txtProduktfilter
• Listenfeld lstAlleProdukte
• Listenfeld lstBestellteProdukte
• Listenfeld lstNichtBestellteProdukte
• Schaltfläche cmdZuBestelltenProdukten
• Schaltfläche cmdZuNichtBestelltenProdukten

Außerdem ziehen wir aus dem Navigationsbereich das Formular sfmKundenNachProduktenFiltern in das Hauptformular. Die Steuerelemente ordnen wir dabei wie in Bild 4 an.

Bild 4: Anordnung der Steuerelemente im Hauptformular

Listenfelder mit Daten füllen

Das Listenfeld lstAlleProdukte soll alle Produkte anzeigen mit Ausnahme derer, die bereits zu einem der Listenfelder lstBestellteProdukte oder lstNichtBestellteProdukte hinzugefügt wurden.

Das Listenfeld lstBestellteProdukte soll alle Produkte anzeigen, die nach Markierung im Listenfeld lstAlleProdukte mit der Schaltfläche cmdZuBestelltenProdukten hinzugefügt wurden.

Das Listenfeld lstNichtBestellteProdukte soll analog alle Produkte anzeigen, die nach Markierung im Listenfeld lstAlleProdukte mit der Schaltfläche cmdZuNichtBestelltenProdukten hinzugefügt wurden.

Nun müssen wir allerdings noch festlegen, wie wir die Produkte markieren, die in einem der beiden Listenfelder lstBestellteProdukte oder lstNichtBestellteProdukte angezeigt werden.

Tabellen zum Speichern der ein- und auszuschließenden Produkte

Wir könnten die beiden Listenfelder einfach mit dem Wert Wertliste für die Eigenschaft Herkunftsart ausstatten und die Produkte als String-Liste der Eigenschaft Datensatzherkunft hinzufügen. Wenn wir die Produkte in diesen beiden Listen allerdings jederzeit nach dem Produktnamen sortiert anzeigen wollen, haben wir mehr Programmieraufwand. Und da wir in Access arbeiten, legen wir schnell zwei Tabellen namens tblBestellteProdukte und tblNichtBestellteProdukte an. Die erste sieht in der Entwurfsansicht wie in Bild 5 aus und enthält lediglich ein Zahlenfeld zum Speichern der ProduktID-Werte, nach denen die Kunden gefiltert werden sollen. Dieses Feld haben wir als Primärschlüsselfeld definiert, jedoch logischerweise nicht mit dem Datentyp Autowert. Die zweite unterscheidet sich nur durch den Namen von der ersten Tabelle. Tipp zum Zeit sparen: Einfach die erste Tabelle kopieren und unter dem Namen tblNichtBestellteProdukte speichern.

Bild 5: Entwurf der Tabelle tblBestellteProdukte

Datensatzherkunft der Listenfelder lstBestellteProdukte und lstNichtBestellteProdukte

Die Datensatzherkunft der beiden rechten Listenfelder können wir nun bereits einstellen. Die für das Listenfeld lstBestellteProdukte gestalten wir wie in Bild 6. Sie soll alle Datensätze der Tabelle tblProdukte anzeigen, deren Wert im Feld ProduktID in der Tabelle tblBestellteProdukte gespeichert ist. Nachdem Sie die beiden Tabellen zum Abfrageentwurf hinzugefügt haben, müssen Sie die Beziehung zwischen den Feldern ID der Tabelle tblProdukte und ProduktID der Tabelle tblBestellteProdukte manuell hinzufügen. Diese Beziehung wollen wir nicht im Datenbankfenster festlegen.

Bild 6: Datensatzherkunft des Listenfeldes lstBestellteProdukte

Die Datensatzherkunft für das Listenfeld lstNichtBestellteProdukte erstellen wir analog, diesmal verwenden wir jedoch die Tabelle tblNichtBestellteProdukte als zweite Tabelle.

Weitere Einstellungen für die Listenfelder

Für alle drei Listenfelder nehmen wir nun die Format-Einstellungen vor. Hier legen wir für die Eigenschaft Spaltenanzahl den Wert 2 und für Spaltenbreiten den Wert 0cm fest. So zeigen die Listenfelder immer nur die Produktbezeichnung an, aber nicht den Wert der gebundenen Spalte ProduktID.

Daten des Listenfeldes lstAlleProdukte

Der Name dieses Listenfeldes ist eigentlich irreführend, denn es zeigt nur direkt nach dem Öffnen des Formulars alle Produkte an. Sobald der Benutzer über das Textfeld txtProduktfilter einen Filter eingegeben hat oder eines der Produkte in eines der rechten Listenfelder verschoben hat, zeigt es nicht mehr alle Produkte an.

Es soll dann nur noch die Produkte anzeigen, die nach dem Filtern und dem Entfernen der bereits in einem der übrigen Listenfeldern enthaltenen Produkte übrig bleiben.

Zuerst aber soll dieses Listenfeld einfach alle Produkte anzeigen, die in der Tabelle tblProdukte enthalten sind – und zwar nach dem Alphabet sortiert. Dazu weisen wir der Eigenschaft Datensatzherkunft des Listenfeldes die Abfrage aus Bild 7 zu.

Bild 7: Datensatzherkunft des Listenfeldes lstAlleProdukte

Die enthaltenen Daten sollen anschließend nach verschiedenen Aktionen aktualisiert werden. Bei den Aktionen handelt es sich um die folgenden:

• Filtern über das Textfeld txtProduktfilter nach dem Auslösen des Ereignisses Bei Änderung
• Hinzufügen eines der Produkte zu den Listenfeldern lstBestellteProdukte und lstNichtBestellteProdukte mit den Schaltflächen cmdZuBestelltenProdukten und cmdZuNichtBestelltenProdukten
• Entfernen eines der Produkte aus den Listenfeldern lstBestellteProdukte und lstNichtBestellteProdukte per Doppelklick auf einen der Einträge der Listenfelder

Deshalb rufen wir von all den durch die oben beschriebenen Aktionen Prozeduren auf, welche die Datensatzherkunft des Listenfeldes lstAlleProdukte aktualisiert (und später auch die Liste der Kunden).

Diese Prozeduren sollen ProdukteAktualisieren und KundenAktualisieren heißen.

Prozeduren zum Aktualisieren von Produkten und Kunden

Die Prozeduren ProdukteAktualisieren und KundenAktualisieren sollen den im Textfeld txtProduktfilter eingegebenen Suchbegriff berücksichtigen und zusätzlich über die in die beiden Tabellen tblBestellteProdukte und tblNichtBestellteProdukte gespeicherten Einträge die noch verfügbaren Produkte im Listenfeld lstAlleProdukte anzeigen. Schließlich soll noch die Anzeige der Kunden passend zu den in den beiden Tabellen gespeicherten Daten aktualisiert werden.

Da diese Prozeduren sowohl durch das Ereignis Bei Änderung des Textfeldes txtProduktfilter ausgelöst werden kann als auch durch das Hinzufügen oder Entfernen von Einträgen zu den Tabellen tblBestellteProdukte und tblNichtBestellteProdukte, haben wir ein kleines Problem: Beim Aufruf über das Textfeld können wir auf den aktuellen Inhalt des Textfeldes nur über die Eigenschaft Text des Textfeldes zugreifen. Beim Aufruf über eines der anderen Ereignisse müssen wir die Value-Eigenschaft des Textfeldes verwenden.

Wir können das auf verschiedene Arten lösen. Eine ist, den Wert des Textfeldes jeweils in einer Variablen zu speichern, auf die wir dann von der Prozedur ProdukteAktualisieren aus zugreifen. Die zweite ist, dass wir den Inhalt der Variablen Text beim Aufruf vom Textfeld aus als Parameter übergeben und beim Aufruf von den Schaltflächen beziehungsweise Listenfeldern den Wert über die Eigenschaft Value ermitteln und dann ebenfalls per Parameter übergeben.

Da Variablen unter Umständen ihren Wert verlieren können, nutzen wir die letztere Variante.

Nach Produktname filtern

Die Eingabe beziehungsweise Änderung des Inhalts des Textfeldes txtProduktfilter soll die im Listenfeld lstAlleProdukte enthaltenen Datensätze filtern. Dazu hinterlegen wir für die Ereigniseigenschaft Bei Änderung die folgende Prozedur:

Private Sub txtProduktfilter_Change()
     ProdukteAktualisieren Me!txtProduktfilter.Text
End Sub

Diese ruft lediglich eine weitere Prozedur namens ProdukteAktualisieren auf und übergibt dieser den aktuellen Inhalt des Textfeldes. Diese Prozedur beschreiben wir weiter unten. Erst schauen wir uns weitere Aufrufe dieser Prozedur an.

Hinzufügen von Produkten als einschließende Kriterien für den Kundenfilter

Wenn der Benutzer wie in Bild 8 einen der Einträge des Listenfeldes lstAlleProdukte markiert und dann auf die Schaltfläche cmdZuBestelltenProdukten klickt, löst dies das Ereignis Beim Klicken der Schaltfläche aus.

Bild 8: Hinzufügen eines Produkts zu den bestellten Produkten

Die Prozedur gestalten wir wie in Listing 1. Sie prüft zunächst, ob überhaupt ein Eintrag im Listenfeld lstAlleProdukte markiert ist. Falls ja, erstellt sie eine Referenz auf das aktuelle Database-Objekt und führt damit eine INSERT INTO-Aktionsabfrage aus.

Private Sub cmdZuBestelltenProdukten_Click()
     Dim db As DAO.Database
     If Not IsNull(Me!lstAlleProdukte) Then
         Set db = CurrentDb
         db.Execute "INSERT INTO tblBestellteProdukte(ProduktID) VALUES(" & Me!lstAlleProdukte & ")", dbFailOnError
         ProdukteAktualisieren Nz(Me!txtProduktfilter)
         KundenAktualisieren
     End If
End Sub

Listing 1: Produkt zum Listenfeld der als Kriterien einzuschließenden Produkte hinzufügen

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