Aktualisierungsabfrage mit Parametern per vba

[jagger]

@PhilS,

ja das ist der Code.
Die Parameter sind doch die Werte, die bei der Abfrage in den Zeilen
"Aktualisieren" und "Kriterium" stehen, oder?

LG
jagger

[PhilS]

ja das ist der Code.

Dann übergibst du hardcodiert die Texte OAGKDNR und DAGKDNR an die Parameter der Abfrage. - Genau diese Texte, die hier stehen, nicht die tatsächlichen Kundennummern.

Wenn du zwei Variablen OAGKDNR und DAGKDNR hast, dann darfst du deren Namen nicht in Anführungszeichen setzen, sonst sind das einfache String-Literale.

[jagger]

@PhilS,

OMG, so was Blödes.   

Danke, das wars!

LG
jagger

[jagger]

...auf zum nächsten Problem, 

die Abfrage, die jetzt ausgeführt wird

Code [Auswählen] Erweitern


UPDATE tab_ex_kdnr SET tab_ex_kdnr.AGKDNR = [Forms]![mnu_Kunden]![txtLbxKunden], tab_ex_kdnr.ID = [Forms]![mnu_Kunden]![txtLbxKunden] & "-" & Nz([code]) & "-" & Nz([kdnr])
WHERE (((tab_ex_kdnr.AGKDNR) Like [Forms]![mnu_Kunden]![txtLbxKundenDuplikat]));


ändert nun entsprechend die aufgewählten Daten.

Wenn sich nun aber durch

Code [Auswählen] Erweitern


"... tab_ex_kdnr.ID = [Forms]![mnu_Kunden]![txtLbxKunden] & "-" & Nz([code]) & "-" & Nz([kdnr])..."

doppelte Werte im Primärindex ergeben, kommt
"Laufzeitfehler 3022 Die Änderungen, die Sie an der Tabelle angefordert haben, waren nicht erfolgreich, da sie doppelte Werte im Primärindex, Primärschlüssel oder in der Beziehung erstellen würden ..."

Diese Meldung kann man ja mit "On Error Resume Next" ausblenden.

Code [Auswählen] Erweitern


     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     
     Set db = CurrentDb
     Set qdf = db.QueryDefs("qry_Duplikatezusammenführen")
     qdf.Parameters("[Forms]![mnu_Kunden]![lbxKunden]") = OAGKDNR
     qdf.Parameters("[Forms]![mnu_Kunden]![lbxKundenDuplikat]") = DAGKDNR

On Error Resume Next
    qdf.Execute dbFailOnError
     
      If Err.Number = 3022 Then
      Me.Undo
      End If
   
qdf.Close: Set qdf = Nothing
Set db = Nothing


Wie kann man nun erreichen, dass die Abfrage die Datensätze ändert, die keine Verletzung darstellen und die anderen eifach übergeht?
Muss man da eine Schleife bauen? Wenn ja, wie?

Ich stehe da gerade mal wieder mächtig auf dem Schlauch.

LG
jagger

[PhilS]

doppelte Werte im Primärindex ergeben, kommt
"Laufzeitfehler 3022 Die Änderungen, die Sie an der Tabelle angefordert haben, waren nicht erfolgreich, da sie doppelte Werte im Primärindex, Primärschlüssel oder in der Beziehung erstellen würden ..."

Diese Meldung kann man ja mit "On Error Resume Next" ausblenden.

Hmm...
Die erste Frage, die ich stellen würde, ist, ob denn das Datenmodell so richtig ist.

Mit On Error Resume Next kann man vieles "ausblenden". - Sinnvoll ist das aber i.d.R. nicht.Besser wäre es, die Datensätze, von denen klar ist, dass sie nicht aktualisiert werden können, über ein entsprechendes Kriterium in der Where-Condition von der Aktualisierung auszunehmen.

Letztendlich führt das aber wieder zu der Gretchenfrage nach dem Datenmodell. Macht es Sinn, gewisse Daten in eine bestimmte logische Struktur zu überführen und andere nicht, weil es mal, mehr oder weniger zufällig, nicht passt?

[jagger]

Hallo PhilS,

dass die Frage nach dem Datenbankmodell kommt, war mir eigentlich schon klar. 

