Anfügeabfrage mit Inkosistenzsuche

[gsaccess]

Ich versuche seit einiger Zeit mit Anfügeabfragen mit Inkosistenzsuche(damit Datensätze nicht 2x angefügt werden) Tabellen zu aktualieren.
Ich komme aber bei einigen Tabellen nicht dahinter warum es immer eine Schlüsselverletzung gibt.
in der Anlage ein banales Beispiel, aber ich stehe irgendwie auf dem Schlauch.
Es wäre super wenn sich jemand diese Abfrage anschauen und mir den Fehler erklären könnte.
Vielen Dank
Günther

[markusxy]

Ich komme aber bei einigen Tabellen nicht dahinter warum es immer eine Schlüsselverletzung gibt.

Das Problem ist, dass du einfach alles einfügst und nicht prüfst, ob die Daten schon vorhanden sind.

Ich hab das Select Statement etwas verändert, damit du mit einem Blick siehst was du falsch machst:

Code [Auswählen] Erweitern

SELECT STAMMDATEN_KUNDEN.Kunden_Nr
,STAMMDATEN_KUNDEN.ANREDE
,STAMMDATEN_KUNDEN.TITEL
,STAMMDATEN_KUNDEN.HAUPTNAME
,STAMMDATEN_KUNDEN.VORNAME
,STAMMDATEN_KUNDEN.AkadGradNeu
,STAMMDATEN_KUNDEN.ZUSATZ
,STAMMDATEN_KUNDEN.KONTAKT
,STAMMDATEN_KUNDEN.GEBDAT
,STAMMDATEN_KUNDEN.NOTITZEN
,STAMMDATEN_KUNDEN.AUFNAHME
,STAMMDATEN_KUNDEN.[UID_Nr:]
,STAMMDATEN_KUNDEN.[FN:]
,STAMMDATEN_KUNDEN.Zahlungskonditionen
,STAMMDATEN_KUNDEN.ZahlungskontitionAngebot
        ,tblKunden.KundenNr
FROM STAMMDATEN_KUNDEN
LEFT JOIN tblKunden ON STAMMDATEN_KUNDEN.Kunden_Nr = tblKunden.KundenNr;
Die Spalte tblKunden.KundenNr gehört logischerweise nicht in den Select Bereich, es soll dir nur helfen eine Where Bedingung zu formulieren, damit vorhandene Daten nicht erneut eingefügt werden.
Also einfach mal versuchen.

[gsaccess]

dieser code ist aber doch keine Anfügeabfrage. Wenn ich diese in eine Anfügeabfrage verändere kommt die gleiche Fehlermeldung

[ebs17]

Bei einer Inkonsistenzprüfung fehlt beim LEFT JOIN auf jeden Fall noch ein zusätzliches

Code [Auswählen] Erweitern

WHERE tblKunden.KundenNr Is NULL
Anderenfalls führt der JOIN nur zu einer Vervielfältigung der Datensätze aus STAMMDATEN und damit zum Gegensätzllichen des eigentlichen Wunsches.

[markusxy]

dieser code ist aber doch keine Anfügeabfrage. Wenn ich diese in eine Anfügeabfrage verändere kommt die gleiche Fehlermeldung

Ich hab ja bewusst nur den Select Teil der Abfrage gezeigt.
Sinn wäre gewesen, dass du deinen eigenen Verstand einsetzt um das Problem zu finden - denn was man versteht merkt man sich auch.

[gsaccess]

Danke für eure Rückmeldungen. Ich habe nun die Fehler gefunden. zB war die Anrede in der alten Tabelle ein Text. In der neuen Tabelle gitt es dazu eine FS und eine eigene Tabelle für die Anrede.
Ich knoble derzeit daran wie ich die FS zB Frau = 1,... in die neue Tabelle bekomme.

[Beaker s.a.]

Ich knoble derzeit daran wie ich die FS zB Frau = 1,... in die neue Tabelle bekomme.

Switch() könnte helfen.

[gsaccess]

mit switch habe ich bisher überhaupt nicht gearbeitet. Der code müsste nach meiner Suche im Netz so aussehen.

Code [Auswählen] Erweitern

Function MatchUp (Anredeid_F As String)
MatchUp = Switch(Anredeid_F = "Firma", "1", _
Anredeid_F = "Frau", "2", _
Anredeid_F = "Herr", "3", _
Anredeid_F = "Fam.", "4")
End Function

Wie kann ich diesen code aber in die Abfrage einbinden?

Code [Auswählen] Erweitern

