Abfrage: Verknüpfung ändern

[Christian72]

Guten Morgen,
 
meine Datenbank wird verwendet zum Schreiben von Angeboten, Auftragsbestätigungen, Lieferscheinen und Rechnungen für einen kleinen Handel für Messtechnik; habe diesen vor gut einem Jahr vom Vorgänger übernommen.
Jedem Kunden (Tabelle: "Firma") werden Zahlungs- und Lieferbedingungen (Tabelle: "Zahlungsziel-Frachtkosten") zugewiesen.
Verknüpft sind beide Tabellen über das Attribut "Firmasuch". Das hat folgenden Inhalt:
Firmenname + Bindestrich + Ort (der Firma)
Also zum Beispiel:
 - Volkswagen-Wolfsburg
 - Allianz-München
 - Airbus-Hamburg
 
Dieses Attribut soll komplett aus der Datenbank entfernt werden.
Es ist in einer Reihe von Tabellen und Abfragen und Formularen enthalten.
Anfangen möchte ich mit den oben genannten Zahlungsbedingungen. 
Ich habe dazu in der Tabelle "Zahlungsziel-Frachtkosten" ein neues Attribut "firma_id" ergänzt (siehe: Anhang 1) und dieses mit dem Attribut "Schlüssel" in der Tabelle "Firma" verknüpft (siehe: Anhang 2).
Habe gestern anschließend eine Rechnung erzeugt, was (erwartungsgemäß) nicht funktioniert hat.
Als ich bei "firma_id" den "Schlüssel" aus "Firma eingetragen habe, hat es geklappt.
Die nächsten beiden Schritte sollen sein:
 - Löschen der Verknüpfung von "Firmasuch" zu "Firmasuch"
 - Löschen des Attributes "Firmasuch" aus der Tabelle "Zahlungsziel-Frachtkosten"
 
Vorab aber folgende Fragen:
 - Ist die Vorgehensweise insgesamt sinnvoll?
 - Ist der Name des neuen Attributs "firma_id" gut gewählt?
 - Gibt es eine Möglichkeit alle Einträge von "Schlüssel" in der Tabelle "Firma" auf einmal nach "firma_id" in "Zahlungsziel-Frachtkosten zu kopieren?
 
Vielen Dank vorab für eure Antworten!
 
Christian
 

[Doming]

Moin Christian,
das wird so nicht klappen, denn von selber wird sich Dein Feld firma_id nicht füllen.
Also vorweg, da es sich bei dem Feld um den Primärschlüssel einer anderen Tabelle handelt, würde ich ihn mit dem Präfix FS versehen, also z.B. FS_firmID.
Dann das Feld mit einer Aktualisierungsabfrage füllen, um den Schlüssel aus der Firmentabelle dort einzufügen. Danach kannst Du dann die Beziehung ,,Firmasuch" dort entfernen.

Gibt es einen Grund, warum zwischen den Tabellen keine referentielle Integrität eingestellt ist?
Gruß
 Doming

[Bitsqueezer]

Hallo Christian,

ja, das "Firmasuch" gegen eine ID auszutauschen, ist auf jeden Fall sinnvoll, da "Name-Ort" kaum wirklich eindeutig ist (was machst Du bei 3 Standorten einer Firma im gleichen Ort?).

"firma_id" ist schon OK, ich persönlich verwende lieber "ID_Firma" (immer "ID_" führend). Wenn Du andere fragst,  hängen einige auch immer "_fk" für Foreign Key an den Namen, was ich wiederum für immer überflüssig halte.

Umgekehrt sollte der PK (Primary Key) aber ebenfalls so heißen. Ganz sicher nicht "Schlüssel" (auch nicht "ID").

Aber die Datenbank scheint dahingehend eh chaotisch zu sein, was Du nicht auf die Schnelle bereinigen kannst.
"Primärschlüssel Lieferadresse" bzw. "..Rechnungsadresse" als FK-Name in einer Bestelltabelle.. total daneben.

Namen mit Leer- und Sonderzeichen, etwa "Bestell-Nr" (wieso heißt eine Tabelle wie ein Feld? Sollte wohl eher "Bestellungen" oder noch besser "tblBestellungen" heißen), "P/N", "Serien - Nummer" uvm. - Namen sollten grundsätzlich keine Leer- und Soderzeichen beinhalten, nur Buchstaben, Zahlen, Unterstrich.
Gleiches gilt für "Rechnung-Nr" und andere solcher Namen als Tabellennamen.

ID-Felder (PKs und i.d.R. auch FKs) sollten immer an den Anfang der Tabelle. Den Grund siehst Du an den vielen Scrollbalken in Deiner Abfrage, weil das verbundene Feld jeweils weiter unten als die sichtbaren Felder steht.

