Anfügeabfrage Verknüpfungstabelle m:n

[Katastrophenkommando]

Hallo zusammen,

ich versuche mich gerade an der m:n-Beziehung und komme nicht weiter.

In meiner DB gibt es 2 Tabellen (tblMitarbeiter und tblLehrgänge), die in einer m:n-Beziehung zueinander stehen. Die m:n-Beziehung habe ich über eine Verknüpfungstabelle (tblMitarbeiterLehrgang) in zwei 1:n-Beziehungen zerlegt. Nun möchte ich, dass die Anfügeabfrage beim Ausführen die Verknüpfungstabelle mit den (noch nicht vorhandenen) "Kombinationsdatensätzen" aus MitarbeiterID und LehrgangID ergänzt. Ziemlich genau dasselbe Problem wie <hier>.

Meine Anfügeabfrage sieht so aus:

Code [Auswählen] Erweitern

INSERT INTO tblMitarbeiterLehrgang ( LehrgangID, MitarbeiterID )
SELECT tblMitarbeiter.MitarbeiterID, tblLehrgänge.LehrgangID
FROM tblLehrgänge, tblMitarbeiter;

Das funktioniert nur leider nicht und ich finde nicht raus wieso nicht. Alternativ habe ich das InnerJoin in der zweiten Abfrage berücksichtigt, das führt aber nur dazu, dass Access von vornherein keinen Datensatz erkennt, der anzufügen wäre.

Ich bekomme beim Ausführen die Fehlermeldung, dass x Datensätze wegen Schlüsselverletzungen nicht eingefügt wurden. Wenn ich wie in dem oben verlinkten Topic die tblMitarbeiter oder die tblLehrgänge nach From entferne (das war in dem verlinkten Topic die Lösung), fragt Access nach dem entsprechenden Parameter.

Die betroffenen Felder sind alle auf Autowert bzw. Zahl und Long Integer eingestellt. In der Verknüpfungstabelle habe ich einen zusammengesetzten Primärschlüssel aus MitarbeiterID und LehrgangID erstellt. Wenn ich in der Verknüpfungstabelle Datensätze manuell eintrage, bekomme ich keinen Fehler.

Was mache ich falsch?

[MzKlMu]

Hallo,
die Reihenfolge der Felder muss gleich sein.

Code [Auswählen] Erweitern

INSERT INTO tblMitarbeiterLehrgang ( LehrgangID, MitarbeiterID )
SELECT  LehrgangID, MitarbeiterID
FROM tblLehrgänge, tblMitarbeiterBekommen immer alle Mitarbeiter alle Lehrgänge?
Wie machst Du das mit den Wiederholungen der Lehrgänge (was ja meist zutrifft) ?

[PhilS]

Meine Anfügeabfrage sieht so aus:

Code [Auswählen] Erweitern

Code [Auswählen] Erweitern

INSERT INTO tblMitarbeiterLehrgang ( LehrgangID, MitarbeiterID )
SELECT tblMitarbeiter.MitarbeiterID, tblLehrgänge.LehrgangID
FROM tblLehrgänge, tblMitarbeiter;

Das funktioniert nur leider nicht und ich finde nicht raus wieso nicht.

Schau dir mal die Reihenfolge der Felder ganz genau an und vergleiche sie in beiden Teilen der Abfrage.

[ebs17]

Zusätzlich: Ehe Du mehr investierst, beachte folgendes.

1) Den Overkill an Indizes in der Verknüpfungstabelle solltest Du beseitigen. Die Fremdschlüsselfelder erhalten automatisch einen nicht oberflächlich erkennbaren Index, wenn die Beziehung mit referentieller Integrität eingerichtet wird. Zusätzliche eigene erhöhen nur den Aufwand in der Verwaltung inkl. Abfrageausführung, bringen aber keinen funktionellen Effekt.
Es macht Sinn, über beide Fremdschlüsselfelder einen zusammengesetzten eindeutigen Index anzulegen, um die FK-Kombinationen einmalig zu halten. Das muss aber kein Primärschlüssel sein. Für letzteren nimmt man i.d.R. ein zusätzliches Autowertfeld. Dieses wird in der Praxis einer Verknüpfungstabelle selten genutzt, aber manchmal ist es dann doch hilfreich, einen Datensatz über genau ein Feld zu identifizieren.

2) Statt einem Ja/Nein-Feld wäre ein Datumsfeld sehr viel informativer, z.B. wann ein MA einen Lehrgang absolviert hat und ob er ihn ggf. auffrischen müsste.