Ich will mal versuchen, es zu erklären.
Es gibt eine Agentur und die betreut verschiedene Kunden.
Sagen wir mal: P&C, Breunigner, Karstadt, Wöhrl, oder diverse Boutiquen.
Jeder Kunde hat in dem Programm eine eindeutige "AGKDNR" (Agenturkundennummer),
die vergibt das Programm.
"tab_Kunden": indiziert keine Duplikate.

Die Agentur verteibt Produkte (vermittelt Orderaufträge) von/für Lieferanten.
Sagen wir: Mustang, Lewi`s, Wrangler, CULTURE, CATNOIR,...
Bei diesen verschiedenen Lieferanten hat jeder Kunde natürlich auch seine eigene "kdnr" (Kundennummer), die vergibt der jeweilige Lieferant.
"tab_ex_kdnr": indiziert Duplikate möglich, denn es kann ja vorkommen, dass zwei verschiedene Kunden zufällig von zwei verschiedenen Lieferanten die selbe Kundennummer verpasst bekommen haben.

Weiterhin ist es oft so, dass ein Kunde von einem Lieferanten für unterschiedliche Unterkollektionen auch  die selbe Kundennummer bekommt. Deswegen gibt es in der "tab_ex_Kunden" noch ein eindeutige Feld "ID" welches sich aus der AGKDNR-code-kdnr zusammen setzt. Wobei "code" eine Zahl ist, die der jeweiligen Kollektion zugeordnet ist.

Die beiden Tabellen "tab_Kunden" und "tab_ex_Kunden" sind 1:n über die AGKDNR verknüpft.

Nun hat so ein Kunde oftmals meherer Standort bzw. Filialen. Einige Lieferanten vergeben für jeden Standort jeweils eine eigene Kundennummer und einige eben nicht.
Auch gibt es eigenständige Häuser, die einen eigene Einkauf haben, aber trotzdem über eine Kundennummer geführt werden.
Es gibt also jede Menge Möglichkeiten. Fakt ist, dass wenn ich im Programm nach z.B. Erfurt und Breuniger suche, ich dann bei dem Kunden (frm_Kunde) alle Informationen sehen will. Ansprechpartner, Telefonnummern und eben auch die Kundennummer.
(Bild01)

Es kommt im Eifer des Gefechtes (z.B. auf Messen) schon mal vor, dass die Agentur einen Kunden noch einmal eingibt/erfasst oder beim Einspielen von neuen Kundenlisten sich Duplikate ergeben. Das kann man nicht verhindern, denn oftmals geben die Kunden ihre Namen nicht richtig an oder bei der Erfassung wird geschludert.
Beispiel: P&C oder Peek & Cloppenburg oder Peek&Cloppenburg oder Peek und Cloppenburg,...

Oftmals werden solche Duplikate erst später entdeckt.
Und genau dafür ist das Tool "Duplikate zusammenführen" vorgesehen.
Der Anwender klickt im Oberen Listfeld "txtKunden" den Kunden (Datensatz) an, der bleiben soll und im unteren "txtKundenDuplikate" markiert er den, der weg kann.
(Bild02)

Hierbei werden alle Kundennummern des Duplikatdatensatzes dem zugeordnet, der übrigbleiben soll.
Das soll die Aktualisierungsabfrage erledigen.
Es wird (in der tab_ex_kdnr) die "AGKDNR" und die "ID" geändert. Da die "ID" ohne Duplikate indexiert ist, werden hier doppelte Einträge verhindert. Das heisst, die doppelten Kundennummern bleiben beim Duplikat.
Wenn man die Abfrage direkt ausführt und man sich durch die diversen msg-Boxen geklickt hat funtioniert das ja auch.
Es werden nur die Datensätze geändert, die nicht gegen die Regeln verstoßen, der Rest wird nicht geändert.

Wenn dann später das Duplikat gelöscht wird, werden die mit verschwinden (Löschweitergabe).

So, ich hoffe, ich konnte jetzt darlegen wie das so zusammenhängt. Ich glaube/hoffe schon, dass das Datenbankmodell richtig ist. ;-)

Nur fehlt mir jetzt immernoch der Ansatz wie ich die Abfrage ausführen kann und damit die Datensätze ändere.

Mit "DoCmd.SetWarnings False" möchte ich hier nicht arbeiten.

LG
jagger

[PhilS]

dass die Frage nach dem Datenbankmodell kommt, war mir eigentlich schon klar.

Ich stelle diese Frage selten. Sehr selten.
Hauptsächlich, weil ich es immer recht langweilig und aufwendig finde, ein Datenmodell zu diskutieren und selten Zeit und Geduld dafür habe. Daher im Folgenden auch eher ein paar kurze Hinweise als eine vollständige Analyse.

"tab_ex_kdnr": indiziert Duplikate möglich, denn es kann ja vorkommen, dass zwei verschiedene Kunden zufällig von zwei verschiedenen Lieferanten die selbe Kundennummer verpasst bekommen haben.

Dann sollte die Eindeutigkeit von Lieferant und KdNr zusammen durch einen entsprechenden Schlüssel über beide Felder sichergestellt werden. (Du kannst im Tabellendesign im Index-Dialog einen Index(namen) definieren und diesem Index mehrere Felder hinzufügen, um Eindeutigkeit über die Kombination der Felder zu erreichen; geht auch für den PK.)

Weiterhin ist es oft so, dass ein Kunde von einem Lieferanten für unterschiedliche Unterkollektionen auch  die selbe Kundennummer bekommt. Deswegen gibt es in der "tab_ex_Kunden" noch ein eindeutige Feld "ID" welches sich aus der AGKDNR-code-kdnr zusammen setzt. Wobei "code" eine Zahl ist, die der jeweiligen Kollektion zugeordnet ist.

Die tab_ex_Kunden.ID auf die AGKDNR und die übrigen Felder aufzubauen ist ein Normalisierungsfehler. (Redundanz)

Es wäre zu überlegen, ob die (Unter)kollektionen in ihre eigene Tabelle gehören.

Wenn nicht, sollte der KollektionsCode mit in den Schlüssel (s.o.) einbezogen werden.
Identifiziert denn ein Duplikat an dieser Stelle (hier entsteht ja scheinbar die Fehlermeldung) ein tatsächliches Duplikat in der Realität, oder nur ein zufälliges, technisches Duplikat, weil derselbe "Code" mehrfach für verschiedene Unterkollektionen vergeben wurde?

Wenn die Kollektionen in ihre eigene Tabelle gespeichert werden und dort ihren eigenen eindeutigen Schlüssel bekommen, kann es eigentlich keine rein "technischen Duplikate" geben.

Die "fachlichen (realen) Duplikate" müssen aufgelöst werden, indem die Daten aus mehreren Duplikatsdatensätzen in einem einzigen zusammengefasst werden. - Ob das autmatisch oder manuell passieren muss, musst du selbst beurteilen.

Das Feld ID in der tab_ex_Kunden kann entweder durch eine AutoNummer ersetzt werden oder komplett gelöscht werden. - Weitgehend Geschmackssache; die Auto-Id vereinfacht die technische Identifizierung in Abfragen/Code ist aber streng genommen nicht erforderlich, weil die Kombination Lieferant-LieferantKdNr-KollektionCode ja bereits eindeutig ist.

Nun hat so ein Kunde oftmals meherer Standort bzw. Filialen. Einige Lieferanten vergeben für jeden Standort jeweils eine eigene Kundennummer und einige eben nicht.

Das könnte man über die Eingabe einer optionalen Standort-KundenNr in einer Standort-Tabelle lösen. - Die Anforderung an die Standort-Darstellung geht für mich aber nicht klar genug aus deiner Beschreibung hevor. (Vielleicht ist auch nur meine Geduld für Datenmodell-Diskussionen erschöpft (s.o.).)

Es werden nur die Datensätze geändert, die nicht gegen die Regeln verstoßen, der Rest wird nicht geändert.

Das ergibt für mich keinen Sinn. Entweder sind dann die Regeln für Eindeutigkeit (bzw. das DB-Design) falsch, oder die Logik für die Zuordnung/Verarbeitung der Duplikate. - So sind wir zur Frage nach dem Design gekommen.

[jagger]

Hallo PhilS,

vorab erstmal vielen Dank für Deine Mühe, Dich durch die lange und umfangreiche Beschreibung zu kämpfen.

Kurz noch einige Anmerkungen von mir:

Dann sollte die Eindeutigkeit von Lieferant und KdNr zusammen durch einen entsprechenden Schlüssel über beide Felder sichergestellt werden.

Das macht doch die "ID": AGKDNR-code-kdnr.
Der Lieferantencode muss hier mit rein, da ein Kunde mehrfach die selbe Kundennummer haben kann.
Kunde: Müller AGKDNR: XYZ2345
kdnr: 1234 von AntonsHose code:12; ID =XYZ2345-12-1234
kdnr: 1234 von AntonsHose(Bermudas) code:32; ID =XYZ2345-32-1234
Kdnr: 1234 von MarionsRockfabrik code:44; ID =XYZ2345-44-1234

Der Lieferantencode ist eindeutig in der "tab_ex_Lieferanten" hinterlegt, also jede Kollektion hat eine eindeutige Nummer (code).
Diese ID wird beim Erfassen der Umsätze im "frm_Ums_erfassen" jedem Umsatz zugeordnet.
Bei späteren Auswertungen kann man also jeden Umsatz immer genau zuordnen.

Das funktioniert schon seit Jahren super und alle Nutzer (Kollegen, die täglich damit arbeiten) sind damit sehr glücklich und zufrieden.

Die "fachlichen (realen) Duplikate" müssen aufgelöst werden, indem die Daten aus mehreren Duplikatsdatensätzen in einem einzigen zusammengefasst werden. - Ob das autmatisch oder manuell passieren muss, musst du selbst beurteilen.

Durch unkonzentrierte Arbeit oder unkorrekte Dateneingabe (unterschiedliche Schreibweisen) oder das Einpflegen von zusätzlichen Kundenlisten kann es passieren, das ein Kunden mehrfach angelegt wurde.

Kunde: Mueller AGKDNR: XYZ6789
kdnr: 1234 von AntonsHose code:12; ID = XYZ6789-12-1234
kdnr: 1234 von AntonsHose(Bermudas) code:32; ID = XYZ6789-32-1234
Kdnr: 3333 von RenatesBlusenfabri code:77; ID = XYZ6789-77-3333
kdnr: 5454 von RolandsGürtelparadies code: 55; ID = XYZ6789-55-5454

Oftmals wird so ein doppelter Kundendatensatz erst nach langer Zeit entdeckt. Manchmal werden solche "Duplikate" auch bewusst angelegt. Weil der eine Kollege mit dem Kunden in der Abteilung Frauenmode arbeitet und ein andere in der Abteilung Männermode. Kommt in der Praxis oft vor.
Wenn nun so eine Agentur seine Struktur ändert und jetzt nur noch ein Kollege für den Kunden zuständig ist oder weil man den "doppelten" Kunden zu einem Datensatz zusammenfassen will, soll aus Müller und Mueller eben Müller werden. Also die AGKNDR von Mueller wird durch die von Müller ersetzt. Das geschieht auch in der ID.

Kunde: Müller AGKDNR: XYZ2345
kdnr: 1234 von AntonsHose code:12; ID = XYZ2345-12-1234
kdnr: 1234 von AntonsHose(Bermudas) code:32; ID = XYZ2345-32-1234
Kdnr: 1234 von MarionsRockfabrik code:44; ID = XYZ2345-44-1234
Kdnr: 3333 von RenatesBlusenfabri code:77; ID = XYZ2345-77-3333
kdnr: 5454 von RolandsGürtelparadies code: 55; ID = XYZ2345-55-5454

Der "Rest" von Mueller wird dann später gelöscht.

Kunde: Mueller AGKDNR: XYZ6789
kdnr: 1234 von AntonsHose code:12; ID = XYZ6789-12-1234
kdnr: 1234 von AntonsHose(Bermudas) code:32; ID = XYZ6789-32-1234

Die neue Zuordnung von allen Telefonnummern, Notizen, Dokumentenanhängen, Lieferanschriften, Rechnugsanschriften,... (BildBez01) wird in den entsprechenden Tabellen auch durch den Austausch der AGKDNR gelöst.

Diese Arbeit musste ich dann immer händisch durchführen.
Diese Arbeit soll mir das Tool "Duplikate zusammenführen" abnehmen. Zumal ich auch nicht entscheiden kann, ob nun Müller weg soll oder Mueller. Das wissen die Kollegen in den einzelnen Bundesländern und Verkaufsgebieten schon besser. Es sind ja schließlich ihre Kunden und ihre Daten. ;-)
Alles funtioniert super, bis auf die Kundennummern.

Also zurück zur eigentlichen Frage. Kann man die Aktualiesierungsabfrage so durchlaufen lassen, dass alle Änderungen ohne Fehlermeldung (3022) gemacht werden.
Oder kann man vorher nur die Daten ermitteln, die bei der Aktualisierung keine Regelverletzung darstellen?
Oder gibt es einen anderen Ansatz?

Mit bestem Dank im Voraus

LG
jagger

[PhilS]

Das macht doch die "ID": AGKDNR-code-kdnr.
Der Lieferantencode muss hier mit rein, da ein Kunde mehrfach die selbe Kundennummer haben kann.

Ob Lieferantencode und KollektionCode dasselbe oder verschiedene Felder sind war mir bei meiner vorigen Antwort nicht klar und ist es auch jetzt noch nicht. Das ist aber nur von untergeordneter Bedeutung.
Bzgl. Eindeutigkeit über mehrere Felder lies nochmal den fettgedruckten Teil meiner vorigen Antwort.

Deine Id ist in jedem Fall ein Normalisierungsfehler, ein doppelter sogar.
1.) Weil die Teil-Daten nicht atomar sind und sie somit gegen die 1. Normalform verstößt.
2.) Weil sie redundant die Daten anderer Felder enthält und sie somit gegen die 2. Normalform verstößt.

Also zurück zur eigentlichen Frage. Kann man die Aktualiesierungsabfrage so durchlaufen lassen, dass alle Änderungen ohne Fehlermeldung (3022) gemacht werden.
Oder kann man vorher nur die Daten ermitteln, die bei der Aktualisierung keine Regelverletzung darstellen?

Zurück zur eigentlichen Antwort. ;-)
Du kannst nur Daten aktualisieren, bei denen für die neue AGKDNR und den bestehenden code und kdnr maximal ein Datensatz existiert. - Das kann man ja in der Where-Condition der Abfrage prüfen.
[/quote]

[MzKlMu]

Hallo,

Bei diesen verschiedenen Lieferanten hat jeder Kunde natürlich auch seine eigene "kdnr" (Kundennummer), die vergibt der jeweilige Lieferant.
"tab_ex_kdnr": indiziert Duplikate möglich, denn es kann ja vorkommen, dass zwei verschiedene Kunden zufällig von zwei verschiedenen Lieferanten die selbe Kundennummer verpasst bekommen haben.

Ohne jetzt auf die fachlichen Hinweise von Philipp einzugehen.
Solche Konstellationen sind ein typischer Fall für n:m Beziehungen in denen über eine Zwischentabelle einem Artikel beliebig viele Lieferanten und deren eigenen Bezeichnungen und Artikelnummern zugeordnet werden. Und zwar relativ einfach und für jede Kombination eindeutig
Das würde dann auch den Normalisierungsregeln entsprechen.

Im Beziehungsbild aus #22 ist aber keine einzige n:m Beziehung zu erkennen.
n:m Beziehungen enthalten mindesten 2 Fremdschlüsselfelder.

[jagger]

allo PhilS,

Du kannst nur Daten aktualisieren, bei denen für die neue AGKDNR und den bestehenden code und kdnr maximal ein Datensatz existiert. - Das kann man ja in der Where-Condition der Abfrage prüfen.

Wie kann ich das machen? Finde keinen Ansatz? 

LG
jagger

[jagger]

@PhilS,

mit

Code [Auswählen] Erweitern

UPDATE tab_ex_kdnr SET tab_ex_kdnr.AGKDNR = [Forms]![mnu_Kunden]![lbxKunden], tab_ex_kdnr.ID = [Forms]![mnu_Kunden]![lbxKunden] & "-" & Nz([code]) & "-" & Nz([kdnr])
WHERE (((tab_ex_kdnr.AGKDNR) Like [Forms]![mnu_Kunden]![lbxKundenDuplikat] Or (tab_ex_kdnr.AGKDNR)=[Forms]![mnu_Kunden]![lbxKunden]) AND (([Forms]![mnu_Kunden]![lbxKunden] & "-" & Nz([code]) & "-" & Nz([kdnr])) Not In (SELECT  tab_ex_kdnr.ID
FROM tab_ex_kdnr
WHERE (((tab_ex_kdnr.AGKDNR)=[Forms]![mnu_Kunden]![lbxKunden]));)));

funktioniert es wie es soll! Durch die Unterabfrage kann ich die "IDs" ermitteln, die schon existieren und somit verhindern, das die Aktualisierungsabfrage hier Regelverletzungen (Laufzeitfehler 3022) produziert!

Vielen Dank für Deine Mitarbeit, Unterstützung und Zeit!
Danke auch an die anderen Hinweisgeber.

LG
jagger