INSERT INTO tblKunden ( KundenNr, HAUPTNAME, VORNAME, ZUSATZ, KontaktPers, GebDatKunde, Notizen, AufnahmeDat, UID_Nr, FBNr, ZahlungskondID_F, ZahlungskondAngebotID_F )
SELECT STAMMDATEN_KUNDEN.Kunden_Nr, STAMMDATEN_KUNDEN.HAUPTNAME, STAMMDATEN_KUNDEN.VORNAME, STAMMDATEN_KUNDEN.ZUSATZ, STAMMDATEN_KUNDEN.KONTAKT, STAMMDATEN_KUNDEN.GEBDAT, STAMMDATEN_KUNDEN.NOTITZEN, STAMMDATEN_KUNDEN.AUFNAHME, STAMMDATEN_KUNDEN.[UID_Nr:], STAMMDATEN_KUNDEN.[FN:], STAMMDATEN_KUNDEN.Zahlungskonditionen, STAMMDATEN_KUNDEN.ZahlungskontitionAngebot
FROM STAMMDATEN_KUNDEN LEFT JOIN tblKunden ON STAMMDATEN_KUNDEN.Kunden_Nr = tblKunden.KundenNr;


[MzKlMu]

Hallo,
Du brauchst hier keine extra Funktion. Switch kann direkt in der Abfrage verwendet werden. Zu beachten ist nur, daß statt Komma Semikolon zu verwenden sind (bei einem deutschen Access).

[ebs17]

Ich habe nun die Fehler gefunden

Der Glaube ist des Menschen Himmelreich.

Bei dem, was Du zeigst, ist nie und nimmer eine Inkonsistenzprüfung enthalten, der Begriff Inkonsistenzabfrage ist da nur aus der lostrommel gefallen.

Ich knoble derzeit daran wie ich die FS zB Frau = 1,... in die neue Tabelle bekomme.

Wenn man eine Tabelle Anreden hätte, würde man sie dann auch einbeziehen:
=> JOIN Stammdaten zu Anreden über die Anrede ermöglicht Zugriff auf die AnredeID, die man dann somit unmittelbar in der Anfügeabfrage verwenden kann.

[gsaccess]

Danke für eure Rückmeldungen!

Hallo,
Du brauchst hier keine extra Funktion. Switch kann direkt in der Abfrage verwendet werden. Zu beachten ist nur, daß statt Komma Semikolon zu verwenden sind (bei einem deutschen Access).

Wo muss der switch code genau eingefügt werden?

Wenn man eine Tabelle Anreden hätte, würde man sie dann auch einbeziehen:
=> JOIN Stammdaten zu Anreden über die Anrede ermöglicht Zugriff auf die AnredeID, die man dann somit unmittelbar in der Anfügeabfrage verwenden kann.

Dabei habe ich wieder das gleiche Problem mit der Fehlermeldung. Siehe Anlage.
Mir ist auch nicht klar wie Access den Zugriff auf die zb tblAnlrede, tblAkadGrad.., tblZahlungkonditionen..., erkennen soll. In der Anlage auch nochmals die Musterdb mit der Anfügeabfrage. Wie genau muss der Code mit Join aussehen, dass die Abfrage funktioniert. Die Beziehungen müssten ja meiner Meinung nach passen. Siehe Bild.
Ich drehe mich da einfach im Kreis.

[MzKlMu]

Hallo,
die Abfrage ist falsch. Die Zieltabelle darf nicht in die Anfügeabfrage aufgenommen werden. Die Zieltabelle ist ja noch leer, welchen Sinn soll da eine Beziehung ergeben. Die Abfrage darf nur die Tabelle für die Anrede und die Tabelle STAMMDATEN_KUNDEN enthalten. Verknüpft (mit RIGHT JOIN) über den Text der Anrede. Die AnredeID wird dabei als AnredeID_F übertragen.

Code [Auswählen] Erweitern

INSERT INTO tblKunden ( AnredeID_F, Hauptname, KundenNr )
SELECT AnredeID, HAUPTNAME, Kunden_Nr
FROM tblAnrede RIGHT JOIN STAMMDATEN_KUNDEN ON tblAnrede.Anrede = STAMMDATEN_KUNDEN.ANREDEAbfrage beispielhaft nur mit 3 Feldern. Abfrageentwurf als Bild anbei.

Außerem sind in der Zieltabelle in allen allen Fremdschlüsselfelder der Standardwert 0 zu löschen.

Zu den Beziehungen:
Die Beziehung mit dem Fragezeichen (im 2.Bild) dürfte nicht stimmen.
Gibt es Zusammenhänge zwischen dem alten und dem neuen AkadGrad ?
Wenn ja, muss das mit einer Zuordnungstabelle abgebildet werden.

[ebs17]

Der ersten Aussage von Klaus stimme ich nicht zu, siehe Abfrage weiter unten.

Außerem sind in der Zieltabelle in allen allen Fremdschlüsselfelder der Standardwert 0 zu löschen.

So ist es richtig. Bei eingestellter referentieller Integrität kann man keinen Fremdschlüssel eintragen, den es in der jeweiligen Primärtabelle als Primärschlüssel nicht gibt. Das trifft hier auf diese 0 als Standardwert zu. Dies gibt dann auch Anlass für die bemängelten Indexfehler.