3) In Stammdatentabellen trägt man besser nur echte Informationen ein, also wenn wirklich ein MA einen Lehrgang absolviert hat. Mit Deinem verwendeten Kreuzprodukt fügst Du sofort alle möglichen Varianten ein, das ist so Datenmüll. Mit solchem startet man besser nicht seine Karriere.

[Katastrophenkommando]

Hallo zusammen und erstmal ein globales Danke Euch allen!!

@ Phil und Klaus : Richtig gut - so was kleines und doch so hilfreich, da wäre ich nie alleine drauf gekommen, danke.

Bekommen immer alle Mitarbeiter alle Lehrgänge?
Wie machst Du das mit den Wiederholungen der Lehrgänge (was ja meist zutrifft) ?

Wenn ich das richtig verstehe hatte Eberhard dieselbe Kritik, darauf gehe ich gleich noch ein.

@ebs17 : Dir auch vielen Dank für die ganzen Tipps. Ich werde heute Abend mal versuchen, die umzusetzen, aber habe noch ein Verständnisproblem:

1) Den Overkill an Indizes in der Verknüpfungstabelle solltest Du beseitigen. Die Fremdschlüsselfelder erhalten automatisch einen nicht oberflächlich erkennbaren Index, wenn die Beziehung mit referentieller Integrität eingerichtet wird. Zusätzliche eigene erhöhen nur den Aufwand in der Verwaltung inkl. Abfrageausführung, bringen aber keinen funktionellen Effekt.
Es macht Sinn, über beide Fremdschlüsselfelder einen zusammengesetzten eindeutigen Index anzulegen, um die FK-Kombinationen einmalig zu halten. Das muss aber kein Primärschlüssel sein. Für letzteren nimmt man i.d.R. ein zusätzliches Autowertfeld. Dieses wird in der Praxis einer Verknüpfungstabelle selten genutzt, aber manchmal ist es dann doch hilfreich, einen Datensatz über genau ein Feld zu identifizieren.

Verstehe ich Dich richtig, dass die Verknüpfungstabelle nicht zwangsläufig einen PK braucht, wenn ich den Index über die beiden Fremdschlüsselfelder erstelle und das AutoWert-Feld nur optional ist?

2) Statt einem Ja/Nein-Feld wäre ein Datumsfeld sehr viel informativer, z.B. wann ein MA einen Lehrgang absolviert hat und ob er ihn ggf. auffrischen müsste.

Das stimmt wohl, dann suche ich mal einen Weg, das Datum nur einmal eingeben zu müssen, die entsprechenden Mitarbeiter auszuwählen und es dann bei allen auf einmal einzutragen (ich vermute hier ist "Aktualisierungsabfrage" das Stichwort?). Eine Historie der Lehrgänge bei Wiederholungen brauche ich eigentlich nicht, insofern reicht mir das letzte Datum.

3) In Stammdatentabellen trägt man besser nur echte Informationen ein, also wenn wirklich ein MA einen Lehrgang absolviert hat. Mit Deinem verwendeten Kreuzprodukt fügst Du sofort alle möglichen Varianten ein, das ist so Datenmüll. Mit solchem startet man besser nicht seine Karriere.

Der Gedanke mit dem Datenmüll schwebte mir auch schon im Kopf herum, aber ich kam auf keine andere Lösung. Mein Vorhaben war, den Mitarbeitern, die einen Lehrgang besucht haben, über ein Kontrollkästchen den Lehrgang zuzuweisen. Und das in einem Formular, in dem man den jeweiligen Lehrgang meinetwegen über ein Kombinationsfeld auswählt, alle Mitarbeiter in dem Formular angezeigt werden und nur die abgehakt werden müssen, die den Lehrgang besucht haben. Und da habe ich einen Knoten im Gehirn- brauche ich dafür nicht jede Mitarbeiter/Lehrgang-Kombination in der Verknüpfungstabelle? Oder kann man das im Formular über die tblMitarbeiter und die tblLehrgänge lösen?

VG Hauke

[MzKlMu]

Hallo,
das Hakenfeld halte ich für überflüssig, auch die Anfügeabfrage braucht es nicht.
Lege ein Hauptformular an (für die Mitarbeiter) und ein Unterformular (Verknüpfungstabelle).
Hafo un Ufo sind über die Schlüsselfelder zu verknüpfen. Per Kombinationsfeld wird wird dann für den Mitarbeiter ein zutreffnder Lehrgang ausgewählt. Dann nur noch Datum eintragen.

Verstehe ich Dich richtig, dass die Verknüpfungstabelle nicht zwangsläufig einen PK braucht,

Nein, der PK sollte als Autowert angelegt werden. Über die beiden FS Felder dann einen zusammengesetzten eindeutigen Index.

