Access anzahl gleicher werte

G

Gast

• 21. Juli 2009

• #1

Hi ,)

wäre Euch sehr dankbar, wenn Ihr mir eine Lösung für folgende Problemstellung anbieten könntet .)

Es geht hier um folgendes: ich habe eine kleine Tabelle "Essen" (2 Spalten):
------------------------
Gruppe Wert
------------------------
Essen Burger
Essen Käse
Essen Salat
Essen Burger
Essen Erdbeeren
Essen Erdbeeren
Essen Käse
Essen Käse
Essen Käse

Nun will ich die Werte für die Gruppe "Essen" zusammenzählen, sodass es mir als Ergebnis folgendes ausgibt:

Das Essen besteht aus:
2 Burger
4 Käse
1 Salat
2 Erdbeeren

Natürlich kann ich die Abfrage so gestalten: =Count (IIF([Essen]![Wert])="Burger";0)
damit muss ich aber für jeden Wert (sprich Lebensmittel - im Beispiel sind es nur 4, hab aber viel mehr) eigenen Zähler basteln.

Nun die Frage: kann man (obs überhaupt möglich ist?) es so machen, dass es die Werte automatisch "selbst" durchläuft und die Werte zusammenzählt? =Count (IIF ([Essen]!=*);0) oder so...

komme leider nicht weiter...

Danke im voraus!
Grüsse,
Gast

Sprich: anstatt manuell jedes Mal Count (...Burger...), Count (...Käse...) u.s.w einzugeben,

 

D

dbwizard

Erfahrenes Mitglied

• 21. Juli 2009

• #3

Gast hat gesagt.:

Hi ,)

wäre Euch sehr dankbar, wenn Ihr mir eine Lösung für folgende Problemstellung anbieten könntet .)


komme leider nicht weiter...

Danke im voraus!
Grüsse,
Gast

Sprich: anstatt manuell jedes Mal Count (...Burger...), Count (...Käse...) u.s.w einzugeben,

Zum Vergrößern anklicken....

Hallo,

Code:

SELECT COUNT (wert), wert FROM mytable WHERE GRUPPE='Essen' GROUP BY wert

(Dies ist Oracle, hoffe es funktioniert auch in Access...)



Gruss

 

G

Gast

• 21. Juli 2009

• #4

Oh my got, they killed Kenny (c)

das ist doch sooo einfach...

funktioniert wunderbar .)

Ich danke EUCH vielmals! Entschuldigt bitte, dass es so ne frage war

Grüsse,
Gast

 

Eine MDB enth�lt eingebundene Tabellen. �ndert sich das Verzeichnis der Herkunfts-DB dieser Tabellen, dann geht die Verbindung verloren.

L�sung

Die einfachste Variante ist nat�rlich, die eingebundenen Tabellen zu l�schen und �ber den Men�punkt Datei/Externe Daten/Tabellen verkn�pfen neu einzubinden.

Eine weitere Methode ist die Verwendung des Tabellenverkn�pfungs-Managers, der im Men� Extras/Datenbank-Dienstprogramme zu finden ist.

Wenn die Wiedereinbindung dynamisch mit VBA passieren soll, gibt's die M�glichkeit, einen Dateidialog zu verwenden und Code wie in:
//www.mvps.org/access/tables/tbl0009.htm

Eine Komplettl�sung samt Dateidialog und auch f�r mehrere Backends bietet der JStreet Access Relinker: //www.jstreettech.com/downloads.aspx

Wenn sicher ist, dass sich beide DBs im gleichen Verzeichnis befinden, l�sst sich die Wiedereinbindung auch komplett mit VBA automatisieren. Im Autoexec-Makro oder beim �ffnen des ersten Formulares kann z.B. folgender Code aufgerufen werden:

'******* CODE START *******
On Error GoTo MyError

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer

Set db = CurrentDB()

strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"

For i = 0 To db.TableDefs.Count - 1
  If db.TableDefs(i).Connect <> "" Then
    If Mid(db.TableDefs(i).Connect, 11) <> strDaten Then
      db.TableDefs(i).Connect = ";database=" & strDaten
      db.TableDefs(i).RefreshLink
    End If
  End If
Next i

MyExit:
  Exit Sub

MyError:
  MsgBox "Bei der Installation ist eine Ausnahme aufgetreten. ", 16, "Ausnahme"
  Resume MyExit
'******* CODE ENDE *******

nach oben

