umgekehrtes Abfrageergebnis

[pit1303]

Hallo !
Ich erstelle mir eine Poolrangliste.
Die Rangliste wird mit einer Hin- und Rückrunde gespielt.
Eine Abfrage über bereits gespielte Partien hab ich mir schon erstellt. Auch eine Prüfung, die Partie bereits gespielt wurde habe ich mir schon erstellt.
Mein Problem nun ist, daß ich gerne mir eine Abfrage erstellen möchte, welche mir noch nicht gespielte Partien anzeigt.

zum Aufbau der DB:
Tabelle Spieler
Tabelle Begenungen

Erklärung der Abfrage:
Begegnung.ID= Autowert (Primärschlüssel)
Begegnung.Dat=Spieldatum
Begegnung.Partie=fortlaufende Zahl
Begegnung..Runde=Hin- oder Rückrunde
Begegnung.ID_Spieler1 = Fremdschlüssel Spieler 1
Begegnung.ID_Spieler2 = Fremdschlüssel Spieler 2
Begegnung.HB_SP1= Highest Break Spieler1
Begegnung.HB_SP2= Highest Break Spieler 2
Begegnung.Erfassung = Erfassungsdatum
CB_Runde= Wert aus einer Combobox für Hin oder Rückrunde (1 oder2)
CB_Spieler= Wert aus einer Combobox (FremdID) Spielertabelle.

Nachfolgend mal der Code für die Prüfung der Doppelerfassung:

Code [Auswählen] Erweitern

Dim sqlstr As String
Dim myrs As Recordset
Dim myrs2 As Recordset
Dim myquery As QueryDef

    sqlstr = ""
    sqlstr = "SELECT Begegnung.ID, Begegnung.Dat, Begegnung.Partie, Begegnung.Runde, Begegnung.ID_Spieler1, Begegnung.ID_Spieler2, Begegnung.HB_SP1, Begegnung.HB_SP2, Begegnung.Erfassung "
    sqlstr = sqlstr + "FROM Begegnung "
    sqlstr = sqlstr + "WHERE (((Begegnung.Runde)=" + CB_Runde + ") AND ((Begegnung.ID_Spieler1)=" + CB_Spieler1 + ") AND ((Begegnung.ID_Spieler2)=" + CB_Spieler2 + "));"
    Set myquery = db.CreateQueryDef("", sqlstr)
    Set myrs = myquery.OpenRecordset(dbOpenDynaset)
   
    sqlstr = ""
    sqlstr = "SELECT Begegnung.ID, Begegnung.Dat, Begegnung.Partie, Begegnung.Runde, Begegnung.ID_Spieler1, Begegnung.ID_Spieler2, Begegnung.HB_SP1, Begegnung.HB_SP2, Begegnung.Erfassung "
    sqlstr = sqlstr + "FROM Begegnung "
    sqlstr = sqlstr + "WHERE (((Begegnung.Runde)=" + CB_Runde + ") AND ((Begegnung.ID_Spieler2)=" + CB_Spieler2 + ") AND ((Begegnung.ID_Spieler1)=" + CB_Spieler1 + "));"
    Set myquery = db.CreateQueryDef("", sqlstr)
    Set myrs2 = myquery.OpenRecordset(dbOpenDynaset)
   
    If myrs.RecordCount >= 1 Or myrs2.RecordCount >= 1 Then
        MsgBox "Dieses Begegnung wurde bereits gespielt !!!" + vbNewLine + "Bitte geben Sie weitere Begegnungen ein"
Achtung !
Ich möchte eine Abfrage die SÄMTLICHE Spieler beinhaltet. Nicht nur wie im o.g. Beispiel angegeben eine Partie.

Als Ergebnis wäre schon "Spieler A vs. Spieler C Hinrunde noch offen" oder "Spieler B vs. Spieler D Rückrunde noch offen"

[andyfau]

Hallo,

schau Dir mal das Thema Join Left und Join Right bei SQL an.
Oder, wenn Du die Abfrage mit dem Abfragegenerator von Access erstellst, kannst Du die Verknüpfungseigenschaften "alle von links" oder "alle von rechts" zusammen klicken.

[pit1303]

