[ACCESS/VBA/SQL] Count Distinct Values

[Popopinsel]

Hallo zusammen,

ich habe hier eine Datenbank, die ich mit MS Access 2003 bearbeite. Dort gibt es innerhalb einer Tabelle eine Spalte namens "TelefonDurchwahl". Dort eingetragen sind z.T. identische Rufnummern, teilweise sind die Felder aber auch leer.

Nun möchte ich einen Button auf einem Forumular haben, der mir bei betätigen eine MessageBox öffnet, in der drin steht, wie viele Telefone es gibt, d.h. wie viele unterschiedliche Rufnummern (ohne die leeren Felder!) in der Tabelle eingetragen sind.

Bisher bin ich so weit:

Private Sub cmd_count_Click()

Dim cDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set cDB = CurrentDb
strSQL = "SELECT COUNT(DISTINCT [TelefonDurchwahl]) FROM Tabelle_Telefonverwaltung;"
Set rst = cDB.OpenRecordset(strSQL)

MsgBox "Anzahl der Telefone: " & rst.RecordCount, vbInformation + vbOKOnly, "Hinweis"

End Sub

Leider scheint das nicht so ganz zu funktionieren, denn sobald ich z.B. eine noch nicht vorhandene Nummer in ein bisher leeres Feld für die Rufnummern eintrage, wird die Zahl in der MessageBox bei erneutem betätigen des Buttons nicht aktualisiert (sie bleibt gleich).

Hat jemand Rat?

[Popopinsel]

Hab meinen Fehler gefunden... -.-

1. Mit dem zählen ist doppelt gemoppelt. Hab das COUNT aus der SQL-Abfrage gestrichen.
2. Ich habe nur GEDACHT, es würde nicht aktualisieren. Denn die Zahlen, die ich eingegeben habe, waren alle schon vorhanden (es sind an die 800 Geräte/Rufnummern in der DB), ich hab mich lediglich durch die Sortierung irreführen lassen.
3. Kann auch sein, dass ich die Tabelle immer erst schließen muss, bevor ich die Abfrage über den Button ausführe, jedenfalls gehts jetzt.

Sorry für den sinnlosen Thread! *schäm* =)

[Popopinsel]

Ein Problem bleibt aber:

Selbst wenn es in der Spalte keinen einzigen Eintrag gibt, also wenn alle Felder leer sind, wird es als 1 Telefon angesehen, da anscheinend auch leere Felder zählen. Wie kann ich das verhindern?

Edit: Never mind...

Private Sub cmd_count_Click()

Dim cDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set cDB = CurrentDb
strSQL = "SELECT DISTINCT [TelefonDurchwahl] FROM [Tabelle_Telefonverwaltung] WHERE TelefonDurchwahl<>'';"
Set rst = cDB.OpenRecordset(strSQL)

MsgBox "Anzahl der Telefone: " & vbCrLf & rst.RecordCount, vbInformation + vbOKOnly, "Hinweis"

End Sub

[Popopinsel]

Mein Vorhaben hat sich etwas geändert, daher hab ich doch noch ein paar Fragen:

Ich möchte jetzt nicht mehr nur die Anzahl aller unterschiedlichen Rufnummern (=Anzahl der Telefone), sondern diese den Organisationen zugeordnet in ein Excel-Sheet exportieren. So soll es dann in etwa aussehen:


Organisation | Anzahl der Telefone
---------------------------------
A | 100
B | 50
C | 90
D | 30


Im Moment tüftel ich erstmal an der Zordnung der Telefonanzahl zu den Organisationen. Dazu hab ich mir folgendes gedacht:

1. Mittels SELECT DISTINCT ermittle ich aus der Spalte "Organisation" alle (unterschiedlichen) Organisationen.
2. Diese würde ich gerne in einem Array speichern.
3. Da ich jetzt a) alle Bezeichnungen der Organisationen habe und b) auch deren Anzahl, kann ich daraus eine Schleife machen. Diese läuft von i=0 bis das Ende des Array erreicht ist und führt bei jedem Durchlauf ein SELECT COUNT (DISTINCT) der Spalte "Telefondurchwahl" WHERE "Organisation=Array[i]".
4. Dadurch kann ich a) dynamisch die Bezeichnungen der Organisationen und b) die zugehörige Anzahl an Telefonen auslesen.
5. Zum Schluss wird dann ein Excel-Sheet nach o.g. Layout erstellt.

Daher meine Fragen:

1. Wie kann ich den Rückgabewert der SQL-Abfrage (SELECT) in einem Array abspeichern? (Zum testen: Wie kann ich das Array als String für eine Msgbox ausgeben?)
2. Wie baue ich die Schleife auf?
3. Wie kann ich die Daten (nach o.g. Layout) in ein Excel-Sheet exportieren?

Hoffentlich seid ihr noch gewillt mir helfen... :tongue:

[Unfug]

Select distinct orga, count(tel) from Tabelle1 group by orga

orga = Spaltenname der Organisation
tel = Spaltenname der Telefonnummern

[Popopinsel]

Select distinct orga, count(tel) from Tabelle1 group by orga

orga = Spaltenname der Organisation
tel = Spaltenname der Telefonnummern

Danke Dir, werd ich mal probieren! Wie kann ich das denn zum testen mal ausgeben (z.B. Msgbox)?

Edit: Ich denke außerdem nicht, dass ich damit genau das erreichen werde, was ich möchte. Denn das gibt mir ja nur die Anzahl der Telefone pro Organisation aus, aber nicht auch die Organisation selbst. Mit dem SELECT DISTINCT der Spalte "Organisation" bekomme ich nämlich genau diese Bezeichnungen, auch wenn es mal neue Einträge geben sollte.