//www.donkarl.com?FAQ3.2aktualisiert 2008-12-12

Problem

Du m�chtest ein Autowert-Feld einer Tabelle wieder auf 1 oder den n�chsth�heren freien Wert zur�cksetzen, nachdem Datens�tze eingegeben und wieder gel�scht wurden.

L�sung

Komprimiere die Datenbank: Men� Extras/Datenbank-Dienstprogramme/Datenbank komprimieren
Das setzt die Autowerte aller Tabellen auf den n�chsth�heren freien Wert zur�ck.

In Versionen >=A00 funktioniert das oft nicht mehr. s. //support.microsoft.com/?kbid=287756
Die dort vorgestellten L�sungen sind allerdings unn�tig kompliziert, denn ab JET4, d.h. ab A00, kann man Startwert und Schrittweite (Seed und Increment) eines Autowertfeldes per SQL einstellen:

ALTER TABLE Tabelle ALTER COLUMN Feld COUNTER(1,1)

Die erste Zahl setzt den Startwert auf 1 zur�ck, die zweite stellt die Schrittweite auf 1 ein (kann man auch weglassen, inkl. Komma).

Autowerte sind �brigens f�r interne Zwecke gedacht, z.B. als Schl�ssel- und Beziehungsfelder, nicht f�r die Erzeugung sch�ner Nummern. Die sollte man per Programmierung, �ber Standardwerte oder zu Fu� erzeugen.

nach oben

//www.donkarl.com?FAQ3.3

Problem

Du m�chtest als Standardwert eines Tabellenfeldes eine Funktion wie z.B. AktuellerBenutzer (bzw. CurrentUser()) oder eine selbst erstellte VBA-Funktion oder einen Bezug auf ein Formularfeld etc. verwenden. Entweder wird die Funktion in einen Text umgewandelt (Access f�gt " " drumherum) oder es kommt eine Fehlermeldung: Standardwert wird nicht erkannt, unbekannte Funktion o.�.

Ursache

Im Standardwert eines Tabellenfeldes sind benutzerdefinierte Funktionen oder Aggregatfunktionen von Access (DomWert etc.) sowie best. Funktionen wie CurrentUser oder Eval nicht m�glich. Ebenso funktionieren Verweise auf Access-Objekte nicht.

L�sung

Verwende Formulare. Nur bei Steuerelementen von Formularen sind o.a. Funktionen bzw. Bez�ge als Standardwert m�glich.

nach oben

//www.donkarl.com?FAQ3.4

Problem

Du m�chtest automatisiert pr�fen, ob eine bestimmte Tabelle bereits in der DB vorhanden ist.

L�sung

Daf�r gibt es mehrere Varianten. Man kann z.B. den Fehler abfangen, der bei Aufruf einer nicht vorhandenen Tabelle erzeugt wird. Sauberer ist aber ein �berpr�fen der TableDefs-Auflistung. Dazu kannst du folgende Funktion in ein Standardmodul kopieren:

Function fctTableExists(strTableName As String) As Boolean

  Dim db As DAO.Database
  Dim tdf As DAO.TableDef
  Set db = CurrentDb
  For Each tdf In db.TableDefs
    If tdf.Name = strTableName Then fctTableExists = True: Exit For
  Next

End Function

Eine andere Methode ist die Verwendung der Systemtabelle MSysObjects:

Function fctTableExists(strTableName As String) As Boolean

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer1

End Function

Aufruf in beiden F�llen dann irgendwo in der DB mit:
Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer3

nach oben

//www.donkarl.com?FAQ3.5

Problem

Du m�chtest programmatorisch den Typ oder die Gr��e eines Feldes �ndern.

L�sung

Vor der Version A00 ging das nur mit umst�ndlicher Programmierung. Beispiel f�r das m�gliche Vorgehen beim �ndern des Typs eines Feldes in ein Textfeld bzw. �ndern der Feldgr��e eines Textfeldes:

'************ CODE START ************
Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer4

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer5

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer6

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer7

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer8
'************ CODE ENDE ************

Seit JET4, d.h. ab Version A00, ist dieses komplizierte Vorgehen nicht mehr n�tig. Der ALTER TABLE-Befehl von Access-SQL wurde um ALTER COLUMN erweitert (s.). F�r die gleiche Aufgabe reicht daher (mit obiger db-Variable) die Zeile:

Dim db As DAO.Database
Dim strDaten As String
Dim i as Integer9

nach oben

//www.donkarl.com?FAQ3.6