Hey...danke für die schnelle Rückmeldung.

ok...den "join" sagt mir was, aber wie krieg ich das gebacken mit jeden Spieler
Idee wäre dann die Spieler einzeln in der Tabelle durchgehen und dann jedes mal ne Abfrage darüber laufen lassen.

Im VBA wäre das ja dann die "For each" anweisung, oder?

[Knobbi38]

Hallo Pit,

wie definierst du denn eine "nicht gespielte Partie"?

Knobbi38

[pit1303]

Hallo Pit,

wie definierst du denn eine "nicht gespielte Partie"?

Knobbi38

Definition:
Das sind Partien, die nicht in der Tabelle Begegnungen vorkommen/fehlen.

Bsp:
Insgesamt 3 Spieler.

Spieler 1 gegen Spieler2 - gespielt (Ergebnis vorhanden)
Spieler 2 gegen Spieler3 - gespielt (Ergebnis vorhanden)

Es fehlt somit:
Spieler 1 gegen Spieler 3 - nicht gespielt (kein Ergebnis vorhanden/Eintrag ein der Begegnungstabelle))

[Knobbi38]

Hallo Pit,

du kannst doch nicht nach etwas suchen, zu dem du keine Daten erfaßt hast. 

Die Spielpaarungen sind doch irgendwo festgelegt und man könnte auch an dem noch ausstehenden Ergebnis bzw. an dem Spieldatum in der Zukunft feststellen, daß ein Spiel noch nicht stattgefunden hat. Hier solltest du nochmal über dein Datenmodell nachdenken.

Knobbi38
 

[pit1303]

Ein Ansatz wäre aber:
Ich gehe die Spielertabelle einzeln durch und generiere mir ne Abfrage, ob die anderen Partien stattgefunden haben.
Falls ich kein Ergbnis zurückbekomme von der Abfrage fehlt logischerweise die Partie.
In dem o.g. Beispielt wäre das dann so:
Abfrage:
Spieler 1 gegen Spieler 2
Spieler 1 gegen Spieler 3
Spieler 2 gegen Spieler 1
Spieler 2 gegen Spieler 3
Spieler 3 gegen Spieler 1
Spieler 3 gegen Spieler 2

Das Ergebnis müsste somit sein:
Spieler 1 gegen Spieler 2 - vorhanden
Spieler 1 gegen Spieler 3 - nicht vorhanden
Spieler 2 gegen Spieler 1 - nicht vorhanden (wäre Rückrunde)
Spieler 2 gegen Spieler 3 - vorhanden
Spieler 3 gegen Spieler 1 - nicht vorhanden (wäre Rückrunde)
Spieler 3 gegen Spieler 2 - nicht vorhanden (Wäre Rückrunde)

Nur....wie krieg ich die Logik in VBA gebacken ??
Im VBA könnte ich nach der SpielerNummer abfragen, wenn diese kleiner ist, dann ist es automatisch Rückrunde.
Folglich bleibt ja nur das eine Abfrageergebnis übrig.

Man könnte natürllich auch die Partien (Spielplan) im Vorfeld "generieren" und dann einfach nach Dateninhalt abfragen.
Das verlagert aber nur die Programmierung und es treten dann andere Probleme auf (z.B. neuer Mitspieler). Dachte es gibt vielleicht ne Möglichkeit das genau umgedreht zu machen...deshalb auch der Topicname. ;-)

[Normalo0815]

Hallo pit1303,
folgende Abfrage mit 2x der Spielertabelle gibt alle möglichen Spielpartien (Hin- und Rückrunden) aus (Ist nur ein Beispiel, Tabellennamen und Feldnamen ev. noch anpassen auf eigene Bedürfnisse):

Code [Auswählen] Erweitern

SELECT
  TS1.Spieler,
  TS2.Spieler
FROM
  tbl_Spieler AS TS1,
  tbl_Spieler AS TS2
WHERE
  TS1.Spieler <> TS2.Spieler
Die Abfrage kann dann mit der Tabelle Begegnungen verknüpft werden (Inkonsistenzabfrage), wo dann die fehlenden Partien ausgegeben werden.

