Abfrage mit mehreren Tabellen 1:N

[Wichtelmann]

Ich komme bei folgendem Problem nicht weiter:
Tabelle1 mit Mitgliederdaten
Tabelle2 mit Lehrgängen (also mehrere pro Mitglied) über ID verknüpft
Tabelle3 mit Untersuchungen wie vor
Tabelle4 mit Übungen wie vor

Die Tabellen 2-4 enthalten u.a. ein Datumfeld.
Ich möchte nun eine Übersicht über jedes Mitglied mit den jüngsten DAten aus Tabellen 2-4.
Bei einer normalen Abfrage erhalte ich ja jedes Mitglied so oft wie es Daten inden verschiedenen Tabellen gibt.

Wie mus sich dennd a vorgehen? Ich habe etwas mit Hilfsabfrage gelesen, komme jedoch nicht weiter.

[MzKlMu]

Hallo,
nach meiner Auffassung sollten die Tabellen 2-4 in einer Tabelle (z.B. tblEreignisse) zusammengefasst werden. In dieser Tabelle ein Kennzeichen für die Art (1=Lehrgänge, 2=Untersuchungen, 3=Übungen).
Dann kannst Du nach Mitglied und Art gruppieren und Max von Datum anwenden.
Für die Art der Ereignisse sollte man zweckmäßigerweise auch eine Tabelle anlegen.

Wobei ich jetzt nicht weis, ob sich die Tabellen 2-4 so ohne weiteres zusammenfassen lassen.

Kannst Du mal ein Bild des Beziehungsfensters hier zeigen ?
Die Tabellen aber so groß ziehen, dass man alle Felder sieht.

[Wichtelmann]

Eine Zusammenführung der Tabellen würde ich eher ungern machen. Sie haben auch nicht den gleichen Aufbau.
Machen wir es zu Beginn einfacher und nehmen wir nur eine Tabelle sazu.
Also Tabelle 1 mit Primärschlüssel ID
Tabelle 2 mit Primärschlüssel, Datum, Bemerkung, Verknüpfung (zur ID der Tabelle 1)
Die Tabelle 2 enthält 10 verschiedene Daten unterschiedlichen Datums.
Ich möchte in einer Abfrage nur den Datensatz miz dem jüngsten Datum anzeigen. Wie gehe ich da vor?

[Bitsqueezer]

Hallo,

ich würde hier wohl auch die Tabellen nicht zusammenführen, da inhaltlich völlig unterschiedlich.

Die Abfrage sollte in etwa so aussehen:

Code [Auswählen] Erweitern

SELECT   MA.ID_Mitarbeiter
        ,MA.Vorname
        ,MA.Nachname
        ,LG.Lehrgangsdatum
        ,US.Untersuchungsdatum
        ,UEB.Übungsdatum
FROM tblMitarbeiter AS MA
LEFT JOIN (SELECT   TOP 1
                    LG1.ID_Lehrgang
                   ,LG1.ID_Mitarbeiter
                   ,LG1.Lehrgangsdatum
                   ,LG1.LehrgangsName
           FROM tblLehrgänge AS LG1
           ORDER BY LG1.Lehrgangsdatum DESC) AS LG
    ON LG.ID_Mitarbeiter = MA.ID.Mitarbeiter
LEFT JOIN (SELECT   TOP 1
                    US1.ID_Untersuchung
                   ,US1.ID_Mitarbeiter
                   ,US1.Untersuchungsdatum
                   ,US1.Sonstwas
           FROM tblUntersuchungen AS US1
           ORDER BY US1.Untersuchungsdatum DESC) AS US
    ON US.ID_Mitarbeiter = MA.ID.Mitarbeiter