Problem

Beim Versuch, einen Datensatz zu speichern, erscheint der Fehler 3047 "Datensatz zu gro�."

Ursache

Ein Datensatz einer Access-(d.h. Jet-)Tabelle ist mit ca. 2000 Zeichen begrenzt. In Versionen >=A00 erh�ht sich dieses Limit auf ca. 4000 Zeichen, wenn bei Textfeldern die Eigenschaft Unicode-Kompression auf Ja eingestellt ist.
Dabei kommt es nie auf die Eigenschaft Feldgr��e an, sondern auf die tats�chlich enthaltenen Zeichen.

L�sung

Memofelder werden bei dieser Begrenzung nicht eingerechnet, weil sie anders verwaltet werden. Du kannst also evtl. die Grenze umgehen, wenn du einige der (gr��eren) Textfelder in Memos umwandelst.

Ansonsten kannst du die Tabelle auch noch in mehrere gleichartige Tabellen aufteilen und zwischen diesen Tabellen eine 1:1-Beziehung setzen.

nach oben

//www.donkarl.com?FAQ3.11aktualisiert 2021-10-25

Problem

Du m�chtest in einer Abfrage ein Feld mit einer fortlaufenden Nummer oder fortlaufenden Summe haben.

L�sung

Laufende Nummer

Du kannst innerhalb deiner Abfrage eine Unterabfrage zum Erzeugen eines Z�hlerfeldes verwenden. Dazu gibst du in einer eigenen Spalte einen SQL-Text wie den folgenden ein:

Set db = CurrentDB()0

Das Beispiel zeigt, wie man f�r eine Tabelle "Artikel" mit einem eindeutigen Schl�sselfeld "Id" in der Abfrage ein Z�hlerfeld erzeugt. Wenn es in deiner Abfrage einschr�nkende Kriterien gibt oder Du nach anderen Kriterien sortiert hast, wird der SQL-Ausdruck etwas komplizierter:
mein deutscher KB-Artikel dazu im Webarchiv

Eine Alternative zu der L�sung mit der Unterabfrage ist, die laufende Nummer mit DCount (DomAnzahl) zu erstellen:
Set db = CurrentDB()1

Da Unterabfragen mit JET nicht gerade Boliden sind, kann diese Version trotz der oft verp�nten Dom�nenaggregatfunktion bei gro�en Datenmengen durchaus schneller sein. Also, am besten testen.

Laufende Summe

geht z.B. mithilfe der DSum-Funktion:
Laufende Summe in Abfragen
Gruppierte Laufende Summe in Abfragen

nach oben

//www.donkarl.com?FAQ3.12aktualisiert 2021-10-25

Problem

Du m�chtest eine Abfrage erstellen, um auf Basis eines Geburtsdatum-Feldes zu ermitteln, wer von den Leuten in deiner Tabelle z.B. in den n�chsten 10 Tagen Geburtstag haben wird.

L�sung

Du kannst in einer Abfrage in einer neuen Spalte die Geburtstage ins aktuelle Jahr transferieren:
Set db = CurrentDB()2

Dann schreibst du noch als Kriterium:
Set db = CurrentDB()3

Diese Variante ber�cksichtigt nat�rlich nur Geburtstage im heurigen Jahr. Falls die Sache auch �ber den Jahreswechsel funktionieren soll, kannst du noch ein zweites berechnetes Feld erzeugen:
Set db = CurrentDB()4

Bei diesem Feld das gleiche Kriterium wie oben verwenden, aber eine Zeile tiefer, also als Oder-Kriterium.

s.a. Alter ermitteln und mein deutscher KB-Artikel dazu im Webarchiv

nach oben

//www.donkarl.com?FAQ3.13

Problem

Du verwendest in einer Abfrage als Kriterium einen Ausdruck wie Set db = CurrentDB()5 und keiner der Datens�tze, die dem Kriterium entsprechen sollten, wird zur�ckgegeben.

Ursache

Die h�ufigste Ursache ist, dass im Tabellenfeld auch eine Zeit >00:00 Uhr mit gespeichert ist. Wie die Anzeige formatiert ist, ist dann egal, die Zeit wird immer mitgef�hrt. Das Problem wird meistens dadurch verursacht, dass das Feld irgendwo mit Set db = CurrentDB()6 bzw. Set db = CurrentDB()7 gef�llt wurde. Wenn man dieses Feld denn mit Set db = CurrentDB()5 vergleicht, so wird es immer mit 00:00 Uhr des aktuellen Tages verglichen.