Siehe auch:
https://www.donkarl.com?FAQ3.16
https://support.microsoft.com/de-de/topic/vergleichen-zweier-tabellen-und-suchen-von-inkonsistenten-datens%C3%A4tzen-cb20ad48-4eba-402a-b20d-eaf10a5d1cb4

[Knobbi38]

Hallo Pit,

Man könnte natürllich auch die Partien (Spielplan) im Vorfeld "generieren" und dann einfach nach Dateninhalt abfragen. Das verlagert aber nur die Programmierung und es treten dann andere Probleme auf (z.B. neuer Mitspieler). Dachte es gibt vielleicht ne Möglichkeit das genau umgedreht zu machen...deshalb auch der Topicname. ;-)

Natürlich kann man sich eine Logik ausdenken, mit der man so etwas machen könnte, aber das ist grundsätzlich ein falsche Datenmodell und führt dann später zu weiteren Schwierigkeiten, bis man dann für solche Probleme keine Lösungen mehr findet. Dann muss man wieder von vorne anfangen. Also besser gleich ein passendes Modell erstellen.

Bei 3 Spielern mag das ja noch vertretbar sein, aber probiere mal aus, wieviele Spielerpaarungen es schon mit z.B. 5 Spielern wäre. Und so eine Abfrage müsste dann jedes mal neu durchlaufen werden. Das wird nicht gerade performant.

Wieso mit einem anderen Modell ein Problem bei neuen Mitspieler eintreten soll, erschließt sich mir so noch nicht.

Knobbi38

[pit1303]

ok...performant ist anders ...wohl war.

Ich hab das jetzt wie folgt gelöst:

Code [Auswählen] Erweitern

Dim sqlstr As String
Dim tab_Spieler As Recordset
Dim myrs As Recordset
Dim myrs2 As Recordset
Dim myrs3 As Recordset
Dim myquery As QueryDef
Dim Spieler As String
Dim Anz_Spiele As Long
Dim Anzspieler As Long
Dim offene_Spiele As Recordset



Set db = CurrentDb
Set offene_Spiele = db.OpenRecordset("Begegnung_offen")

'festellen der Anzahl der aktiven Spieler
    sqlstr = ""
    sqlstr = "SELECT Spieler.ID, Spieler.spielernr, Spieler.Eintritt, Spieler.Nn, Spieler.Vn, Spieler.strasse, Spieler.PLZ, Spieler.Ort, Spieler.Tel, Spieler.mobil, Spieler.Mail, Spieler.bemerk, Spieler.aktiv "
    sqlstr = sqlstr + "FROM Spieler "
    sqlstr = sqlstr + "WHERE (((Spieler.aktiv)=True));"
    Set myquery = db.CreateQueryDef("", sqlstr)
    Set myrs = myquery.OpenRecordset(dbOpenDynaset)
    Set myrs3 = myquery.OpenRecordset(dbOpenDynaset)
    Anzspieler = 0
   
    myrs.MoveFirst
   
    Do Until myrs.EOF
        Anzspieler = Anzspieler + 1
        myrs.MoveNext
    Loop
   
    myrs.MoveFirst
   
    Do Until myrs.EOF
            Do Until myrs.EOF
                If myrs3.EOF Then Exit Do
                If myrs!ID = myrs3!ID Then
                    myrs3.MoveNext
                Else
                    'Abfrage welche Spiele noch für welchen Spieler offen sind
                    sqlstr = ""
                    sqlstr = "SELECT Begegnung.ID,Begegnung.Dat, Begegnung.Partie, Begegnung.Runde, Begegnung.ID_Spieler1, Begegnung.ID_Spieler2, Begegnung.RL_SP1, Begegnung.RL_SP2, Begegnung.HB_SP1, Begegnung.HB_SP2, Begegnung.Erfassung "
                    sqlstr = sqlstr + "FROM Begegnung "
                    sqlstr = sqlstr + "WHERE (((Begegnung.Runde) = 1)) AND ((Begegnung.ID_Spieler1) = " & myrs!ID & ") AND ((Begegnung.ID_Spieler2) = " & myrs3!ID & ");"
                    Set myquery = db.CreateQueryDef("", sqlstr)
                    Set myrs2 = myquery.OpenRecordset(dbOpenDynaset)
                   
                    If myrs2.RecordCount = 0 Then
                        offene_Spiele.AddNew
                            offene_Spiele!ID_Spieler1 = myrs!ID
                            offene_Spiele!ID_Spieler2 = myrs3!ID
                            offene_Spiele!Runde = "1"
                        offene_Spiele.Update
                    End If
                    If myrs3.EOF = False Then
                        myrs3.MoveNext
                    Else
                        Exit Do
                    End If
                End If
            Loop
            myrs.MoveNext
            myrs3.MoveFirst
     Loop