"Umsatz 1991", "Umsatz 1992" usw.: Aufzählungsfelder, gehören nicht in Spalten, sondern in eine eigene Tabelle. Mit "firma_id", "Jahr", "Umsatz" und ggf. noch weitere.

"Preis 2024", "EK 2024"...

"Kunden Nr 2" und "Kundennr2"...
Besonders schön finde ich "Brange"... Sollte wohl eher "Branche" heißen...

Diverse der Tabellen haben nicht einmal einen PK, den sollte man immer erstellen.

Das waren nur mal eine Reihe der Dinge, längst nicht alles.

Gibt es eine Möglichkeit alle Einträge von "Schlüssel" in der Tabelle "Firma" auf einmal nach "firma_id" in "Zahlungsziel-Frachtkosten zu kopieren?

Ja, natürlich.
Solange "Firmasuch" ein eindeutiges Ergebnis erzeugt, also nicht mehr als eine Firma für jeden Eintrag der Zahlungsziel-Frachtkosten-Tabelle, kannst Du das mit einer UPDATE-Anweisung erledigen.

Am einfachsten ist, eine Abfrage zwischen den beiden zu erstellen mit ID, Firmasuch, Schlüssel, Kundennr, Firma als Ausgabefelder (zur Kontrolle) und erst mal anzuschauen, was dabei herauskommt.

Ist die Zuweisung in Ordnung, kannst Du im Designer die Abfrage auf eine UPDATE-Abfrage umstellen und dann den Wert von "Schlüssel" in "firma_id" schreiben.
Da das Feld neu ist, macht es erst einmal nichts, wenn es nicht korrekt funktioniert, kannst Du dann ja jederzeit ändern ohne Auswirkungen.

Wenn das Feld gefüllt ist, kannst Du die gleiche SELECT-Abfrage nochmal machen, nur verbindest Du dann nicht mehr Firmasuch, sondern firma_id mit Schlüssel und schaust Dir an, ob das Ergebnis stimmt. Dabei sollte die Anzahl Datensätze passen (Schnelltest) und zur näheren Prüfung der Inhalt.
Wenn alles OK ist, kannst Du die Referenz zwischen Firmasuch entfernen (im Beziehungseditor, nicht wie hier gezeigt in einer Abfrage). Wenn der Beziehungseditor keine Beziehungen anzeigt, ist es noch chaotischer. Einige denken, man könne gute Datenbanken auch einfach ohne Beziehungen bauen, was aber eine Menge Potential der Datenbanken verschenkt. Insbesondere die Einstellung zur referentiellen Integrität ist wichtig für stets saubere Daten.

Übrigens muß man auch nicht "Firmasuch" als Feld in der Tabelle führen. Das erstellt man bei Bedarf einfach aus Firma und Ort in einer Abfrage (z.B. für eine Kombobox für ein Suchfeld). Kann man dann auch generell überall entfernen, sobald vernünftige Beziehungen über ID-Felder aufgebaut wurden.

Gruß

Christian

[MzKlMu]

Hallo,
ich habe Dir bereits am 23. Oktober 2025 schon einiges zum Datenmodell geschrieben.

- Es fehlt referentielle Integrität
- Für die Verknüpfungen/Beziehungen werden zum Teil nicht die Primärschlüsselfelder verwendet.
- Die Verknüpfung Schlüssel zu Schlüssel ist überflüssig, da die Bestell-Nr. eindeutig ist.
- Eines der Felder "Schlüssel" ist dann zu zu löschen. Wahrscheinlich in der Tabelle "Einzelangaben_Auftrag".
- Die Felder Gesamtpreis und Summen sind in Tabellen zu löschen, solcher Felder werden im Regelfall nur berechnet.
- Felder für DM sind überflüssig, die DM lassen sich aus Euro berechnen.
- Für die Umsätze nach Jahren ist eine extra Tabelle erforderlich-
- Für die Ansprechpartner ist eine extra Tabelle erforderlich.
- Die Preise müssen in eine eigne Tabelle. Was machst Du z.B. für die Preise 2025 ein neues Feld ?
- Die Feldnamen enthalten Leer und Sonderzeichen (-)
- Die Feldnamen sind teilweise viel zu lang.

Davon hast Du leider bis heute nichts umgesetzt.

Neu dazu:
- Für die Mahnungen ist eine extra Tabelle erforderlich
- Redundante Felder
- Für die MWST ist eine extra Tabelle erforderlich

[Christian72]

Hallo Doming, hallo Bitsqueezer, hallo MzKIMu,
 
vielen Dank für eure Tipps!
 