L�sung

Verwende zum automatisierten F�llen von Datumsfeldern, die keine Zeit (bzw. 00:00) enthalten sollen, die Funktion Date statt der Funktion Now.
oder
Verwende f�r alle Datens�tze mit heutigem Datum - unabh�ngig von der Uhrzeit - in der Abfrage als Kriterium:
Set db = CurrentDB()9
oder
Verwende in der Abfrage beim Feld: strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"0
und im Kriterium entsprechend: strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"1

nach oben

//www.donkarl.com?FAQ3.14aktualisiert 2021-10-25

Problem

Du m�chtest in einer Abfrage bei einem best. Feld ein Kriterium verwenden z.B. einen Bezug auf ein Formularfeld oder einen Parameter. Normalerweise sollen die Datens�tze entsprechend diesem Wert gefiltert werden.
Wenn das Kriterium aber leer ist oder einen bestimmten Wert hat (z.B. "*" oder "Alle") dann sollen alle Datens�tze der Abfrage erscheinen.

L�sung

Du kannst als Kriterium schreiben:

f�r ein Steuerelement in einem Formular
strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"2

f�r einen leeren Parameter
strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"3

f�r den * (Stern, Asterisk) aus einem Formularfeld
strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"4

bei einem Parameter "Alle"
strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"5

usw.

Wenn der Oder-Teil in den Beispielen Wahr ist, dann werden alle Datens�tze der Abfrage zur�ckgeliefert. Andernfalls werden die Datens�tze ganz normal durch den Wert im ersten Teil des Kriterienausdrucks gefiltert.

Diese Methode funktioniert nur, wenn sie f�r wenige Spalten einer Abfrage angewandt wird. Bei mehr als ein paar Kriterien dieser Art hat Access/JET meist Probleme, die Abfrage auszuf�hren. In diesem Fall ist es besser, einen SQL-Text per Code zu basteln, der nur die Kriterien beinhaltet, die einen Wert haben. Das verbessert sowohl das SQL-Statement als auch die Performance.

mein deutscher KB-Artikel dazu im Webarchiv

nach oben

//www.donkarl.com?FAQ3.15

Problem

Du m�chtest eine globale Variable in einer Abfrage verwenden, z.B. als Kriterium oder Teil eines berechneten Feldes. Wenn du das versuchst, wird die Variable nicht erkannt sondern als String oder Parameter angesehen.

L�sung

Du kannst eine Variable nicht direkt in einer Abfrage verwenden. Erzeuge stattdessen in einem Standardmodul eine Funktion, die nichts anderes tut, als den Wert der Variablen zur�ckzugeben. z.B.

strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"6

strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"7

End Function

In der Abfrage schreibst du dann statt der Variablen den Namen der Funktion inkl. Klammern.

Auf die gleiche Weise kannst du �brigens auch den Wert einer Variablen als Steuerelementinhalt in Formularen oder Berichten verwenden.

nach oben

//www.donkarl.com?FAQ3.16

Problem

Du hast 2 Tabellen "A" und "B" und m�chtest herausfinden, welche Datens�tze sich in A befinden, aber nicht in B.

L�sung

Verkn�pfe die Tabellen im Abfrageentwurf �ber daf�r geeignete Felder (Prim�rschl�ssel und Fremdschl�ssel o.�.) und mach einen Doppelklick auf die Verkn�pfungslinie. Dort stellst du die zweite Variante an Beziehung ein, also jene, die alle DS aus Tabelle A bringt und nur die aus Tabelle B usw� Hol den Stern aus Tabelle A als Feld in die Abfrage und das Prim�rschl�sselfeld aus Tabelle B als weiteres Feld. Dieses Feld brauchst du nicht anzeigen zu lassen, aber als Kritierum schreibst du: strDaten = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "DeineDaten.mdb"9

Das SQL-Statement sieht dann ca. so aus:

For i = 0 To db.TableDefs.Count - 1
  If db.TableDefs(i).Connect <> "" Then
    If Mid(db.TableDefs(i).Connect, 11) <> strDaten Then
      db.TableDefs(i).Connect = ";database=" & strDaten
      db.TableDefs(i).RefreshLink
    End If
  End If
Next i0

nach oben

//www.donkarl.com?FAQ3.17

Problem