Die klassische Bubblemethode.
Ich frage mit der Spieler nummer und einem 2. Recordset die Begenungen ab.
Sollte ich nix zurückbekommen, dann frag ich die nachste Spielernummer im 2.Set ab (myrs3). Die Speicherung erfolgt logischerweise wenn nix zurückkommt in einer temporäeren Tabelle.
Sind die Spieler durchprobiert, dann gehe ich einfach in der Spielertabelle eins weiter und fange wieder von vorne an.

Sicherlich nicht die beste Lösung, aber sie funktioniert.

Ja. das geht sehr schnell nach oben ( (Anzahl Spieler*(Anzahl Spieler-1))/2 ) und die Verarbeitungszeit wird länger. Ich denke aber bis 20 Spieler (20*19=380/2=190) ist das alles vertretbar.

Möchte mich aber trotzdem für eure Bemühungen und Hilfe bedanken.

Gruß
Pit

P.S.: Vielleicht sind auch noch ein paar Relikte im Code anthalten, welche ich noch nicht gelöscht habe. ;-)

[andyfau]

Da machst Du aber ganz schöne Klimmzüge. Mit dem Ansatz von Normalo0815 und den Anmerkungen von Knobbi ist das doch ganz simpel.
(Siehe Anlage)

Tabelle MoeglichePartien leeren
Tabelle Spieler mit Daten füllen
Tabelle MoeglichePartien mit der Anfügeabfrage füllen
Ergebnisse nach Bedarf in  MöglichePartien eintragen
(Achtung Spieldatum ist Kriterium, nicht das Ergebnis(2 Felder). Denn es kann ja auch 0 zu 0 gespielt worden sein.
Dann gibt es zwei Abfragen qgespieltePartien und qfehlendePartien. Erst in diesen Abfragen werden Name, Adressen, etc. dazu gespielt.

[Knobbi38]

Hallo Pit,

wie du schon selbe erkannt hast, ist da noch Luft nach oben.
Lade doch mal eine Beispiel Datenbank hier hoch, damit die Helfer nicht alles nachbauen müssen. 

Bei Code wie diesem:

Code [Auswählen] Erweitern

    myrs.MoveFirst
   
    Do Until myrs.EOF
        Anzspieler = Anzspieler + 1
        myrs.MoveNext
    Loop

könnte man auch einfach mit Count() die Anzahl der DS vorher abfragen und so ganz nebenbei gibt es auch noch solche SQL-Operatoren wie Exists oder IN, die sich hier anbieten würden.

Bei Zuweisungen wie

Code [Auswählen] Erweitern

sqlstr = ""
sqlstr = "SELECT Be ... "
an eine Stringvariable kann man sich die Initialisierung mit einem Leerstring sparen.

Mir ist auch nicht klar, warum du haufenweise temporär neue Querydefs anlegst, man kann doch direkt mit dem SQL-String ein Recordset öffnen. Hast du schon mal beobachtet, ob deine DB sich so unnötig aufbläht?

Knobbi38

 

[Beaker s.a.]

@andy

Denn es kann ja auch 0 zu 0 gespielt worden sein.

Nein, unentschieden gibt es beim Pool(billard) nicht. Es wird immer
"Best of" einer ungeraden Zahl an Frames gespielt.

@pit1303
Das Einfachste ist wohl ein Feld "PartieErgebnis" einzuführen und es
bei beendeten Partien mit 1 (Heimsieg) oder 2 (Gastsieg) zu füllen.
Da ist das Filtern ein Kinderspiel

Code [Auswählen] Erweitern

... WHERE PartieErgebnis IS NULL ...
gruss ekkehard