LEFT JOIN (SELECT   TOP 1
                    UEB1.ID_Übung
                   ,UEB1.ID_Mitarbeiter
                   ,UEB1.Übungsdatum
                   ,UEB1.Irgendwas
           FROM tblÜbungen AS UEB1
           ORDER BY UEB1.Übungsdatum DESC) AS UEB
    ON UEB.ID_Mitarbeiter = MA.ID.Mitarbeiter   
Das ist natürlich Standard-SQL, in Access gibt es ganz seltsame Klammerungen, die bastele ich Dir aber hier nicht nach. Am besten ziehst Du alle 4 Tabellen in eine Abfrage, erstellst LEFT JOINs zu den Untertabellen auf ID_Mitarbeiter, dann gehst Du in SQL und ergänzt das TOP 1 und ORDER BY in jeder Untertabelle.

Gruß

Christian

[Wichtelmann]

Ich hab mal eine Abfrage der betreffenden 4 Tabellen gemacht. Ich glaube das bekomme ich so auf dem Stehgreif nicht hin, wie Du es vorschlägst. Könntest Du mir dazu bitte nopchmal unter die Arme greifen?

Code [Auswählen] Erweitern

SELECT  tbl_Daten.ID_Personalnummer
      , tbl_Daten.Nachname
      , tbl_Daten.Rufname
      , tbl_g26_un.Untersuchung
      , tbl_g26_un.Gültigkeit
      , tbl_g26_un.Bemerkung
      , tbl_g26_st.Strecke_Datum
      , tbl_g26_st.Strecke_Ort
      , tbl_g26_st.Bemerkung
      , tbl_g26_ue.Uebung_Datum
      , tbl_g26_ue.Uebung_Ort
      , tbl_g26_ue.Uebung_Thema
      , tbl_g26_ue.Geraeteart
      , tbl_g26_ue.Einsatzdauer
      , tbl_g26_ue.Bemerkung
      , tbl_g26_ue.EInsatz
      , tbl_Daten.aktiver_AGT

FROM ((tbl_Daten LEFT JOIN tbl_g26_st ON tbl_Daten.ID_Personalnummer = tbl_g26_st.FK_ID)
LEFT JOIN tbl_g26_ue ON tbl_Daten.ID_Personalnummer = tbl_g26_ue.FK_ID)
LEFT JOIN tbl_g26_un ON tbl_Daten.ID_Personalnummer = tbl_g26_un.FK_ID
WHERE (((tbl_Daten.aktiver_AGT) Like True));

Habe es jetzt mal versucht so umzustellen:

Code [Auswählen] Erweitern

SELECT     FM.ID_Personalnummer
    ,FM.Nachname
    ,FM.Rufname
    ,FM.Untersuchung
    ,FM.Gültigkeit
    ,FM.Bemerkung
    ,ST.Strecke_Datum (hier fehlte der Punkt)
    ,ST.Strecke_Ort
    ,ST.Bemerkungen
    ,US.Gültigkeit
    ,US.Bemerkung
    ,UEB.Uebung_Datum
    ,UEB.Uebung_Ort
    ,UEB.Uebung_Thema
    ,UEB.Geraeteart
    ,UEB.Einsatzdauer
    ,UEB.Bemerkung
    ,UEB.EInsatz                  


FROM tbl_Daten AS FM

LEFT JOIN (SELECT   TOP 1
    ,ST1.Strecke_Datum
    ,ST1.Strecke_Ort
    ,ST1.Bemerkung
               
    FROM tbl_g26_st AS ST1
    ORDER BY ST1.Strecke_Datum DESC) AS ST
   ON ST.FK_ID = FM.ID_Personalnummer
   
LEFT JOIN (SELECT   TOP 1
    US1.Untersuchung
    ,US1.Gültigkeit
    ,US1.Bemerkung                   


    FROM tbl_g26_un AS US1
        ORDER BY US1.Untersuchung DESC) AS US
    ON US.FK_ID = FM.ID_Personalnummer

