none
Access 2003 Abfrage durchnummieren

    Frage

  • Hallo,

    ich versuche das Ergebnisse meiner Abfrage durchzunummerieren. Ich habe mich dabei an folgende Hilfe gehalten . Bei kleineren Abfragen hat es geklappt undnun wagte ich mich an komplexere Abfragen. Was mir nicht so richtig gelingt.

    Hier meine funktionierende Ausgangsabfrage:

     

    SELECT tbl_spieler.name AS Name, Count(Temp.name) AS Spiele, Count(IIf(Temp.spielart=3,[name],Null)) AS HSpiele, 
    Count(IIf(Temp.spielart=4,[name],Null)) AS ASpiele, Sum(Temp.holz) AS Gesamt,
    Sum(IIf(Temp.spielart=3,[holz],Null)) AS HGesamt, Sum(IIf(Temp.spielart=4,[holz],0)) AS AGesamt,
    Round(([Gesamt]/[Spiele]),2) AS [Holz/Spiel], IIf([HGesamt]=0,0,Round(([HGesamt]/[HSpiele]),2)) AS [Holz/HSpiel],
    IIf([AGesamt]=0,0,Round(([AGesamt]/[ASpiele]),2)) AS [Holz/ASpiel] FROM tbl_spieler INNER JOIN Temp ON tbl_spieler.id_spieler = Temp.name GROUP BY tbl_spieler.name, Temp.spielklasse HAVING (((Temp.spielklasse)=3)) AND (((Temp.spielklasse)=4)) ORDER BY Sum(Temp.holz) DESC , Sum(IIf(Temp.spielart=3,[holz],Null)) DESC , Sum(IIf(Temp.spielart=4,[holz],0)) DESC;


     

    Nun versuchte ich wie im o.g. Link eine Spalte Nr hinzufügen mit allen Bedingen der Ausgangsabfrage:

     

    Nr: (SELECT tbl_spieler.name AS Name, Count(Temp.name) AS Spiele, Count(IIf(Temp.spielart=3,[name],Null)) AS HSpiele, 
    Count(IIf(Temp.spielart=4,[name],Null)) AS ASpiele, Sum(Temp.holz) AS Gesamt,
    Sum(IIf(Temp.spielart=3,[holz],Null)) AS HGesamt, Sum(IIf(Temp.spielart=4,[holz],0)) AS AGesamt,
    Round(([Gesamt]/[Spiele]),2) AS [Holz/Spiel], IIf([HGesamt]=0,0,Round(([HGesamt]/[HSpiele]),2)) AS [Holz/HSpiel],
    IIf([AGesamt]=0,0,Round(([AGesamt]/[ASpiele]),2)) AS [Holz/ASpiel] FROM tbl_spieler INNER JOIN Temp ON tbl_spieler.id_spieler = Temp.name GROUP BY tbl_spieler.name, Temp.spielklasse HAVING (((Temp.spielklasse)=3)) AND AND ([Temp].[id_erg] < [tbl_ergebnisse].[id_erg]) ORDER BY Sum(Temp.holz) DESC , Sum(IIf(Temp.spielart=3,[holz],Null)) DESC , Sum(IIf(Temp.spielart=4,[holz],0)) DESC)


     

    Kann mir jmd helfen?

    Gruß

     


    Samstag, 31. Dezember 2011 16:47