Eine Historie der Lehrgänge bei Wiederholungen brauche ich eigentlich nicht, insofern reicht mir das letzte Datum.

Das halte ich für einen fehler. Ich kann mir nicht vorstellen, dass es bei Dir keine Lehrgänge gibt die nicht dokumentiert werden müssen, auch die Historie.

[ebs17]

Verstehe ich Dich richtig, dass die Verknüpfungstabelle nicht zwangsläufig einen PK braucht

Bei einer reinen Verknüpfungstabelle wird jener selten genutzt. Bei einer etwas umfangreicheren Datenmodellierung kann es aber sein, dass die Verknüpfungstabelle zur Primärtabelle einer weiteren Beziehung wird.
Desweiteren gibt es Abfragekonstellationen wie z.B. TOP-X-Gestaltungen, wo ein simpler PK sehr hilfreich ist, um das Ganze erträglich einfach zu halten.
Aus Angst vor Platzmangel sollte man also nicht darauf verzichten.

Zu 2)
Standardwert mit Date() oder Now(). Der wird bei Anlegen des Datensatzes automatisch gefüllt.

Oder kann man das im Formular über die tblMitarbeiter und die tblLehrgänge lösen?

Ja. Zu m:n-Beziehungen gibt es spezifische Formularlösungen. Einfach mal nachschlagen.

[PhilS]

Verstehe ich Dich richtig, dass die Verknüpfungstabelle nicht zwangsläufig einen PK braucht

Bei einer reinen Verknüpfungstabelle wird jener selten genutzt. Bei einer etwas umfangreicheren Datenmodellierung kann es aber sein, dass die Verknüpfungstabelle zur Primärtabelle einer weiteren Beziehung wird.
Desweiteren gibt es Abfragekonstellationen wie z.B. TOP-X-Gestaltungen, wo ein simpler PK sehr hilfreich ist, um das Ganze erträglich einfach zu halten.

Erstmal grundsätzlich: Jede Tabelle sollte einen Primärschlüssel haben!

Ich würde bei einer M:N-Verknüpfungstabelle den zusammengesetzten PK auf den beiden Fremdschlüsselspalten belassen, weil damit die Bedeutung dieser Spalten in der Tabelle deutlicher sichtbar ist.
Technisch spielt es keine Rolle, ob man den PK auf eine zusätzliche Autowert-Spalte legt, und einen "normalen", zusammengesetzten, eindeutigen Index auf die beiden Fremdschlüsselspalten, oder andersherum.
Den zusätzliche Autowert kann man auch jederzeit später ergänzen, wenn man ihn wirklich braucht. - Nur muss man diese Möglichkeit im Hinterkopf behalten.

[ebs17]

kann, sollte, würde ... hier ist man auch im Bereich persönlicher Vorlieben und Arbeitsweisen, und bei allen wird die Hintertür für eine Abweichung offengehalten.

Erstmal grundsätzlich: Jede Tabelle sollte einen Primärschlüssel haben!

Den Grundsatz sollte man mindestens auf Tabellen des Datenmodells reduzieren, also da, wo man es mit Schlüsseln und darauf basierenden Beziehungen zu tun hat.
Ich kenne und verwende weitere Tabellen: Import, Export, temporär, Hilfstabellen und weitere. Da kommt teilweise nicht mal ein Index daher.

Ich würde noch darauf hinweisen, dass eine Verknüpfungstabelle mehr als zwei Fremdschlüssel zusammenbringen kann, und wie gesagt, wenn die Tabelle als Primärtabelle (enthält Primärschlüssel) mit einer weiteren in Beziehung stehen soll, braucht man für den Schlüssel der Sekundärtabelle auch einen Mehrfelderschlüssel. Das kann dann schnell unübersichtlich werden, erhöht auf jeden Fall die Länge einer Abfrage, die dies verwendet.
Einfeldschlüssel und Mehrfelderschlüssel macht schon einen Unterschied, auch technisch.

Aber es ist schon gut, wenn überhaupt darüber nachgedacht wird und man sich über Varianten bewusst ist.

[Katastrophenkommando]

Super, vielen Dank Euch allen. Jetzt habe ich es verstanden. Ich habe das jetzt anhand Eurer Vorschläge umgebaut. Das Kontrollkästchen habe ich durch ein Datumsfeld ersetzt, um Wiederholungsschulungen dokumentieren zu können.

Beim Index der Verknüpfungstabelle bin ich nun Ebs Vorschlag gefolgt, Phils Erläuterungen waren aber auch noch mal hilfreich für das Verständnis.

Nun funktioniert alles so, wie ich mir das vorgestellt hatte

Viele Grüße
Hauke