Auf die Beziehungs-Stilblüte mit den Zahlungskonditionen (2 Primärschlüssel auf einen Fremdschlüssel) gehe ich nicht weiter ein.

Ich habe die Abfrage auf einige Felder verkürzt, um mir Schreibarbeit zu sparen und Dir die Gelegenheit zu geben, mit eigenem Verstehen die Vervollständigung selbst vorzunehmen => Lerning by Doing.
Die erste Nachschlagetabelle (tblAnrede) ist eingebunden.

Code [Auswählen] Erweitern

INSERT INTO
   tblKunden(
      KundenNr,
      AnredeID_F,
      HAUPTNAME,
      VORNAME
   )
SELECT
   S.Kunden_Nr,
   A.AnredeID,
   S.HAUPTNAME,
   S.VORNAME
FROM
   (STAMMDATEN_KUNDEN AS S
      LEFT JOIN tblAnrede AS A
      ON S.ANREDE = A.Anrede
   )
   LEFT JOIN tblKunden AS K
   ON S.Kunden_Nr = K.KundenNr
WHERE
   K.KundenNr Is Null

[MzKlMu]

Hallo,
@Eberhard
ich bin ja in SQL nicht ganz so sattelfest würde aber doch gern meinen Erfahrungsschatz vergrößern. Daher würde mich jetzt doch mal interessieren wozu hier die Tabelle tblKunden notwendig ist. Die Tabelle als Zieltabelle ist ja leer.
Ich habe meine Abfrage getestet, und da werden genau die 18 Datensätze angefügt die in den Stammdaten sind, mit der richtigen Übertragung des Fremdschlüssels zur Anrede.

Wo siehst Du da den Vorteil Deiner Abfrage ?

Außerdem habe ich Deine Abfrage getestet, da wird nichts angefügt. Es kommt die Meldung:
Es werden 0 Datensätze angefügt.
Die Datenblattansicht der Abfrage liefert auch keine Datensätze, während die von mir gezeigte Abfrage in der DAtenblattansicht auch diese 18 DS zeigt.

[ebs17]

wozu hier die Tabelle tblKunden notwendig ist

Die Aufgabe ist ja, eine Inkonsistenzprüfung auszuführen, also nur Datensätze aus STAMMDATEN_KUNDEN zum Anfügen zu berücksichtigen, die es in tblKunden noch nicht gibt. Schlüssel zum Vergleich ist hier die eindeutige Kundennummer.
Datensätze aus A, die nicht in B sind

Es werden 0 Datensätze angefügt.

Genau das ist Ziel der Abfrageformulierung, wenn es die betreffenden Kundennummern in der Zieltabelle bereits gibt. Also prüfe, nach der wievielten Anfügung Du diese Wirkung erzielst und was es in der Zieltabelle bereits gibt.

Die Wirkung soll also gleich sein, als wenn man keinen eindeutigen Index auf Kundennummer hätte. Diesen Index sollte man trotzdem immer als Absicherung haben, weil Anfügungen und Editierungen auf die Zieltabelle auch über andere Wege erfolgen könnten (andere Aktionsabfragen, schreibende Recordsetaktionen, Arbeit über gebundene Formulare, Standardimporte, händische Aktionen), die ja im funktionellen Sinne auch überwacht werden müssten.

Warum dieser zusätzliche Aufwand, obwohl der eindeutige Index auf Kundennummer alle Duplikate abblockt?
1) Indexfehler sind auch Fehler. Wenn man gezielt fehlerfrei (als Idealfall) arbeiten will, ist jeder vorhersehbare und vermeidbare Fehler einer zuviel.
2) Bei dem Abblocken von Duplikaten durch den eindeutigen Index werden in der Zieltabelle trotzdem Autowerte "verbraucht". Das Geschrei über Lücken kennt jeder, es ist nicht funktionell schlecht, aber trotzdem unschön.
3) In einem Fall aus einem Forum gab es beim Abfeuern von Duplikaten auf den Unique Index tatsächlich Datenbankaufblähung, durch Massendaten dann erheblich. Die genauen Gründe dafür kenne ich nicht weiter. Aber spätestens, wenn man innerhalb eines Gesamtimportes zwischendurch gezwungen ist, das Backend zu komprimieren, hat man einen Katastrophenfall.
4) Eigentlich liegt das (mein) Bemühen darin, unmittelbar eine hohe Performance zu erreichen. Das schließt ein, Aufwand niedrig zu halten, also auch die bewegten Datensätze in der auswählenden Abfrage niedrig zu halten wie auch das eigentliche Schreiben in die Zieltabelle.

Das alles ist mir allermeist eine etwas längere Abfrageformulierung wert und also Standard.

Ach so: Ich hatte auch getestet und bin mir meiner Sache sicher.