Antworten

  • mister_sweety wrote:
    >
    > ich versuche das Ergebnisse meiner Abfrage durchzunummerieren.
    > ...
    > Hier meine funktionierende Ausgangsabfrage:
    >
    > SELECT tbl_spieler.name AS Name, Count(Temp.name) AS Spiele,
    > Count(IIf(Temp.spielart=3,[name],Null)) AS HSpiele,
    > Count(IIf(Temp.spielart=4,[name],Null)) AS ASpiele, Sum(Temp.holz) AS
    > Gesamt, Sum(IIf(Temp.spielart=3,[holz],Null)) AS HGesamt,
    > Sum(IIf(Temp.spielart=4,[holz],0)) AS AGesamt,
    > Round(([Gesamt]/[Spiele]),2) AS [Holz/Spiel],
    > IIf([HGesamt]=0,0,Round(([HGesamt]/[HSpiele]),2)) AS [Holz/HSpiel],
    > IIf([AGesamt]=0,0,Round(([AGesamt]/[ASpiele]),2)) AS [Holz/ASpiel]
    > FROM tbl_spieler INNER JOIN Temp ON tbl_spieler.id_spieler =
    > Temp.name
    > GROUP BY tbl_spieler.name, Temp.spielklasse
    > HAVING (((Temp.spielklasse)=3)) AND (((Temp.spielklasse)=4))
    > ORDER BY Sum(Temp.holz) DESC , Sum(IIf(Temp.spielart=3,[holz],Null))
    > DESC , Sum(IIf(Temp.spielart=4,[holz],0)) DESC;
     
    Ein paar Anmerkungen dazu:
     
    Du solltest unbedingt den Feldnamen und Alias "name" vermeiden,
    d.h. rasch ändern. Das ist in mehrfacher Hinsicht ein Schlüsselwort
    in Access und verursacht oft Probleme. Ebenso solltest du die
    Aliase mit dem Divisionszeichen / vermeiden.
     
    Ist dir klar, dass die Funktion Round() nicht kaufmännisch rundet?
     
    Ich vermute, dass du mit deiner HAVING-Bedingung dafür sorgen willst,
    dass nur Spielklasse 3 und 4 berücksichtigt wird. Das formulierst du
    dann besser als WHERE-Bedingung, denn HAVING liefert zwar oft das
    gleiche Ergebnis, ist aber eigentlich etwas anderes.
     
    In der Abfrage-Entwurfsansicht erreichst du das, indem du die Spalte
    "spielklasse" noch einmal einfügst und in der Funktionszeile statt
    "Gruppierung" "Bedingung" wählst. Dann löscht du die Kriterien
    in der bisherigen Spalte und fügst sie stattdessen in der neuen ein.
     
    > Nun versuchte ich wie im o.g. Link eine Spalte Nr hinzufügen mit
    > allen Bedingen der Ausgangsabfrage:
    > ...
     
    Du hast mehrere Syntaxfehler in deinem Ausdruck. Es ist zwar
    möglich, aber recht kompliziert, so etwas richtig in die selbe
    Abfrage hinein zu formulieren. Hier 2 Lösungsvarianten:
     
    1.
    Du könntest das Abfrageergebnis per Anfügeabfrage in eine
    Arbeitstabelle schreiben, die einen Autowert enthält. Den kannst
    du entweder jedesmal runtersetzen und direkt als laufende Nummer
    verwenden (s. http://www.donkarl.com?FAQ3.2), oder du machst
    eine Abfrage darauf, mit einem dann sehr einfachen Ausdruck zur
    laufenden Nummerierung. Für diesen Ablauf wäre etwas VBA
    notwendig.
     
    2.
    Wenn du es hingegen nur mit Abfragen hinbekommen willst,
    wäre eine Möglichkeit, deine erste, funktionierende Abfrage zu
    speichern und die laufende Nummer in einer zweiten Abfrage
    zu berechnen, die auf der ersten basiert.
     
    Wenn ich von deinen bisherigen Feldnamen und Aliasen ausgehe (s.o.)
    und davon, dass du die erste Abfrage als qryErgebnis1 abspeicherst,
    könnte die 2. Abfrage z.B. so aussehen:
     
    SELECT Name, Spiele, HSpiele, ASpiele, Gesamt, HGesamt,
    AGesamt, [Holz/Spiel], [Holz/ASpiel],
     
    (
    SELECT Count (*)
    FROM qryErgebnis1 As Temp
    WHERE
    Format(Temp.Gesamt,"00000.00") &
    Format(Temp.HGesamt,"00000.00") &
    Format(Temp.AGesamt,"00000.00")
    >
    Format(qryErgebnis1.Gesamt,"00000.00") &
    Format(qryErgebnis1.HGesamt,"00000.00") &
    Format(qryErgebnis1.AGesamt,"00000.00")
    )+1 AS Nr
     
    FROM qryErgebnis1
     
    Das wäre eine relativ einfache Trickserei, die davon ausgeht, dass
    deine für die Sortierung relevanten Felder max. 5 Stellen vor dem
    Komma haben. Ansonsten musst du eben die Stellen in den
    Format()-Ausdrücken erhöhen.
     
    --
    Servus
    Karl
    *********
     
     
     
    • Als Antwort markiert mister_sweety Sonntag, 1. Januar 2012 10:23
    Samstag, 31. Dezember 2011 18:26
  • Hallo Karl,

    vielen dank für Deine Antwort mit den vielen Hinweisen. Du hast mich in zweierlei Hinsicht sehr weiter gebracht. Das ist meiner Abfrage auf eine gespeicherte Abfrage mit SQL zu greifen kann war mir bis ich Deine Antwort las nicht klar. Das werde ich gleich mal ausprobieren und dann ein Feedback abgeben.

    Das Round() nicht kaufmännisch rundet war mir bevor ich die Abfrage erstellte nicht bewusst. Hinterher schon, da so ja entweder keine, 1 oder 2 Stellen nach dem Komma auftreten. Was aber nicht die allerhöchste Priorität hatte. Trotzdem ist der Hinweis von Dir sehr nützlich und hilfreich.

    Gruß und ein gesundes Jahr

     

    • Als Antwort markiert mister_sweety Sonntag, 1. Januar 2012 10:31
    Sonntag, 1. Januar 2012 10:03
  • Hallo Karl,

    hier nun mein Feedback.

    Die Abfrage:

    SELECT Name, Spiele, HSpiele, ASpiele, Gesamt, HGesamt,
    AGesamt, [Holz/Spiel], [Holz/ASpiel],
     
    (
    SELECT Count (*)
    FROM qryErgebnis1 As Temp
    WHERE
    Format(Temp.Gesamt,"00000.00") &
    Format(Temp.HGesamt,"00000.00") &
    Format(Temp.AGesamt,"00000.00")
    >
    Format(qryErgebnis1.Gesamt,"00000.00") &
    Format(qryErgebnis1.HGesamt,"00000.00") &
    Format(qryErgebnis1.AGesamt,"00000.00")
    )+1 AS Nr
     
    FROM qryErgebnis1
    


    war goldwert. Das war genau das was ich haben wollte. DANKE DANKE DANKE. Werde nun versuchen das auf meine anderen Abfragen anzuwenden.

    P.S. Die Formatfunktion hat allerdings nichts gebracht.

    • Als Antwort markiert mister_sweety Sonntag, 1. Januar 2012 10:23
    Sonntag, 1. Januar 2012 10:23

Alle Antworten

  • mister_sweety wrote:
    >
    > ich versuche das Ergebnisse meiner Abfrage durchzunummerieren.
    > ...
    > Hier meine funktionierende Ausgangsabfrage:
    >
    > SELECT tbl_spieler.name AS Name, Count(Temp.name) AS Spiele,
    > Count(IIf(Temp.spielart=3,[name],Null)) AS HSpiele,
    > Count(IIf(Temp.spielart=4,[name],Null)) AS ASpiele, Sum(Temp.holz) AS
    > Gesamt, Sum(IIf(Temp.spielart=3,[holz],Null)) AS HGesamt,
    > Sum(IIf(Temp.spielart=4,[holz],0)) AS AGesamt,
    > Round(([Gesamt]/[Spiele]),2) AS [Holz/Spiel],
    > IIf([HGesamt]=0,0,Round(([HGesamt]/[HSpiele]),2)) AS [Holz/HSpiel],
    > IIf([AGesamt]=0,0,Round(([AGesamt]/[ASpiele]),2)) AS [Holz/ASpiel]
    > FROM tbl_spieler INNER JOIN Temp ON tbl_spieler.id_spieler =
    > Temp.name
    > GROUP BY tbl_spieler.name, Temp.spielklasse
    > HAVING (((Temp.spielklasse)=3)) AND (((Temp.spielklasse)=4))
    > ORDER BY Sum(Temp.holz) DESC , Sum(IIf(Temp.spielart=3,[holz],Null))
    > DESC , Sum(IIf(Temp.spielart=4,[holz],0)) DESC;
     
    Ein paar Anmerkungen dazu:
     
    Du solltest unbedingt den Feldnamen und Alias "name" vermeiden,
    d.h. rasch ändern. Das ist in mehrfacher Hinsicht ein Schlüsselwort
    in Access und verursacht oft Probleme. Ebenso solltest du die
    Aliase mit dem Divisionszeichen / vermeiden.
     
    Ist dir klar, dass die Funktion Round() nicht kaufmännisch rundet?
     
    Ich vermute, dass du mit deiner HAVING-Bedingung dafür sorgen willst,
    dass nur Spielklasse 3 und 4 berücksichtigt wird. Das formulierst du
    dann besser als WHERE-Bedingung, denn HAVING liefert zwar oft das
    gleiche Ergebnis, ist aber eigentlich etwas anderes.
     
    In der Abfrage-Entwurfsansicht erreichst du das, indem du die Spalte
    "spielklasse" noch einmal einfügst und in der Funktionszeile statt
    "Gruppierung" "Bedingung" wählst. Dann löscht du die Kriterien
    in der bisherigen Spalte und fügst sie stattdessen in der neuen ein.
     
    > Nun versuchte ich wie im o.g. Link eine Spalte Nr hinzufügen mit
    > allen Bedingen der Ausgangsabfrage:
    > ...
     
    Du hast mehrere Syntaxfehler in deinem Ausdruck. Es ist zwar
    möglich, aber recht kompliziert, so etwas richtig in die selbe
    Abfrage hinein zu formulieren. Hier 2 Lösungsvarianten:
     
    1.
    Du könntest das Abfrageergebnis per Anfügeabfrage in eine
    Arbeitstabelle schreiben, die einen Autowert enthält. Den kannst
    du entweder jedesmal runtersetzen und direkt als laufende Nummer
    verwenden (s. http://www.donkarl.com?FAQ3.2), oder du machst
    eine Abfrage darauf, mit einem dann sehr einfachen Ausdruck zur
    laufenden Nummerierung. Für diesen Ablauf wäre etwas VBA
    notwendig.
     
    2.
    Wenn du es hingegen nur mit Abfragen hinbekommen willst,
    wäre eine Möglichkeit, deine erste, funktionierende Abfrage zu
    speichern und die laufende Nummer in einer zweiten Abfrage
    zu berechnen, die auf der ersten basiert.
     
    Wenn ich von deinen bisherigen Feldnamen und Aliasen ausgehe (s.o.)
    und davon, dass du die erste Abfrage als qryErgebnis1 abspeicherst,
    könnte die 2. Abfrage z.B. so aussehen:
     
    SELECT Name, Spiele, HSpiele, ASpiele, Gesamt, HGesamt,
    AGesamt, [Holz/Spiel], [Holz/ASpiel],
     
    (
    SELECT Count (*)
    FROM qryErgebnis1 As Temp
    WHERE
    Format(Temp.Gesamt,"00000.00") &
    Format(Temp.HGesamt,"00000.00") &
    Format(Temp.AGesamt,"00000.00")
    >
    Format(qryErgebnis1.Gesamt,"00000.00") &
    Format(qryErgebnis1.HGesamt,"00000.00") &
    Format(qryErgebnis1.AGesamt,"00000.00")
    )+1 AS Nr
     
    FROM qryErgebnis1
     
    Das wäre eine relativ einfache Trickserei, die davon ausgeht, dass
    deine für die Sortierung relevanten Felder max. 5 Stellen vor dem
    Komma haben. Ansonsten musst du eben die Stellen in den
    Format()-Ausdrücken erhöhen.
     
    --
    Servus
    Karl
    *********
     
     
     
    • Als Antwort markiert mister_sweety Sonntag, 1. Januar 2012 10:23
    Samstag, 31. Dezember 2011 18:26
  • Hallo Karl,

    vielen dank für Deine Antwort mit den vielen Hinweisen. Du hast mich in zweierlei Hinsicht sehr weiter gebracht. Das ist meiner Abfrage auf eine gespeicherte Abfrage mit SQL zu greifen kann war mir bis ich Deine Antwort las nicht klar. Das werde ich gleich mal ausprobieren und dann ein Feedback abgeben.

    Das Round() nicht kaufmännisch rundet war mir bevor ich die Abfrage erstellte nicht bewusst. Hinterher schon, da so ja entweder keine, 1 oder 2 Stellen nach dem Komma auftreten. Was aber nicht die allerhöchste Priorität hatte. Trotzdem ist der Hinweis von Dir sehr nützlich und hilfreich.

    Gruß und ein gesundes Jahr

     

    • Als Antwort markiert mister_sweety Sonntag, 1. Januar 2012 10:31
    Sonntag, 1. Januar 2012 10:03
  • Hallo Karl,

    hier nun mein Feedback.

    Die Abfrage:

    SELECT Name, Spiele, HSpiele, ASpiele, Gesamt, HGesamt,
    AGesamt, [Holz/Spiel], [Holz/ASpiel],
     
    (
    SELECT Count (*)
    FROM qryErgebnis1 As Temp
    WHERE
    Format(Temp.Gesamt,"00000.00") &
    Format(Temp.HGesamt,"00000.00") &
    Format(Temp.AGesamt,"00000.00")
    >
    Format(qryErgebnis1.Gesamt,"00000.00") &
    Format(qryErgebnis1.HGesamt,"00000.00") &
    Format(qryErgebnis1.AGesamt,"00000.00")
    )+1 AS Nr
     
    FROM qryErgebnis1
    


    war goldwert. Das war genau das was ich haben wollte. DANKE DANKE DANKE. Werde nun versuchen das auf meine anderen Abfragen anzuwenden.

    P.S. Die Formatfunktion hat allerdings nichts gebracht.

    • Als Antwort markiert mister_sweety Sonntag, 1. Januar 2012 10:23
    Sonntag, 1. Januar 2012 10:23
  • Am 01.01.2012 schrieb mister_sweety:

    war goldwert. Das war genau das was ich haben wollte. DANKE DANKE DANKE. Werde nun versuchen das auf meine anderen Abfragen anzuwenden.

    Vergiss nicht die Hinweise bezüglich Feldnamen umzusetzen, ansonsten
    fällst Du später auf die Nase. Dann hast Du vermutlich viel mehr Arbeit
    als jetzt.

    Servus
    Winfried


    Connect2WSUS: http://www.grurili.de/tools/Connect2WSUS.exe
    GPO's: http://www.gruppenrichtlinien.de
    Community Forums NNTP Bridge: http://communitybridge.codeplex.com/


    Sonntag, 1. Januar 2012 11:11
  • Hallo Winfried,

    danke für den wertvollen Hinweis. Ich bin dabei das alles sythematisch abzuarbeiten.

    Gruß

    Montag, 2. Januar 2012 08:01