Du m�chtest in einer Abfrage erreichen, dass sich die Reihenfolge der Datens�tze nach Zufallsprinzip bei jedem Aufruf �ndert.

L�sung

F�r die Erzeugung von Zufallszahlen bietet VBA die Randomize-Funktion Rnd() (in Abfragen hei�t sie ZZG).
Um eine zuf�llige Sortierung zu erreichen, kannst du ein neues Feld erzeugen und darin ein Autowert-Feld verwenden, das in der zugrundeliegenden Tabelle hoffentlich existiert (falls nicht, dann erzeugen).
Der Ausdruck f�r das neue Feld lautet dann: For i = 0 To db.TableDefs.Count - 1
  If db.TableDefs(i).Connect <> "" Then
    If Mid(db.TableDefs(i).Connect, 11) <> strDaten Then
      db.TableDefs(i).Connect = ";database=" & strDaten
      db.TableDefs(i).RefreshLink
    End If
  End If
Next i1
Nach diesem Feld sortieren lassen.

nach oben

//www.donkarl.com?FAQ3.18

Problem

Du verwendest in einer Kreuztabellenabfrage einen Parameter, den du �ber ein Parameterfenster eingeben m�chtest oder z.B. einen Formularbezug als Kriterium, der bei anderen Arten von Abfragen tadellos funktioniert. Bei der Kreuztabellenabfrage aber erscheint die Fehlermeldung:
"Das Microsoft Jet-Datenbankmodul erkenntnicht als g�ltigen Feldnamen oder Ausdruck. (Fehler 3070)"

Ursache

Bei einer Kreuztabellenabfrage werden die Spaltennamen dynamisch erzeugt. Daher wei� Access nicht, ob es sich um einen Parameter oder einen Feldbezug handelt. Erst sp�ter, beim Versuch von JET, die Spalte an ein Tabellenfeld zu binden, kommt es zu dem Fehler.

L�sung

Trage den Parameter, Formularbezug etc. und den Datentyp zus�tzlich in die Parameterliste der Kreuztabellenabfrage ein. Im Abfrageentwurf Men� Abfrage/Parameter. Damit kennt Access Name und Typ des Parameters und geht von Anfang an richtig damit um.

nach oben

//www.donkarl.com?FAQ3.19aktualisiert 2005-11-27

Problem

Du m�chtest bei einem Kriterium oder bei einer Verkn�pfung unterscheiden, ob ein Buchstabe gro� oder klein geschrieben ist. Access interessiert das aber nicht.

Ursache

JET, die Standard-Datenbank-Engine von Access, unterscheidet nicht zwischen Gro�- und Kleinschreibung, ist also nicht "case-sensitive".

L�sung

Du kannst die VBA-Funktion StrComp() verwenden, um zwischen Gro�- und Kleinschreibung (�brigens auch zwischen "ss" und "�") zu unterscheiden. Angenommen, FeldX soll mit einem Kriterium "abc" verglichen werden. Als Ergebnis soll nur diese klein geschriebene Variante kommen, nicht "ABC", "Abc", "aBc" etc. Daf�r legst du ein neues Abfragefeld an, in dem folgender Ausdruck steht:

For i = 0 To db.TableDefs.Count - 1
  If db.TableDefs(i).Connect <> "" Then
    If Mid(db.TableDefs(i).Connect, 11) <> strDaten Then
      db.TableDefs(i).Connect = ";database=" & strDaten
      db.TableDefs(i).RefreshLink
    End If
  End If
Next i2

Die 0 hinten (f�r den Parameter Compare) sorgt f�r den n�tigen bin�ren Vergleich. Nur wenn die verglichenen Werte in ihrer Schreibweise exakt �bereinstimmen, liefert StrComp() den R�ckgabewert 0 (s.zu "StrComp"). Du kannst daher die Treffer rausfiltern, indem du in der Kriterienzeile des Feldes schreibst: 0
s.a. //support.microsoft.com/?kbid=209674

MIt dem gleichen Vorgehen kann man bei einer Verkn�pfung zwischen Gro�- und Kleinschreibung in den verkn�pfenden Feldern unterscheiden, indem man eben wie o.a. ein zus�tzliches Feld mit dem bin�ren StrComp-Vergleich f�r die beiden Verkn�pfungsfelder einsetzt. In SQL w�re das z.B.:

Was ist eine Union Abfrage?

Was bedeutet Gruppierung bei Access?

Welche Beziehungen gibt es bei Access?

Was ist eine Aggregatfunktion in Access?