none
[ACCESS 03] GROUP BY Feld1 oder nach Feld2, wenn Feld1 NULL ??

    Frage

  • Hallo Freunde,

    folgendes Szenario:

    Master-Tabelle tblEinsätze mit Spalten: EinsatzId (PK), VertragsId (FK)

    Detail-Tabelle tblVerträge mit Spalten: VertragsId (PK), Vertragskennzeichen, Bau

    PROBLEM: Vertragskennzeichen ist nur für Vertragskunden gefüllt, bei vertragslosen Einsätzen "NULL". Bau ist in jedem Fall gefüllt, um innerhalb der vertragslosen Einsätzen zu unterscheiden.

    FRAGE: Ist es möglich die l Einsätze gruppiert nach Vertragskennzeichen bzw. nach Bau, wenn Vertragskennzeichen NULL ist, per SQL-Statement abzufragen?

    Würde ich nur nach Vertragskennzeichen gruppieren, dann wären ALLE vertragslosen Einsätze zusammengefasst..

    Montag, 13. August 2012 07:05

Antworten

  • Hallo Flux1989

    Es gibt keine DAO für 64-bit, die gibt es nur 32-bittig. Für das 64-bit (und 32-bit) Umfeld gibt es die ACE, welche Abwärtskompatibel ist. Du solltest auf jeden Fall die ACE benutzen.

    Download gibt’s hier:
    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    und dann auch gleich noch die SPs installieren, falls diese bei diesem Link nicht schon drauf sind.

    Daher bin ich verwundert, dass es ohne YEAR() funktioniert. Da scheint noch was anderes zu laufen. Entweder läuft das MBP unter WOA64 (sonst würde es ohne Year() auch nicht laufen) oder es steckt da eine weitere Schicht dazwischen, bevor Du auf die MDB zugreifst, die Dir die Meldung auswirft, weil diese das SQL Statement parst und die YEAR Funktion nicht kennt. Jet kennt diese (sowohl DAO als auch ACE)

    Du kannst auch mal versuchen in Order By und Group By nicht YEAR(startdate) , sondern die Ordinalzahl (beginnend mit 1, darum für für das zweite Feld 2) des Feldes zu verwenden (geht auch mit DAO):

    SELECT COUNT(id) AS Anzahl, YEAR(startdate) AS Jahr
    FROM tblTest
    GROUP BY 2
    ORDER BY 2

    Ja und noch was: Da Du evt ein Sprachproblem mit Deutschen Versionen von irgendwelchen Tools haben könntest, solltest Du nicht "AS Jahr" sondern z.B. "AS Kalenderjahr" verwenden. Das könnte auch der Auslöser für den Fehler sein, falls Jahr da von einem Tool nach YEAR übersetzt und dann fehlerhaft interpretiert werden könnte. Zumindest solltest Du [] um den Alias schreiben, also [Jahr], damit das auch wirklich als Feldname erkannt wird.

    Gruss
    Henry

    • Als Antwort markiert Flux1989 Dienstag, 4. September 2012 21:34
    Montag, 3. September 2012 09:56