LEFT JOIN (SELECT   TOP 1
    UEB1.Uebung_Datum
    ,UEB1.Uebung_Ort
    ,UEB1.Uebung_Thema
    ,UEB1.Geraeteart
    ,UEB1.Einsatzdauer
    ,UEB1.Bemerkung
    ,UEB1.EInsatz

        FROM tbl_g26_ue AS UEB1
        ORDER BY UEB1.Uebung_Datum DESC) AS UEB
    ON UEB.FK_ID = FM.ID_Personalnummer;  (hier fehlte das ;)

Fehlermeldung dann Eigenschaft nicht gefunden.
P.S. 2 Fehler hatte ich noch gefunden und beseitigt. Ändert aber noch nichts.

[Bitsqueezer]

Hallo,

wenn ich keinen übersehen habe, müßte es wohl so sein:

Code [Auswählen] Erweitern

SELECT  D.ID_Personalnummer
      , D.Nachname
      , D.Rufname
      , UN.Untersuchung
      , UN.Gültigkeit
      , UN.Bemerkung
      , ST.Strecke_Datum
      , ST.Strecke_Ort
      , ST.Bemerkung
      , UE.Uebung_Datum
      , UE.Uebung_Ort
      , UE.Uebung_Thema
      , UE.Geraeteart
      , UE.Einsatzdauer
      , UE.Bemerkung
      , UE.EInsatz
      , D.aktiver_AGT
FROM ((tbl_Daten AS D
LEFT JOIN (SELECT TOP 1 ST1.FK_ID
                       ,ST1.Strecke_Ort
                       ,ST1.Strecke_Datum
                       ,ST1.Bemerkung
           FROM tbl_g26_st AS ST1
           ORDER BY ST1.Strecke_Datum DESC) AS ST 
    ON D.ID_Personalnummer = ST.FK_ID)
LEFT JOIN (SELECT TOP 1  UE1.FK_ID
                        ,UE1.Uebung_Datum
                        ,UE1.Uebung_Ort
                        ,UE1.Uebung_Thema
                        ,UE1.Geraeteart
                        ,UE1.Einsatzdauer
                        ,UE1.Bemerkung
                        ,UE1.EInsatz
           FROM tbl_g26_ue AS UE1
           ORDER BY UE1.Uebung_Datum DESC) AS UE
    ON D.ID_Personalnummer = UE.FK_ID)
LEFT JOIN (SELECT TOP 1  UN1.FK_ID
                        ,UN1.Untersuchung
                        ,UN1.Gültigkeit
                        ,UN1.Bemerkung
           FROM tbl_g26_un AS UN1
           ORDER BY UN1.Untersuchung DESC) AS UN
    ON D.ID_Personalnummer = UN.FK_ID
WHERE D.aktiver_AGT Like True;   
Das Semikolon ist egal, wird für gewöhnlich ignoriert.

Gruß

Christian

PS: Felder mit Umlauten und Sonderzeichen besser vermeiden, wenn sie vorkommen, sicherheitshalber mit [] umschließen.
Außerdem ist es ungeschickt, die Foreign Keys alle mit "FK_ID" zu benennen. Sie sollten namentlich wie der PK aussehen (ich persönlich nehme immer den gleichen Namen), damit man sofort weiß, was wozu gehört - und Access und andere Datenbanken werden diese Verbindung bei gleichem Namen auch automatisch vorschlagen. Darüber hinaus hättest Du ein Problem ab dem 2. FK, was ist das dann, "FK2_ID"? 

[Wichtelmann]

Vielen DAnk, das ist schonmal klasse, zumindest kommt kerine Fehlermeldung.
Aber die Abfrage iust fast leer. vermutlich habe ich noch/wieder einen Fehler eingebaut.

Ich habe Deine Hinweise mit Umlauten und den ID's geändert. Vermutlich habe ich dabei durcheinander fabriziert.
In den Tabellen sind die Datensätze nicht unbedingt nach Datum sortiert. Wird durch SELECT TOP 1 immer der Erste Datensatz genommen? Da müsste ich den jüngsten der gesamten Tabelle haben.