@ MzKIMu:
Deine Hinweise vom letzten Herbst sind nicht vergessen! Danke auch für diese. Es ist nur so, dass meine Hauptaufgabe ist, Messgeräte zu verkaufen. Die Pflege der Datenbank muß nebenbei laufen. Kann mich leider nicht 1/2 Jahr einschließen. Es ist auch nicht so, dass ich nichts an der Datenbank gemacht habe; im Gegenteil. Habe nur erstmal die Sachen gemacht, die für mich am einfachsten sind. Deine Tipps werden auf jeden Fall noch abgearbeitet.
 
Zum aktuellen Punkt:
- Beim Attributnamen habe ich mich entschieden für: ID_Firma
- ID-Felder wurden nach oben geschoben.
- Im Beziehungsentwurf gab es zwischen den Tabellen "Firma" und "Zahlungsziel-Frachtkosten" bislang keine Beziehung. Diese gibt es nun (siehe: Anhang).
- Referentielle Integrität: Aktuell nicht einstellbar. Da muß ich wohl vorher noch aufräumen.

Melde mich wieder hierzu
 
Gruß
 
Christian
 

[Bitsqueezer]

Hallo Christian,

referentielle Integrität läßt sich erst herstellen, wenn der PK eindeutig ist (sollte der Fall sein, sonst wäre kein PK-Symbol davor) und alle Werte, die im FK enthalten sind, auch im PK der anderen Tabelle vorhanden sind. Genau das ist ja die Integrität, daß die Datenbank Dir das absichert.

Code [Auswählen] Erweitern

SELECT *
FROM Zahlungsziel-Frachtkosten AS ZF
WHERE NOT EXISTS (SELECT 1 FROM Firma WHERE Firma.[Schlüssel] = ZF.ID_Firma)
Damit findest Du die Einträge in der ZF-Tabelle, die eine Firmen-ID haben, die es nicht in der Firmentabelle gibt. Die mußt Du dann entweder in Firma nachtragen oder aus ZF löschen, dann kannst Du die Integrität herstellen.

Gruß

Christian

[Christian72]

Guten Morgen Bitsqueezer,
 
Danke für die Abfrage. 
Habe die ausführen lassen und es kamen 41 Einträge.
Habe diese gelöscht aus der Tabelle "Zahlungsziel-Frachtkosten".
nochmaliges Ausführen der Abfrage => kein Ergebnis
Im Beziehungsentwurf ließ sich bei "Mit referentieller Integrität" immer noch kein Haken setzen.
Beziehung gelöscht und neu erstellt. => keine Veränderung
:-/
Liegt es eventuell am Datenformat?
Es sind allerdings beides "Long Integer" (siehe: Anhang).
 
Vielen Dank nochmal
 
Christian
 

[Bitsqueezer]

Hallo Christian,

41 ist schon ganz schön viel. Das bedeutet übersetzt, Du hast für 41 Firmen-IDs keine Firma in den Firmen-Stammdaten hinterlegt.
Da Du ja gesagt hast, das ID-Feld sei neu, frage ich mich, wie es zu den 41 Einträgen kam? Hast Du die Tabellen dahingehend mal inhaltlich überprüft?

Du solltest mal eine Abfrage mit den beiden Tabellen erstellen und alle Felder ausgeben lassen, verknüpft über ID_Firma zu Schlüssel. Passen die Einträge dann überhaupt zueinander?

Das Problem bei ID-Verknüpfungen ist, daß man auch leicht die falschen ID-Werte eintragen kann. Bevor Du also Daten löschst, die vielleicht noch gebraucht werden, erst mal die ID checken. Mal die alte Verknüpfung über FirmaSuch dagegenstellen und vergleichen.

Genau deswegen ist referentielle Integrität gleich zu Beginn des DB-Designs so wichtig, damit ID-Werte immer zueinander passen und garantiert ist, daß es einen PK-Eintrag in der PK-Tabelle gibt zu den FK-Einträgen anderer Tabellen.

Von Deinen Screenshots her sieht alles korrekt aus, PK, AutoID, Long Integer auf beiden Seiten. Du kannst die gleiche Abfrage auch umgekehrt formulieren, also die Firmentabelle ausgeben WHERE NOT EXISTS mit der anderen Tabelle. Wobei in den Stammdaten natürlich mehr drin sein können als in den Zahlungsziel-Daten, weil man ja nicht zwingend für jede Firma dort einen Eintrag haben muß.

Die RI kann nicht hergestellt werden, wenn die Eindeutigkeit der Verbindung nicht hergestellt werden kann. In Deinem Fall die ID-Werte auf beiden Seiten.

Oh, ich sehe da gerade noch ein Problem, was genau das verhindert: Du hast den Index für ID_Firma auf "Ohne Duplikate" eingestellt. Das würde eine 1:1-Verbindung erzeugen, dann muß es in beide Richtungen genau einen passenden ID-Eintrag geben. Also jede Firma hat genau ein Zahlungsziel. Firmen ohne Zahlungsziel sind dann nicht erlaubt.