Alle Antworten

  • Ideen dazu:

    1. nach Bau gruppieren geht nicht, da mehrere Vertragskennzeichen pro Bau
    2. stattdessen nach VertragsId gruppieren (identifiziert Kombination Vertragskennzeichen und Bau eindeutig)
    3. für vertragslose Einsätze ein Dummy-Vertragskennzeichen aus 99999+Bau eintragen und nach Vertragskennzeichen gruppieren
    4. Einmal nach Vertragskennzeichen gruppieren, dann - in einer zweiten Abfrage - alle vertragslosen nach Bau und diese Ergebnismengen per UNION-Befehl aneinander hängen

    Ich favorisiere 2., da am "saubersten".

    Was meint ihr dazu?


    • Bearbeitet Flux1989 Montag, 13. August 2012 08:23
    Montag, 13. August 2012 08:23
  • Flux1989 wrote:
    >
    > Master-Tabelle tblEinsätze mit Spalten: EinsatzId (PK), VertragsId
    > (FK)
    >
    > Detail-Tabelle tblVerträge mit Spalten: VertragsId (PK),
    > Vertragskennzeichen, Bau
    >
    > PROBLEM: Vertragskennzeichen ist nur für Vertragskunden gefällt,
    > bei vertragslosen Einsätzen "NULL". Bau ist in jedem Fall gefällt,
    > um innerhalb der vertragslosen Einsätzen zu unterscheiden.
    >
    > FRAGE: Ist es möglich die l Einsätze gruppiert nach
    > Vertragskennzeichen bzw. nach Bau, wenn Vertragskennzeichen NULL ist,
    > per SQL-Statement abzufragen?
    >
    > Würde ich nur nach Vertragskennzeichen gruppieren, dann wären ALLE
    > vertragslosen Einsätze zusammengefasst..
     
    Wenn ich richtig verstehe, hülfe dir ein Wenn, d.h. füge ein berechnetes
    Feld in der Abfrage hinzu:
     
    Wenn(IstNull([Vertragskennzeichen]);[Bau])
     
    und gruppiere sowohl nach Vertragskennzeichen als auch nach
    diesem berechneten Feld.
     
    --
    Servus
    Karl
    *********
    Access-FAQ: http://www.donkarl.com  +  AEK15
    Access-Entwickler-Konferenz, 29/30.9. Nürnberg, 13/14.10. Hannover
     
     
     
    Montag, 13. August 2012 10:49
  • hallo karl,

    danke für deine antwort.

    Wie würde denn das gesamte SQL-Statement aussehen? Ich glaube kaum, dass ich da "Wenn..." reinschreiben kann.. ?

    Mittwoch, 15. August 2012 08:29
  • Flux1989 wrote:
    > Wie würde denn das gesamte SQL-Statement aussehen?
    > Ich glaube kaum, dass ich da "Wenn..." reinschreiben kann.. ?
     
    Für das gesamte Statement hättest du zuerst dein bisheriges
    verraten sollen. Daher habe ich nur das berechnete Feld
    für die Abfrage-Entwurfsansicht in Access geschildert.
     
    Im SQL-Statement würde es so aussehen:
     
    SELECT ..., iif(IsNull([Vertragskennzeichen]), [Bau])
    FROM ...
    GROUP BY ..., iif(IsNull([Vertragskennzeichen]), [Bau])
     --
    Servus
    Karl
    *********
    Access-FAQ: http://www.donkarl.com  +  AEK15
    Access-Entwickler-Konferenz, 29/30.9. Nürnberg, 13/14.10. Hannover
     
     
    Mittwoch, 15. August 2012 09:04
  • Hi Karl,

    vielen Dank fuer deine Antwort, das funktioniert super. Vielleicht kannst du mir bei einer weiteren Frage zu Access 03 weiterhelfen:

    Ich habe eine Tabelle T mit Spalten autoid, startdatum, enddatum.Wenn ich nun sage

    select * from T where startdatum between x and y 

    //x und y sind korrekt formattierte Datumswerte

    ist die Ergebnismenge leer.

    Komischerweise klappts bei

    select * from T where startdatum > x

    aber nicht bei

    select * from T where startdatum > x and startdatum < y 

    oder auch

    select * from T where startdatum > x and enddatum < y 

    Unterliege ich einem Denkfehler??

    Samstag, 25. August 2012 22:57
  • Ok, man muss das Datum in 'Hochkommata' setzen und ohne Uhrzeit eingeben, also: '8/26/2012'

    Naechste Frage:

    Wie kann man Records nach Geschaeftsjahr gruppieren? Hat jemand ne Idee?


    • Bearbeitet Flux1989 Sonntag, 26. August 2012 12:15
    Sonntag, 26. August 2012 12:14
  • Antwort:

    select count(*)
    
    from myTable
    
    group by year(dateadd("m", +8, [myDateCol]))
    
    

    Diese Funktion addiert einen Offset von 8 Monaten auf myDateCol, sodass aus 1. Jan --> 1. Sep wird und das Geschaeftsjahr bis 31. Aug geht.

    Montag, 27. August 2012 07:37
  • Hallo Karl, o. g. Funktion (" iif(IsNull(ServiceKennzeichen), Bau)  ") funktioniert bei naeherem Hinsehen doch nicht so wie gewuenscht. Zwar liefert sie die Werte von Bau, wenn ServiceKennzeichen NULL ist, andersherum liefert sie aber nicht Servicekennzeichen, wenn diese Spalte nicht NULL ist, sondern NULL.

    ServiceKennzeichen | Bau | Berechnete Spalte (iif(...))

    NULL | BauABC | BauABC

    12345| BauABC | NULL //anstatt 12345 ??

    Wie lautet die korrekte Funktion ?


    EDIT: Ok die Antwort lautet: iif(IsNull(SK), Bau, SK), also if(IsNULL(SK) return Bau; else return SK;
    • Bearbeitet Flux1989 Freitag, 31. August 2012 23:26
    Freitag, 31. August 2012 23:23
  • Flux1989 wrote:
    > ...
    > Ok die Antwort lautet: iif(IsNull(SK), Bau, SK), also
    > if(IsNULL(SK) return Bau; else return SK;
     
    Wenn du das haben willst, gibt's auch eine einfachere Variante:
     
    nz(SK, Bau)
     
    --
    Servus
    Karl
    *********
    Access-FAQ: http://www.donkarl.com  +  AEK15
    Access-Entwickler-Konferenz, 29/30.9. Nürnberg, 13/14.10. Hannover
     
     
    Samstag, 1. September 2012 08:49
  • Hey Karl,

    danke fuer deine Antwort. Leider habe grad ein richtiegs Problem, denn im Ggs. zu Access (Queries) will weder der Server Explorer in VS 2005, noch OleDbDataAdapter in meinem C# Programm bestimmte Access-Funktionen ausfuehren:

    • nz(col1, col2) geht nicht: Fehlermeldung: "Undefined function 'nz' in expression"
    • year(datecol1) geht ebenfalls nicht: Fehlermeldung: "Undefined function '[YEAR]' in expression"
    Was ist los?
    Sonntag, 2. September 2012 19:26
  • Hallo Flux1989

    Von "extern" kannst Du keine VBA/Access Funktionen aufrufen, nur die, die in der Jet Engine direkt implementiert sind. Nz() ist eine Funktion in VBA/Access und gibt es in Jet ncht, daher bin ich hier nicht überrascht. Versende statt dessen: IIf(IsNull(col1, col2, col1)).
    Bei Year() allerdings überrascht mich das. Das sollte auch in Jet implementiert sein.
    Zeig' doch mal Dein SQL Statement her, welches Du aus C# über den OleDbDataAdapter ausführen willst.

    Grus
    Henry

    On 03.09.2012 02:26, Flux1989 wrote:

    Hey Karl,

    danke fuer deine Antwort. Leider habe grad ein richtiegs Problem, denn
    im Ggs. zu Access (Queries) will weder der Server Explorer in VS 2005,
    noch OleDbDataAdapter in meinem C# Programm bestimmte Access-Funktionen
    ausfuehren:

    * nz(col1, col2) geht nicht: Fehlermeldung: "Undefined function 'nz' in
       expression"
    * year(datecol1) geht ebenfalls nicht: Fehlermeldung: "Undefined
       function '[YEAR]' in expression"Was ist los?

    Montag, 3. September 2012 01:34
  • Hallo Henry,

    also das Statement sieht aus wie folgt:

    SELECT COUNT(id) AS Anzahl, YEAR(startdate) AS Jahr
    FROM tblTest
    GROUP BY YEAR(startdate)
    ORDER BY YEAR(startdate) DESC
    In Access oder auf nen SQL-Server angewendet kein Problem, aber aus meinem Code heraus --> Unknown function ??

    Montag, 3. September 2012 07:07
  • Hallo Flux1989

    Der Fehler muss woanders liegen. Ich habe es soeben ausprobier (mit einem VBScript das ich auf Win7 in der SYSWOW64 mit WSCRIPT ausgeführt habe) und kann die Funktion Year() problemlos aufrufen.

    Wenn Du das aus C# aus machst, musst Du eine Connection öffnen. Zeig' mal den Connection String her.

    Ich vermute, die Connection ist falsch und da wird keine DAO Connection geöffnet, sondern sonst was, was nicht passt.

    Kannst Du z.B.

    SELECT COUNT(id) AS Anzahl, 2012 AS Jahr
    FROM tblText

    ohne Fehler ausführen oder erhälst Du da auch einen Fehler?

    Nachfolgend das Script. Starten musst Du das über:

    %SystemRoot%\SysWOW64\WScript.exe "Pfad und Name des Scripts.vbs"

    Option Explicit
    Dim objDAO
    Dim db
    Dim rs
    Dim strSQL
    Dim strMsg
    Dim I
    Const conDBName = "C:\Temp\test.mdb"
    On Error Resume Next
    Set objDAO = CreateObject("DAO.DBEngine.36")
    if err then
      msgbox err.description
    end if
    Set db = objDAO.OpenDatabase(conDBName, False, False)
    if Err Then
      msgbox err.description
      MsgBox "MDB " & conDBName & " nicht gefunden."
    End If
    db.Execute "DROP TABLE tblTest", 128
    If Err Then
      Err.Clear
    End If
    db.Execute "CREATE TABLE tblTest (ID COUNTER CONSTRAINT tblTest_PK PRIMARY KEY , startdate DATETIME)", 128
    If Err Then
      MsgBox Err.Description
      Err.Clear
    End If
    For I = 1 to 100
      strSQL = "INSERT INTO tblTest(startdate) VALUES( " & (I * 100) & ")"
      db.execute strSQL, 128
      If Err Then
        MsgBox Err.Description
        Err.Clear
      End If
    next
    strSQL = "SELECT COUNT(id) AS Anzahl, YEAR(startdate) AS Jahr FROM tblTest GROUP BY YEAR(startdate) ORDER BY YEAR(startdate) DESC"
    set rs = db.openrecordset(strSQL)
    if err then
      MsgBox err.description
      err.clear
    end if
    while not rs.eof
      msgbox rs(0) & ": " & rs(1)
      rs.movenext
    wend
    rs.close
    set rs = nothing
    db.Close
    Set db = Nothing
    Set daoObject = Nothing

    Dieses Script greift mit DAO Methoden auf die MDB und die Tabelle darin zu. Dabei wird JET Dein SQL Statement übergeben und ausgeführt und es tut wie erwartet.

    Im Ordner C:\Temp musst Du eine MDB mit dem Namen test.mdb anlegen.

    Es könnte es allenfalls sein, dass Du DAO aus dem 64-bit environment heraus aufrufst? Das geht nicht. Falls Du aus 64-bit Befehle an die Jet Engine schicken willst, musst Du die ACE bemühen.

    Das daoObject muss dann wie folgt angelegt werden:

    Set objDao = CreateObject("DAO.DBEngine.120")

    Das läuft dann auch im 64-bit Windows 7 Native Environment. bedingt allerdings, dass die ACE 64-bit Version installiert ist (gibt's bei MS kostenfrei als Re-Distributable)

    Gruss

    Henry


    Montag, 3. September 2012 08:41
  • Hi Henry,

    danke für deine Mühe!

    SELECT COUNT(id) AS Anzahl, 2012 AS Jahr
    FROM tblText

    funktioniert.

    Um nochmal einige Punkte klarzustellen, die ich vielleicht nicht deutlich genug erklärt habe:

    Ich baue einer SQL-Statementbuilder-Funktion mit GUI (comboboxen, textboxen, etc.). Es funktioniert alles wunderbar, aber sobald ich nach Jahr gruppieren will (--> zum SQL-Statement wird "GROUP BY YEAR(startdate)" hinzugefügt) kommt o.g. exception.

    Im Connection String steht: "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\..."

    Ich verwende also die JET-Engine, denke das wolltest du wissen?

    Außerdem: Ich verwende ein MBP mit WIN 7 64 unter bootcamp. Das heißt also, ich muss die ACE-Engine nehmen? Dazu füge ich einen Verweis auf welchen namespace/dll hinzu und wie lautet dann der connectionstring?

    Viele Fragen :)


    • Bearbeitet Flux1989 Montag, 3. September 2012 09:35
    Montag, 3. September 2012 09:31
  • Hallo Flux1989

    Es gibt keine DAO für 64-bit, die gibt es nur 32-bittig. Für das 64-bit (und 32-bit) Umfeld gibt es die ACE, welche Abwärtskompatibel ist. Du solltest auf jeden Fall die ACE benutzen.

    Download gibt’s hier:
    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    und dann auch gleich noch die SPs installieren, falls diese bei diesem Link nicht schon drauf sind.

    Daher bin ich verwundert, dass es ohne YEAR() funktioniert. Da scheint noch was anderes zu laufen. Entweder läuft das MBP unter WOA64 (sonst würde es ohne Year() auch nicht laufen) oder es steckt da eine weitere Schicht dazwischen, bevor Du auf die MDB zugreifst, die Dir die Meldung auswirft, weil diese das SQL Statement parst und die YEAR Funktion nicht kennt. Jet kennt diese (sowohl DAO als auch ACE)

    Du kannst auch mal versuchen in Order By und Group By nicht YEAR(startdate) , sondern die Ordinalzahl (beginnend mit 1, darum für für das zweite Feld 2) des Feldes zu verwenden (geht auch mit DAO):

    SELECT COUNT(id) AS Anzahl, YEAR(startdate) AS Jahr
    FROM tblTest
    GROUP BY 2
    ORDER BY 2

    Ja und noch was: Da Du evt ein Sprachproblem mit Deutschen Versionen von irgendwelchen Tools haben könntest, solltest Du nicht "AS Jahr" sondern z.B. "AS Kalenderjahr" verwenden. Das könnte auch der Auslöser für den Fehler sein, falls Jahr da von einem Tool nach YEAR übersetzt und dann fehlerhaft interpretiert werden könnte. Zumindest solltest Du [] um den Alias schreiben, also [Jahr], damit das auch wirklich als Feldname erkannt wird.

    Gruss
    Henry

    • Als Antwort markiert Flux1989 Dienstag, 4. September 2012 21:34
    Montag, 3. September 2012 09:56
  • Hallo Henry,

    Es hat funktioniert!

    Leider befuerchte ich nun, dass Anwender meiner Applikation ebenfalls ACE installieren muessen, da diese ja im ConnectionString steht. Oder meint...

    Für das 64-bit (und 32-bit) Umfeld gibt es die ACE, welche Abwärtskompatibel ist. Du solltest auf jeden Fall die ACE benutzen.

    ...dass trotzdem DAO geht?

    Was ist WOA64?

    Aliase in eckige Klammern setzen kann auch nicht schaden.

    Danke!

    Dienstag, 4. September 2012 21:43
  • WOA64 sollte WOW64 heissen, da habe ich mich vertippt. WOW64 (kannst Du er-googeln) ist eine Abkürzung für "Windows on Windows 64". Das ist die Umgebung, die den 32-bit Anwendungen zur Verfügung gestellt wird, wenn diese in einer Windows 64-bit Umgebung gestartet werden. Die Anwendung merkt davon nichts, sie wird einfach im 32-bit Umfeld gestartet.

    Nachteil ist der das zwischen der Windows 64-bit native Welt und der Windows 32-bit WOW64 Welt der Datenaustausch nur bedingt möglich ist und dass die beiden Welten nicht auf die gleichen DLLs der anderen Welt zugreifen können.

    Wenn nun also Deine Anwendung mit 64-bit läuft, dann kann diese keine DLLs aus der 32-bit Welt benutzen, sondern muss die entsprechenden 64-bit DLLs benutzen. DAO gibt es nur in der 32-bit Ausführung, die ACE gibt es sowohl in der 32-bit als auch in der 64-bit Ausführung. Wenn Du von der 64-bit Anwendung eine Access Datenbank bearbeiten willst musst Du immer die 64-bit ACE benutzen. Die 64-bit Anwendung kann keine DAO oder 32-bit ACE ansprechen.

    Nun hängt es an Dir und Deiner Entwicklungsumgebung, ob Du auf der Zielumgebung die ACE 64-bit installieren musst oder nicht und ob Du die ACE überhaupt brauchst. Vernünftige Entwicklungsumgebungen erlauben 32 als ach 64 bit Kompilate zu erzeugen. Wenn Deine Entwicklungsumgebung dieses bietet, dann wird das 32-bit Kompilat mit der in allen Windows Systemen vorhandenen DAO Libraries (da mit dem Betriebssystem ausgeliefert) funktionieren. Falls Du aber eine 64-bit Anwendung an Deine Kunden weiter gibst, dann müssen die Kunden zum einen ein 64-bit Windows 7 fahren (was nicht überall der Fall ist) und zum anderen muss auf diesen Windows Systemen die 64-bit ACE installiert sein, da diese nicht mit dem Betriebssystem installiert wird.

    Wenn Du also die Wahl hast, dann solltest Du vorzugsweise für die 32-bit Umgebung entwickeln. Dann ersparst Du Dir Probleme, wenn ein Kunde kein 64-bit Windows installiert hat und zudem ersparst Du dem Kunden die ACE 64-bit installieren zu müssen. Du bist damit dann auch rückwärts kompatibel zu älteren Betriebssystemen bis Windows XP, welche üblicherweise nur in der 32-bit Version installiert wurden.

    HTH

    Henry



    Mittwoch, 5. September 2012 03:42
  • Wenn Du von der 64-bit Anwendung eine Access Datenbank bearbeiten willst musst Du immer die 64-bit ACE benutzen. Die 64-bit Anwendung kann keine DAO oder 32-bit ACE ansprechen.

    Wenn Deine Entwicklungsumgebung dieses bietet, dann wird das 32-bit Kompilat mit der in allen Windows Systemen vorhandenen DAO Libraries (da mit dem Betriebssystem ausgeliefert) funktionieren.

    Du bist damit dann auch rückwärts kompatibel zu älteren Betriebssystemen bis Windows XP

    Hallo,

    danke für diese detaillierte Erklärung. Ein paar Fragen dazu dürfen natürlich nicht ausbleiben ;)

    Also:

    Ich benutze VS 2005, die Zielplattform ist x86 und damit 32 bit. Mein OS ist Win 7 64 bit. Der ACE 64 bit-installer wollte nicht, da meine Office (und damit Access 03)-Version 32-bittiger Natur sei, also habe ich ACE 32 bit installiert, was wunderbar funktioniert.

    Du sagst:

    Wenn Deine Entwicklungsumgebung dieses bietet, 
    dann wird das 32-bit Kompilat mit der in allen 
    Windows Systemen vorhandenen DAO Libraries 
    (da mit dem Betriebssystem ausgeliefert) 
    funktionieren. 

    Aber die Frage bleibt:

    Im moment steht ACE im Connection-String, da ich sonst auf meinem Rechner die YEAR()-Funktion nicht nutzen kann. Da ich den ConnectionString in die app.config ausgelagert habe, kann man de manuell einmalig auf dem Zielsystem (Win XP 32) in "...JET.4.0. ..." umändern. Frage mich ob das reibungslos funktioniert.

    Hilft wohl nur Testen.

    Mittwoch, 5. September 2012 07:48
  • Das wird nur funktionieren, wenn auf dem Zielsystem die ACE 32 installiert ist.

    Es ist mir nach wie vor ein Rätsel, wieso die kompilierte Version mit der DAO Library keine YEAR() Funktion kennen will. Das ist falsch.

    Es könnte allenfalls an der Entwicklungsumgebung liegen. Hast Du schon mal versucht die DAO Library zu benutzen wenn Du das 32-bit Programm startest oder hast Du das bisher nur aus der Entwicklungsumgebung heraus im Debug Mode getestet.

    Es muss mit DAO auch eine YEAR Funktion geben, sonst läuft deine DAO nicht richtig. Evt. mal die DAO neu installieren / registrieren.

    Gruss

    Henry

    Mittwoch, 5. September 2012 11:39