VBA Tipp: Distinct Count

Aus DBWiki
Wechseln zu: Navigation, Suche

Anforderung

Ich möchte Datensätze ohne Duplikate zählen. Die DCount-Funktion kann das aber nicht leisten.

Lösung

  • Die Funktion lässt sich genauso wie DCount() einsetzen, zählt Duplikate aber nur einmal.
  • Die Funktion wird in einem globalen Modul gespeichert.
Public Function DistinctCount(strFeld As String, _
                              strTab As String, _
                              Optional Kriterien) As Long
 
   'Quelle: www.dbwiki.net oder www.dbwiki.de
 
   Dim strSQL As String
 
   strSQL = "SELECT COUNT(*) FROM (" _
          & "SELECT COUNT(*) AS B FROM " & strTab
 
   If Not IsMissing(Kriterien) And Not IsNull(Kriterien) Then
      strSQL = strSQL & " WHERE " & Kriterien
   End If
 
   strSQL = strSQL & " GROUP BY " & strFeld & ")"
 
   DistinctCount = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)(0)
 
End Function

Aufruf

im Direktfenster

   'nach folgendem Muster:
   ?DistinctCount("FeldName", "Tabellenname", "Kriterium nach SQL-Art")
 
   'Beispiel:
   ?DistinctCount("Bestimmungsland", "Bestellungen", "Year(Bestelldatum) = 1996")

als Steuerelementinhalt

nach folgendem Muster:
=DistinctCount("FeldName";"Tabellenname";"Kriterium nach SQL-Art")

Beispiel:
=DistinctCount("Bestimmungsland";"Bestellungen";"Year(Bestelldatum) = 1996")

Abfragebeispiel

In einer Abfrage sollen aus der Tabelle t die c_ids ermittelt werden, die sowohl mindestens 1x eine j_id mit dem Wert J1 als auch mindestens 1x eine j_id mit dem Wert J2 enthalten. Wie man leicht aus nachfolgender Tabelle erkennen kann, trifft das nur für die c_id C1 zu.

id c_id j_id
1 C1 J1
2 C1 J2
3 C2 J1
4 C2 J3
5 C2 J3
6 C3 J2
7 C3 J2
8 C4 J1
9 C4 J1
10 C1 J3
11 C1 J2

Lösung in Jet- bzw. Access-SQL

Folgende Abfrage führt zu dem Ergebnis:

SELECT c_id
FROM   t
WHERE  DistinctCount( 'j_id', 't',
                      'j_id IN( "J1", "J2" ) AND c_id = "' & c_id & '"' ) = 2 AND
       j_id IN ( 'J1', 'J2' )
GROUP  BY c_id;

Lösung in anderen SQL-Dialekten als Gegenüberstellung

In anderen SQL-Dialekten, die einen Ausdruck wie Count(DISTINCT ...) verstehen, könnten die Lösungen so aussehen:

SELECT c_id
  FROM t
 WHERE j_id IN ('J1', 'J2') 
 GROUP BY c_id
HAVING COUNT(DISTINCT j_id) = 2;

oder auch so:

SELECT c_id
  FROM t
 GROUP BY c_id
HAVING COUNT(DISTINCT CASE j_id WHEN 'J1' THEN 'J1'
                                WHEN 'J2' THEN 'J2' END) = 2;

Lösung für Access 97

Die Funktion Distinctcount erstellt anscheinend einen SQL-String, mit dem Access 97 nicht umgehen kann. Deshalb hier ein leicht veränderter Ansatz:

Function DistinctCountNeu(strFeld As String, strTab As String, Optional Kriterien)
 
   Dim rs        As DAO.Recordset
   Dim strSQL    As String
   Dim uebergabe As Long
 
   strSQL = "SELECT COUNT(*) FROM " & strTab
 
   If Not IsMissing(Kriterien) And Not IsNull(Kriterien) Then _
      strSQL = strSQL & " WHERE " & Kriterien
 
   strSQL = strSQL & " GROUP BY " & strFeld
 
   Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
   If Not rs.EOF Then
      rs.MoveLast
      uebergabe = rs.RecordCount
   End If
   rs.Close
 
   DistinctCountNeu = uebergabe
 
End Function
Wiki hinweis.png Anmerkung: Sind im Feld- oder Domänennamen Leer- oder Sonderzeichen enthalten, so müssen die jeweiligen Namen innerhalb eckiger Klammern stehen.


Weblinks