Der Benutzer hätte am liebsten entweder eine Liste mit allen kritischen Werten oder wenigstens eine Meldung, dass noch alles in Ordnung ist. Dabei ist die Darstellung der Grenzwertanalyse zunächst zweitrangig, denn vorher gilt es zu klären, wer eigentlich was und womit analysiert.
Abfragen und Funktionen analysieren
Grundsätzlich können Sie davon ausgehen, dass es um Grenzwerte von Daten geht und diese in Datensätzen auffindbar sind. Damit sollten auch alle Analysen mit Abfragen machbar sein.
Es ist schon fast mühsam, überhaupt Beispiele für kritische Parameter zu finden, die nicht in Abfragen analysierbar sind. Um auch für solche seltenen Fälle gerüstet zu sein, soll die Größe der Datenbank überwacht werden. Dafür wird statt einer Abfrage eine VBA-Funktion aufgerufen. Sobald auch diese Fähigkeit zur Funktionsverarbeitung integriert ist, gibt es gar keine Einschränkungen mehr.
Eigentlich sollen aber Abfragen im Vordergrund stehen, damit auch Benutzer ohne VBAKenntnisse später Erweiterungen vornehmen können. Das Konzept lässt sich daher in zwei wesentliche Kernaussagen zusammenfassen:
-
Alle Datensätze mit überschrittenen Grenzwerten lassen sich in beliebigen Abfragen ermitteln.
-
Jede dieser Abfragen mit mehr als 0 Datensätzen muss als Problem angezeigt werden.
Drei Warnstufen
Allerdings ist es sinnvoll, Problemwerte nicht erst dann zu melden, wenn das Kind bereits in den Brunnen gefallen ist. Es soll darum drei Stufen geben, von der Information über die Meldung bis zum wirklich überschrittenen Wert mit Stopp- Hinweis.
In Anlehnung an die Ungarische Notation werden die betreffenden Abfragen in ihren Namen entsprechend gekennzeichnet. Der Rest des Namens sollte möglichst selbsterklärend und für eine Anzeige geeignet sein.
-
QryINFO für unkritische Abfragen, deren Ergebnisse eher interessehalber verfolgt werden. Das könnten Kunden sein, deren Gesamtumsatz unter 1.000 Euro liegt, oder Seminare, für die acht Wochen vor Beginn noch keine drei Teilnehmer angemeldet sind.
-
QryMLDG für bedenkliche Abfragen, die sich in der Nähe von Grenzwerten bewegen. Das wären Kunden mit einem Saldo unter 100 Euro oder weniger als drei Teilnehmer für Seminare, die schon in drei Wochen beginnen.
-
qrySTOP für die Abfragen mit überschrittenen Grenzwerten. Dazu zählen Kunden mit negativem Saldo oder fast leere Seminare, die nächste Woche starten sollen.
Beispielabfragen in der Nordwind-DB
Da die eigentlichen Inhalte beliebig sind, können Sie beispielsweise in der Nordwind-Datenbank ein paar Abfragen erstellen wie in den Listings 1 bis 3.
Natürlich wollen Sie nicht jede Abfrage einzeln starten und sich das jeweilige Ergebnis merken.
SELECT Kunden.*, Bestellungen.[Kunden-Code]
FROM Kunden LEFT JOIN
Bestellungen ON Kunden.[Kunden-Code] = Bestellungen.[Kunden-Code]
WHERE (((Bestellungen.[Kunden-Code]) Is Null));
SELECT Kunden.[Kunden-Code],
Count(Bestellungen.[Kunden-Code]) AS [AnzahlvonKunden-Code]
FROM Kunden INNER JOIN
Bestellungen ON Kunden.[Kunden-Code] = Bestellungen.[Kunden-Code]
GROUP BY Kunden.[Kunden-Code]
HAVING (((Count(Bestellungen.[Kunden-Code]))<5));
SELECT Kunden.[Kunden-Code],
Count(Bestellungen.[Kunden-Code]) AS [AnzahlvonKunden-Code]
FROM Kunden INNER JOIN
Bestellungen ON Kunden.[Kunden-Code] = Bestellungen.[Kunden-Code]
GROUP BY Kunden.[Kunden-Code]
HAVING (((Count(Bestellungen.[Kunden-Code]))>=5 And
(Count(Bestellungen.[Kunden-Code]))<10));
Abfrageergebnisse zusammenfassen
Alternativ lassen sich alle diese Abfragen in einer Union-Abfrage zusammenfassen, wie es Listing 4 zeigt. Das lässt sich sogar für VBA-Funktionen erweitern, wie Sie später noch sehen werden. Allerdings erfüllt eine Union-Abfrage bestimmt nicht die Anforderungen an die einfache Erweiterbarkeit durch den Benutzer. Neue Abfragen können dort zwar im Grunde durch einfaches Kopieren angehängt werden, es gibt aber nicht die gewohnte grafische Unterstützung durch die Entwurfsansicht.
SELECT "qrySTOPKunden ohne Bestellungen" As xName,
Count(*) AS AnzahlvonFirma
FROM [qrySTOPKunden ohne Bestellungen]
UNION
SELECT "qryMLDGKunden mit weniger als 5 Bestellungen" As xName,
Count(*) AS AnzahlvonFirma
FROM [qryMLDGKunden mit weniger als 5 Bestellungen]
UNION
SELECT "qryINFOKunden mit 5-10 Bestellungen" As xName,
Count(*) AS AnzahlvonFirma
FROM [qryINFOKunden mit 5-10 Bestellungen];
Namen in MSysObjects finden
Besser wäre es, einfach alle Abfragen mit den passenden Namen per VBA dynamisch zu erkennen. Zwar ließen sich deren Namen durchaus mit einer Abfrage ermitteln, wenn Sie bereit sind, mit Access-internen MSys-Tabellen zu arbeiten. Jede Access-Datenbank verwaltet sich nämlich selbst in Tabellen. In MSysObjects findet sich beispielsweise die Liste aller Datenbankobjekte, auf die Sie dann per Abfrage wie in Listing 5 und Bild 2 lesend zugreifen könnten.
Damit würden Sie sozusagen im System bleiben und alle Analysen ausschließlich mit Abfragen lösen. Leider benötigen Sie für den Lesezugriff auf MSysObjects Admin-Rechte, in einer Datenbank mit differenzierten Zugriffsrechten funktioniert das also schon nicht mehr.
SELECT Mid([Name],4,4) AS Typ, MSysObjects.Name,
DCount("*",[Name]) AS Anzahl
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "qrySTOP*" Or
(MSysObjects.Name) Like "qryMLDG*" Or
(MSysObjects.Name) Like "qryINFO*"))
ORDER BY Mid([Name],4,4) DESC;
Namen per VBA finden
Daher werden die zu berücksichtigenden Abfragen per VBA ermittelt, damit diese Einschränkungen nicht gelten. In Listing 6 sehen Sie den Code, um alle Abfragen mit bestimmtem Namensanfang zu ermitteln. Bei der Namenswahl ist bewusst darauf geachtet worden, dass die „Vorsilben“ gleich lang sind, weil dann einfach die ersten sieben Zeichen mittels Select-Case-Statement geprüft werden können. Für jede gefundene Abfrage zählen Sie die Anzahl der Datensätze und zeigen diesen mit Listing 6 an, wenn er größer ist als 0. Das ist zwar technisch so in Ordnung, doch nicht besonders benutzerfreundlich. Es gibt weder eine Gesamtbewertung noch eine detaillierte Liste, dafür aber eine Unmenge von Messageboxen, die bestätigt werden müssen.
Sub GrenzwerteMitMsgbox()
Dim qryJede As QueryDef
Dim lngAnzahl As Long
For Each qryJede In CurrentDb.QueryDefs
Select Case LCase(Left(qryJede.Name, 7))
Case "qryinfo", "qrymldg", "qrystop"
lngAnzahl = DCount("*", qryJede.Name)
If lngAnzahl > 0 Then
MsgBox qryJede.Name & " hat " & _
lngAnzahl & " Datensätze"
End If
End Select
Next
End Sub
Bessere Darstellung
Es wäre kein besonderer Aufwand, alle gefundenen Abfragen in einer Listbox mit ihren Ergebnissen anzuzeigen. Aber deren fehlende Sortierbarkeit sowie die eher bescheidene optische Gestaltung spricht dafür, stattdessen ein Endlosformular zur Anzeige einzusetzen. Jedes datengebundene Formular in Access muss allerdings auf Tabellen/Abfragen basieren, und genau diese Bedingung ist hier nicht erfüllt, da die Werte ja per VBA ermittelt wurden.
Union-Statement automatisch erzeugen
Im Gegensatz zur Möglichkeit der manuellen Union-Abfrage (Listing 4) wird hier der SQL-Text per VBA erzeugt. Diese Konstruktion ist durchaus mit Vorsicht zu genießen, denn das SQL-Statement kann sehr lang werden. Je Datensatz im Ergebnis, also je Abfrage zur Analyse, wird ein Befehl wie folgt nötig sein:
SELECT "STOP" As QTyp, "Xyz" As QName, 99 As QAnzahl FROM [qrySTOPxyz] UNION …
Statt "Xyz" wird dort in Wirklichkeit ein Abfragename mit etwa 20 Zeichen stehen, das ergibt mit den übrigen 70 Zeichen jeweils einen knapp 90 Zeichen langen SQL-Befehl. Daher wird auch die Ermittlung des Namens ohne die ersten sieben Zeichen und die Anzahl der Datensätze bereits vorher per VBA vorgenommen und nur das (kürzere) Ergebnis eingetragen, was hier durch die Zahl 99 symbolisiert wird.
Da ein SQL-Befehl von Abfragen in Access auch über 32.000 Zeichen lang sein darf, können Sie mehr als 320 Analysen aneinander hängen, bevor es eng wird. Das sollte ausreichen.
Nicht in Tabelle zwischenspeichern
Die Alternative dazu wäre, die Namen und Ergebnisse in einer eigenen Tabelle zwischenzuspeichern. Das ist nicht nur wegen des umfangreichen Löschens und Neuschreibens langsamer, sondern erzwingt deswegen auch regelmäßiges Komprimieren der Datenbank.
Der Code ändert sich nun also wie in Listing 7, um die Union-Abfrage zu erzeugen. Das letzte UNION dabei ist übrig und wird durch ein Semikolon ersetzt, durch welches jeder SQL-Befehl abgeschlossen werden sollte.
Sub GrenzwerteMitUNION()
Dim qryJede As QueryDef
Dim strSQL As String
Dim lngAnzahl As Long
For Each qryJede In CurrentDb.QueryDefs
Select Case LCase(Left(qryJede.Name, 7))
Case "qryinfo", "qrymldg", "qrystop"
lngAnzahl = DCount("*", qryJede.Name)
If lngAnzahl > 0 Then
strSQL = strSQL & _
"SELECT """ & Mid(qryJede.Name, 4, 4) & _
""" As QTyp, """ & Mid(qryJede.Name, 8) & _
""" As QName, " & lngAnzahl & " As QAnzahl " & _
"FROM [" & qryJede.Name & "] UNION" & vbCrLf
End If
End Select
Next
Set qryJede = CurrentDb.QueryDefs("qryUNION")
qryJede.SQL = Left(strSQL, Len(strSQL) - Len("UNION" & vbCrLf))
End Sub
Der Code setzt übrigens voraus, dass bereits eine Abfrage qryUNION existiert. Da deren Inhalt ohnehin überschrieben wird, können Sie sie vor dem ersten Aufruf einfach als umbenannte Kopie einer anderen Abfrage bereitstellen.
Dargestellt wird jetzt eine sortierte Übersicht der wichtigsten Analysen, die jeden Benutzer zufrieden stellen sollte (Bild 3).
Bessere Bearbeitung
Möglicherweise ist aber der Benutzer jetzt zwar zufrieden, aber nicht wirklich glücklich. Schließlich weiß er nun, welches Problem er hat, nicht jedoch, wie er es lösen kann. Eine deutliche Verbesserung der Benutzerführung wäre es, per Klick neben der Zeile direkt in das passende Formular zu gelangen.
Zu jeder Abfrage muss also die Information gespeichert werden, welches Formular die passenden Daten anzeigen kann. Mit sehr viel Glück könnten Sie den Namen des Formulars im Abfragenamen wieder finden, wahrscheinlich gehören aber mehrere Abfragen zum gleichen Formular, so dass Ihr Glück schon bald überstrapaziert wäre.
Zusatzinformationen in Eigenschaften
Schreiben Sie den Formularnamen stattdessen in eine Abfrage-Eigenschaft, haben Sie wieder jede nötige Freiheit. Nicht die einzige, aber die bequemste ist die Eigenschaft Description, also die Beschreibungs-Eigenschaft, weil sie von jedem Benutzer per Rechtsklick auf den Namen und der Menüauswahl gepflegt werden kann.
Im Endlosformular frmAnalyse gibt es also im Detailbereich eine Schaltfläche btnFormular mit dem Code aus Listing 8.
Da fehlende Einträge in der Beschreibung keinen Leerstring liefern, sondern eine Fehlermeldung auslösen, muss das entsprechend berücksichtigt werden.
Private Sub btnFormular_Click()
Dim qryAktuell As QueryDef
Dim strFormular As String
Set qryAktuell = CurrentDb.QueryDefs( _
"qry" & Me.QTyp.Value & Me.QName.Value)
'Fehler aus, weil Beschreibung/Description-Eigenschaft
'fehlen könnte
On Error Resume Next
strFormular = qryAktuell.Properties("Description").Value
If Err.Number = 0 Then
On Error GoTo 0
DoCmd.OpenForm strFormular
Else
MsgBox "Formularname für " & Me.QName.Value & " fehlt!", _
vbCritical
End If
End Sub
Mehrere Parameter
Vor allem aber sollen in dem angezeigten Formular ja nur noch bestimmte Datensätze angezeigt werden. Außer dem Formularnamen muss dort daher noch ein Feldname gespeichert werden, für den der Filter gilt. Um die Parameterübergabe einfach zu halten, stehen diese Feldnamen alle in der Beschreibungs-Eigenschaft und werden durch senkrechte Striche getrennt.
Über die Filter-Eigenschaft des Formulars können Sie nun dafür sorgen, dass nur noch diejenigen Datensätze angezeigt werden, deren eindeutiges Feld in der Analyseabfrage genannt war. Dazu nutzen Sie geschachtelte SQL-Statements mit dem IN-Operator wie beispielsweise in
…WHERE [ID1] IN (SELECT [ID2] FROM [qryDemo])'
Dabei liefert das eingeklammerte SELECT eine Liste von [ID2]-Feldern, zu denen dann in der aufrufenden Abfrage die passenden Datensätze angezeigt werden. Für die Filter-Eigenschaft des Formulars wird später natürlich das Schlüsselwort WHERE weggelassen.
Filter automatisch erstellen
Das erste Feld muss im konkreten Fall durch den eindeutigen Feldnamen im anzuzeigenden Formular, das zweite Feld durch denjenigen der Abfrage ersetzt werden. Die beiden Feldnamen können, aber müssen nicht identisch sein.
Den angepassten Code finden Sie in Listing 9, dort wird auch der nun dreiteilige Inhalt der Beschreibung anhand des senkrechten Strichs auf drei Variablen verteilt.
Private Sub btnFormular_Click()
Dim qryAktuell As QueryDef
Dim strDescript As String
Dim strFormular As String
Dim strTabelle As String
Dim strFeld As String
'Eigenschaften der Abfrage auslesen
Set qryAktuell = CurrentDb.QueryDefs( _
"qry" & Me.QTyp.Value & Me.QName.Value)
strFeld = qryAktuell.SQL
'Fehler-Kontrolle aus, weil Beschreibung/Description-
'Eigenschaft fehlen könnte
On Error Resume Next
'Text vor erstem senkrechten Strich
strDescript = qryAktuell.Properties("Description").Value
strFormular = Left(strDescript, InStr(strDescript, "|") - 1)
'mittlerer Text zwischen senkrechten Strichen
strDescript = Mid(strDescript, Len(strFormular) + 2)
strTabelle = Left(strDescript, InStr(strDescript, "|") - 1)
'Text nach letztem senkrechten Strich
strDescript = Mid(strDescript, Len(strTabelle) + 2)
strFeld = strDescript
'falls Eigenschaft vorhanden war
If Err.Number = 0 Then
On Error GoTo 0
'Hilfsformular in Dialogmodus und mit Filter
DoCmd.OpenForm strFormular, , , _
strFeld & " IN (SELECT " & strTabelle & _
"." & strFeld & " FROM [" & _
qryAktuell.Name & "])", , acDialog
Else
On Error GoTo 0
'Fehlermeldung
MsgBox "Formularname für " & Me.QName.Value & " fehlt!", _
vbCritical
End If
End Sub
Analyse auch von VBA-Funktionen
Lange versprochen, aber noch nicht eingelöst ist die Berücksichtigung von Funktionen zusätzlich zu Abfragen. Es ist natürlich ein Leichtes, die gewünschten Funktionen einfach in Abfragen aufzurufen, dazu reicht die bisherige Technik der Abfragenliste. Doch dann müssen Sie für jede Funktion manuell eine zugehörige Abfrage erstellen und haben die doppelte Arbeit.
Wenn Sie dagegen die Funktionen ebenso namentlich kennzeichnen wie die Abfrage und optimalerweise in einem einzigen Modul sammeln, können sie auch automatisch ermittelt werden.
Funktionen in einem Modul sammeln
Beispielhaft sollen die Funktionen aus Listing 10 getestet werden. Der Parameter booAction dient als Umschalter, ob die Funktion mit der Angabe False nur einen Rückgabewert liefern oder mit True auch Aktionen auslösen soll. Alle Funktionen stehen im Modul modFunktionen und beginnen mit fncSTOP, fncMLDG oder fncINFO. Um diese Funktionsnamen mit ihren Ergebnissen ebenfalls in die Union-Abfrage aufzunehmen, wird der Code wie in Listing 11 erweitert.
Const dblMaxDBGroesse = 1.5 * 1024 * 1024
Function fncSTOPDatenbankGröße(Optional booAction As _
Boolean = False) As Double
Dim strText As String
strText = "Aktuelle Dateigröße:" & vbTab & _
Format(FileLen(CurrentDb.Name) / 1024 / 1024, _
"#,##0.00") & " MBytes" & vbCrLf & _
"Erwünschte Dateigröße:" & vbTab & _
Format(dblMaxDBGroesse / 1024 / 1024, _
"#,##0.00") & " MBytes" & vbCrLf & vbCrLf
If FileLen(CurrentDb.Name) > dblMaxDBGroesse Then
fncSTOPDatenbankGröße = FileLen(CurrentDb.Name)
If booAction Then
strText = strText & "Bitte komprimieren Sie die Datenbank."
MsgBox strText, vbCritical
End If
Else
fncSTOPDatenbankGröße = 0
If booAction Then
strText = strText & "Die Datenbank ist klein genug."
MsgBox strText, vbInformation
End If
End If
End Function
Function fncTest(Optional booAction As Boolean = False) As Integer
fncTest = 99
If booAction Then
MsgBox "Dieser Test ist sinnlos.", vbInformation
End If
End Function
Function fncMLDGAnzahlAdministratoren(Optional booAction As _
Boolean = False) As Integer
Dim intAdmins As Integer
intAdmins = Workspaces(0).Groups("Admins").Users.Count
'mehr als einer soll gemeldet werden
If intAdmins > 1 Then
fncMLDGAnzahlAdministratoren = intAdmins
If booAction Then
DoCmd.RunCommand acCmdUserAndGroupAccounts
End If
Else
fncMLDGAnzahlAdministratoren = 0
If booAction Then
MsgBox "Alles OK, es gibt nur einen Administrator.", _
vbInformation
End If
End If
End Function
Sub GrenzwerteUndFunctionsMitUNION()
Dim qryJede As QueryDef
Dim modFunkt As Module
Dim strSQL As String
Dim strZeile As String, strZeileAlt As String
Dim lngZeilenNr As Long
Dim lngAnzahl As Long
For Each qryJede In CurrentDb.QueryDefs
Select Case LCase(Left(qryJede.Name, 7))
Case "qryinfo", "qrymldg", "qrystop"
lngAnzahl = DCount("*", qryJede.Name)
If lngAnzahl > 0 Then
strSQL = strSQL & _
"SELECT """ & Mid(qryJede.Name, 4, 4) & _
""" As QTyp, """ & Mid(qryJede.Name, 8) & _
""" As QName, " & lngAnzahl & " As QAnzahl, " & _
"""Query"" As QQuelle " & _
"FROM [" & qryJede.Name & "] UNION" & vbCrLf
End If
End Select
Next
Set modFunkt = Modules("modFunktionen")
For lngZeilenNr = 1 To modFunkt.CountOfLines
strZeile = modFunkt.ProcOfLine(lngZeilenNr, vbext_pk_Proc)
If InStr(strZeile, "fnc") And strZeile <> strZeileAlt Then
Select Case LCase(Left(strZeile, 7))
Case "fncinfo", "fncmldg", "fncstop"
lngAnzahl = Eval(strZeile & "(0)")
If lngAnzahl > 0 Then
strSQL = strSQL & _
"SELECT """ & Mid(strZeile, 4, 4) & _
""" As QTyp, """ & Mid(strZeile, 8) & _
""" As QName, " & lngAnzahl & _
" As QAnzahl, " & _
"""VBA"" As QQuelle " & _
"FROM [MSysObjects] UNION" & vbCrLf
End If
End Select
strZeileAlt = strZeile
End If
Next
Set qryJede = CurrentDb.QueryDefs("qryUNION")
qryJede.SQL = Left(strSQL, Len(strSQL) - Len("UNION" & vbCrLf))
End Sub
Damit die Konstante vbext_pk_Proc definiert ist, muss entweder der Verweis auf Microsoft Visual´Basic for Applications Extensibility eingerichtet oder stattdessen direkt deren Wert 0 eingesetzt werden.
Entsprechend muss der Code im Button nun zwischen VBA-Funktionen und Abfragen unterscheiden können, wie Sie in Listing 12 sehen. Durch den Parameter -1, der dem Wert von True entspricht, wird beim Aufruf einer solchen Funktion der darin enthaltene Aktionsteil aufgerufen. Der Rückgabewert wird dabei ignoriert.
Private Sub btnFormular_Click()
Dim qryAktuell As QueryDef
Dim strDescript As String
Dim strFormular As String
Dim strTabelle As String
Dim strFeld As String
Select Case Me.QQuelle.Value
Case "VBA"
'Funktion mit Parameter TRUE ausführen
Eval "fnc" & Me.QTyp.Value & Me.QName & "(-1)"
Case "Query"
'Eigenschaften der Abfrage auslesen
Set qryAktuell = CurrentDb.QueryDefs( _
"qry" & Me.QTyp.Value & Me.QName.Value)
'… [Code wie in Listing 9]
'Fehlermeldung
MsgBox "Formularname für " & Me.QName.Value & " fehlt!", _
vbCritical
End If
End Select
End Sub
Zusammenfassende „Ampel“
Eine vom Benutzer gewünschte „Ampel“, also die nzeige nur des schlechtesten Zustands, ist nun ehr einfach zu erstellen. Da die Bewertungen ufällig auch alphabetisch korrekt sortiert sind,können Sie dies über die Max-Funktion lösen:
=DomMax("QTyp";"qryUNION")
Diese wird Steuerelementinhalt einer Textbox und zeigt wie in Bild 6 nur noch den maximalen Text, also die schlechteste Bewertung.
Wenn der Text wie bei einer Ampel rot für "STOP", gelb für "MLDG" und grün für "INFO" gefärbt sein soll, ergänzen Sie das Formular frmAmpel um den Code aus Listing 13. Dadurch wird die Meldung in der passenden Farbe dargestellt und lässt sich relativ klein in irgendeinem Formular mit anzeigen.
Listing 13: Ampelfarben einstellen
Private Sub Form_Current()
Select Case LCase(Me.edtAmpel.Value)
Case "stop": Me.edtAmpel.ForeColor = 255 'rot
Case "info": Me.edtAmpel.ForeColor = 65535 'gelb
Case "mldg": Me.edtAmpel.ForeColor = 65280 'grün
Case Else: Me.edtAmpel.ForeColor = 16711680 'blau
End Select
End Sub