Wie dem auch sei, ich muss erstmal wissen, wie ich an die Rückgabe einer SELECT-Abfrage komme...

[Unfug]

Doch!
Diese SQL Abfrage liefert dir 2 Spalten.
Einmal die Organisation (gefiltert mit Distinct) und einmal eine mit der Anzahl der Telefone.

Eine SELECT Abfrage liefert dir immer ein Array zurück. In diesem Fall ein 2-Dimensionales
Array[Organsiation][AnzahlDerTelefonnummer].

Du kannst unter VBA einfach das RecordSet durchlaufen und z.B. in die verschiedenen Excel Zeilen eintragen.

Wenn Du über Access-VBA programmierst, dann musst Du im VBA Code erstmal eine ExcelDatei öffnen (als Objekt) und damit kannst du dann auf die Eigenschaften (und somit auch die Zellen) dieser Excel-Instanz zurückgreifen.

Klingt alles schwieriger als es ist. Ich empfehle Dir mal in
office-loesungen.de zu suchen. Da wirst du Code für das Exportieren finden. Da bin ich mir ganz sicher.

P.S: Um den SQL Befehl zu testen, gehe einfach mal in Access, Abfrage erstellen (SQL Ansicht), und trag dort den SQL Befehl ein. Dann Ausführen und Du siehst wie die Rückgabe aussieht.

[CrazyIvan]

Kleine Anmerkung: Das "Distinct" kann man sich sparen, sofern eine GROUP BY-Klausel enthalten ist. Die sorgt eh dafür, dass jeder Wert der gruppierten Spalte nur genau einmal vorkommt.

[Popopinsel]

Also irgendwie scheint es mit dieser recht simplen Methode ein Problem zu geben. Hier noch einmal ein kleiner Ausschnitt aus meiner Datenbank-Tabelle:

Organisation ----- Durchwahl
----------------------------
XX 123
XX 234
XX 234
XX 234
XX
XX
XX 234
XX
XX 567
YY 123
YY 345
YY 345

Demnach gibt es in Organisation "XX" genau 3 Telefone und in "YY" 2 Telefone. Nur liefert mir o.g. Abfrage für "XX" 6 Telefone. Er scheint nämlich die leeren Zeilen mitzuzählen oder aber danach auftauchende, eigentlich bereits als doppelt gefundene Einträge (die 234) wieder neu hinzuzuzählen.

Also meine Tabelle sieht nicht genau so aus, jedenfalls gibt er mir 1 oder 2 Telefone mehr aus, als es eigentlich gibt.

@CrazyIvan

Wie meinst Du das, das DISTINCT kann weg? Bedeutet das GROUP BY am Ende, dass er die Organisationen eh nur 1 Mal listet?

[CrazyIvan]

Genau. Wenn etwas gruppiert wird, dann bedeutet das per definitionem, dass aus dieser Gruppe jedes Element nur einmal aufgelistet wird.

Probier mal folgendes:


select organisation, count(distinct durchwahl) as AnzDurchwahlen
from Tabelle
where isnull(durchwahl, '') <> ''
group by organisation

Damit filterst Du sowohl Zeilen mit leeren durchwahl-Feldern als auch solche, bei denen NULL im durchwahl-Feld steht. Letzteres spielt nur dann eine Rolle, wenn das Feld nullable ist. Ich habs mal sicherheitshalber mit reingemacht. Das '' steht für leer und könnte in Access auch "" sein - kenne mich mit Access nicht soo genau aus.

[Popopinsel]

Vielen Dank für Deine Hilfe!

Leider bekomme ich mit dieser Abfrage einen Laufzeitfehler '3075': Syntaxfehler (fehlender Operator) in Abfrageausdruck 'COUNT(DISTINCT Telefondurchwahl)'

Das hatte ich schon mal, als ich versucht habe, innerhalb eines COUNTs mit DISTINCT zu arbeiten... Irgendeine Idee?

[Popopinsel]

Wie ich gerade gelesen habe, kann man mit Access kein COUNT(DISTINCT) machen. Was allerdings geht, ist:

SELECT COUNT(*) As Anzahl FROM (SELECT DISTINCT Telefondurchwahl FROM Tabelle_Telefonverwaltung WHERE NOT ISNULL(Telefondurchwahl))

Nur bekomme ich dann natürlich nur die Gesamtanzahl der Telefone. Wie verschachtel ich das jetzt, um mein erwünschtes Ergebnis zu erhalten? :confused:

P.S.: Hab die Bedingung mal angepasst, so nämlich werden leere Felder nicht aufgezählt.

[CrazyIvan]

Okay, kein count(distinct x)... Access ist halt ein Dreckstool ;)


select organisation, count(telefondurchwahl)
from (
select distinct organisation, telefondurchwahl
from Tabelle
where telefondurchwahl <> ''
)
group by organisation

Ein distinct im select bezieht sich, abgesehen von count(distinct), immer auf die gesamte Zeile. D. h., das innere Statement gibt alle Kombinationen von orga und durchwahl genau 1x zurück. Darüber kannst Du dann im äusseren Statement dein gewünschtes Count machen. Sollte jetzt aber wirklich passen ;)

[Popopinsel]

Alles klar, ich danke Dir Ivan! =) Werde es morgen auf der Arbeit ausprobieren...

Ich hoffe mal, dass dann endlich die richtigen Zahlen ausgegeben werden und nicht wie bisher und hier (http://www.office-loesung.de/ftopic351293_0_0_asc.php) beschrieben (ganz unten).

Edit: CrazyIvan... You are my hero! :D Es funktioniert endlich! Jetzt wird die richtige Anzahl angezeigt! 1000 Dank! =)