Stelle das auf "Duplikate" um, dann ist es eine normale 1:n-Beziehung. Eine 1:1-Beziehung ist in den seltensten Fällen notwendig und viel zu schwierig zu handhaben. 1:n wäre für den Zweck auch sinnvoller, wenn Du etwa ein Gültigkeitsfeld verwendest, dann kann jede Firma mehrere haben mit einem Gültigkeitszeitraum. Oder, wenn es um Artikel geht, in Abhängigkeit eines Artikels verschiedene Zahlungsziele, usw.
Wenn Du garantieren willst, daß es immer nur einen Eintrag geben darf, kannst Du das auch im Frontend (im Formular) abfangen, ohne eine 1:1-Beziehung zu erzwingen.

Oft genügt es aber auch, wenn ohnehin eine 1:1-Beziehung besteht, die entsprechenden Felder direkt in die Firmentabelle einzutragen, dann hast Du den Stress nicht.

Gruß

Christian

[Christian72]

Hallo Bitsqueezer,
 
vielen Dank für die ausführliche Antwort.
Habe bei ID_Firma auf "Duplikate" umgestellt.
Im Beziehungsentwurf ließ sich bei "Mit referentieller Integrität" immer noch kein Haken setzen.
Dann habe ich in der Backend-Datei in den Beziehungsentwurf geschaut.
Vorweg: Bislang hatte ich immer und ausschließlich im Frontend den Beziehungsentwurf bearbeitet.
Beide Beziehungsentwürfe sehen unterschiedlich aus!
Im Backend-Beziehungsentwurf gab es die Tabelle "Firma"; "Zahlungsziel-Frachtkosten" aber nicht.
Habe diese Tabelle ergänzt, eine Verknüpfung erstellt und siehe da:
Bei "Mit referentieller Integrität" ließ sich der Haken setzen.
:-)
Nachteil: Im Frontend-Beziehungsentwurf gibt es nun eine neue Tabelle "Firma_1" die eine Verküpfung zu "Zahlungsziel-Frachtkosten" hat (siehe: Anhang). Diese neue Tabelle gibt es nur im Beziehungsentwurf; nicht in den Access-Objekten.
So ´ne Datenbank ist ´ne ganz schöne Diva, muß ich mal sagen.
Spaß beiseite:
Macht es überhaupt Sinn, in beiden Dateien Beziehungsentwürfe zu haben und dann auch noch unterschiedliche?
Vielen Dank nochmal
 
Christian
 

[Knobbi38]

Hallo Christian72,

mal eine Frage:
Kann eine Firma mehrere verschiedene Zahlungsziel-Frachtkosten haben?

Knobbi38

[Bitsqueezer]

Hallo,

OK, wenn Du solche Feinheiten nicht mitteilst, kann man schlecht eine Aussage treffen.

Wenn Du ein Backend hast, gehören ALLE Tabellen in das Backend. Ausnahmen sind lediglich ausschließlich lokal genutzte Tabellen (z.B. für Usersettings etc.). Die dann aber auch nie eine Beziehung zu Tabellen im Backend haben!

Beziehungen werden ausschließlich im Backend erstellt, alles andere sind schlicht keine Beziehungen, sondern nur "Abfragevorlagen".

Access hat die unselige Angewohnheit, das Beziehungsfenster für beides zu nutzen. Du kannst hier also auch "Verbindungen zwischen Tabellen" erstellen, die einfach nur einen JOIN darstellen wie in einer Abfrage. Da Du die gleiche Tabelle für verschiedene Verbindungen erstellen kannst, erzeugt Access automatisch ein künstliches "_1" usw. für jedes weitere Vorkommen. Die Tabelle ist dennoch immer die gleiche.

Vergiß dieses - sorry MS - idiotische Feature von Access, der Beziehungseditor soll nur verwendet werden, wenn man echte Beziehungen erstellen will, der ist auch so schon "eng" genug, weil man nur ein Diagramm haben kann. Man kann leider auch nicht im Diagramm unterscheiden, ob es eine echte Beziehung ist oder nicht. Wenn es ein "JOIN-Entwurf" ist (den neue Abfragen dann als Vorlage verwenden), kannst Du natürlich nie eine RI einstellen.

Also: Alles lokale im Beziehungseditor rauswerfen und alles korrekt im Backend erstellen. Dann gibt es auch keine RI-Probleme.

Und: Sobald Du eine Tabelle mit "_1" usw. siehst, ist schon was falsch. In einem Beziehungseditor kommt jede Tabelle nur genau einmal vor.

Gruß

Christian