Aber wieso habe ich keine Daten in der Tabelle?

Code [Auswählen] Erweitern

SELECT D.ID_Personalnummer, D.Nachname, D.Rufname, UN.Untersuchung, UN.Gueltigkeit, UN.Bemerkung, ST.Strecke_Datum, ST.Strecke_Ort, ST.Bemerkung, UE.Uebung_Datum, UE.Bemerkung, UE.Einsatz
FROM ((tbl_Daten AS D LEFT JOIN (SELECT TOP 1 ST1.FK_ST_ID
                       ,ST1.Strecke_Ort
                       ,ST1.Strecke_Datum
                       ,ST1.Bemerkung
           FROM tbl_g26_st AS ST1
           ORDER BY ST1.Strecke_Datum DESC)  AS ST ON D.ID_Personalnummer = ST.FK_ST_ID) LEFT JOIN (SELECT TOP 1  UE1.FK_UE_ID
                        ,UE1.Uebung_Datum
                        ,UE1.Uebung_Ort
                        ,UE1.Uebung_Thema
                        ,UE1.Geraeteart
                        ,UE1.Einsatzdauer
                        ,UE1.Bemerkung
                        ,UE1.Einsatz
           FROM tbl_g26_ue AS UE1
           ORDER BY UE1.Uebung_Datum DESC)  AS UE ON D.ID_Personalnummer = UE.FK_UE_ID) LEFT JOIN (SELECT TOP 1  UN1.FK_UN_ID
                        ,UN1.Untersuchung
                        ,UN1.Gueltigkeit
                        ,UN1.Bemerkung
           FROM tbl_g26_un AS UN1
           ORDER BY UN1.Untersuchung DESC)  AS UN ON D.ID_Personalnummer = UN.FK_UN_ID
WHERE (((D.aktiver_AGT) Like True));


[Bitsqueezer]

Hallo,

OK, das ist jetzt nicht wirklich viel besser, sowas wie "FK_ST_ID" - der Name soll doch verdeutlichen, daß es um die Personalnummer geht. Also entweder "FK_Personalnummer_ID" oder gleich (wie ich das grundsätzlich bevorzuge, weil es das Leben VIEL einfacher macht) "ID_Personalnummer" in beiden Tabellen. Ich stelle ID-Felder auch immer an den Anfang der Feldliste, beginnend mit dem PK-ID, damit man auch im Beziehungsfenster alles, was verbunden werden kann, immer oben in der Liste hat.

Ja, SELECT TOP 1 verwendet nur den ersten Datensatz im SELECT. Durch das ORDER BY..DESC nach Datum ist es absteigend nach Datum sortiert, entsprechend erhältst Du immer den Datensatz mit dem neuesten Datum.

Warum es keine Daten liefert, kannst Du nur selbst herausfinden. Ohne Deine Datenbank kann ich Dir nicht "ferndebuggen", was das Problem sein könnte.

Du könntest z.B. jede Unterabfrage mal einzeln in eine Abfrage stellen und ausprobieren, ob sie das gewünschte Ergebnis liefert, z.B. mit der Personalnummer 23.

Gruß

Christian

[markusxy]

@christian, von der Logik her passt dein Vorschlag nicht.
Ein Subselect mit Top 1 liefert genau einen Datensatz.
Er braucht aber für jeden Datensatz aus dem Hauptselect ein Ergebnis.

Es würde passen - z.B. bei einem Outer Apply beim SQL Server.

Aber wenn ich wirklich nur Access habe, dann macht man doch das übliche zur Filterung mit Exists/Subselect join auf Gruppierten Subselect/Subselect im Select was nur die ID liefert auf die dann später gejoint wird.
Also jede Menge Möglichkeiten...

Hier ein exemplarisches Beispiel mit Exist:

Code [Auswählen] Erweitern

SELECT D.ID_Personalnummer
    ,D.Nachname
    ,D.Rufname
    ,ST.Strecke_Datum
    ,ST.Strecke_Ort
    ,ST.Bemerkung
FROM tbl_Daten AS D
LEFT JOIN (
    SELECT ST1.FK_ST_ID
        ,ST1.Strecke_Ort
        ,ST1.Strecke_Datum
        ,ST1.Bemerkung
    FROM tbl_g26_st AS ST1
    WHERE EXISTS (
            SELECT 0
            FROM tbl_g26_st
            WHERE FK_ST_ID = ST.FK_ST_ID
            HAVING MAX(Strecke_Datum) = ST1.Strecke_Datum
            )
    ) AS ST ON ST.FK_ST_ID = D.ID_Personalnummer
WHERE D.aktiver_AGT = True;
Und Hier noch mit Gruppiertem Subselect:

Code [Auswählen] Erweitern

SELECT D.ID_Personalnummer
,D.Nachname
,D.Rufname
,ST.Strecke_Datum
,ST.Strecke_Ort
,ST.Bemerkung
FROM tbl_Daten AS D
LEFT JOIN (
SELECT ST1.FK_ST_ID
,ST1.Strecke_Ort
,ST1.Strecke_Datum
,ST1.Bemerkung
FROM tbl_g26_st AS ST1
INNER JOIN (
SELECT FK_ST_ID
,MIN(Strecke_Datum) Strecke_Datum
GROUP BY FK_ST_ID
) G ON G.FK_ST_ID = ST1.FK_ST_ID
AND G.Strecke_Datum = ST1.Strecke_Datum
) AS ST ON ST.FK_ST_ID = D.ID_Personalnummer
WHERE D.aktiver_AGT = True;

[Bitsqueezer]

@markusxy :
Du hast natürlich recht. Daran hatte ich nicht gedacht.
Nach einmal Ausführen wäre es mir auch sofort aufgefallen...

Gruß

Christian

[Wichtelmann]

Das scheint ja eine harte Nuß zu sein 

Code [Auswählen] Erweitern

SELECT D.
ID_Personalnummer
    ,D.Nachname
    ,D.Rufname
,D.aktiver_AGT
    ,ST.St_Datum
    ,ST.St_Ort
    ,ST.Bemerkung
FROM tbl_Daten AS D LEFT JOIN (SELECT

ST1.ID_Personalnummer
        ,ST1.St_Ort
        ,ST1.St_Datum
        ,ST1.Bemerkung

    FROM tbl_g26_st AS ST
    WHERE EXISTS (
            SELECT 0
            FROM tbl_g26_st
            WHERE ID_Personalnummer = ST.ID_Personalnummer
            HAVING MAX(St_Datum) = ST.St_Datum
            )
    AS ST ON ST.ID_Personalnummer = D.ID_Personalnummer
WHERE D.aktiver_AGT = True;

Ein paar Variablen angepasst kommt jedoch die Fehlermeldung "Eigenschaft nicht gefunden"

[markusxy]

Fehlermeldung "Eigenschaft nicht gefunden"

Um den Fehler zu finden könntest du die Abfrage schrittweise aufbauen.
Also erstmal nur eine Unterabfrage einbinden und dann Schritt für Schritt aufbauen.

[Knobbi38]

Hallo,

"Having" ohne "Group By" erscheint mir fragwürdig.

[markusxy]

"Having" ohne "Group By" erscheint mir fragwürdig.

Warum?

[Knobbi38]

@markusxy

Hallo,

hast recht, da war ich vielleicht ein wenig voreilig. Ich hatte übersehen. Irgend wo hatte ich mal gelesen, daß lt. Standard eine fehlende Group By Clause implicit ergänzt wird, um den Zugriff auf die Felder zu ermöglichen. 

Grüße
Ulrich