Abragen über mehrere Tabellen

[gsaccess]

Ich versuche seit Stunden Abfragen zwischen mehreren Tabellen zu erstellen. zB(tblAuftrag,tblRechnung,tblKunden).
tblAuftrag...
zB In der Tabelle tblAuftrag werden die Aufträge erfasst.
tblRechnung
In der Tabelle tblRechnung die Rechnungen.
Im Feld ReArtID_F werden die verschiedenen Rechnungsarten (eigene Tabelle tblRechnungsart) zB 2 für Teilrechnung erfasst.
Mit Erstellen einer neuen Rechnung wird in tblAuftrag die Rechnungsnummer in RechnungID_F geschrieben. Es können mehrere Aufträge in einer Rechnung abgerechnet werden.
qryKundeAdressePlz
liefert die Kundennamen und Adressen der Kunden aus den Kundentabellen da ich auch den Kundennamen immer wieder brauche.
Problem 1:
In der tblRechnung gibt es 134 Datensätze mit ReArtID_F =2.
Wenn ich aber in meiner Abfrag das Feld ReArtID_F mit dem Kriterium 2 abfrage erhalte ich nur 53 Datensätze.SQL Code der Abfrage:

Code [Auswählen] Erweitern

SELECT tblAuftrag.AuftragNrID, tblAuftrag.KundenID_F, qryKundeAdressePLZ.KundenNr, qryKundeAdressePLZ.Kundenname, tblAuftrag.Auftragsbeschreibung, tblRechnung.RechnungID, tblRechnung.RechnungNr, tblRechnung.ReArtID_F
FROM (tblAuftrag LEFT JOIN tblRechnung ON tblAuftrag.RechungID_F = tblRechnung.RechnungID) LEFT JOIN qryKundeAdressePLZ ON tblAuftrag.KundenID_F = qryKundeAdressePLZ.KundenID
GROUP BY tblAuftrag.AuftragNrID, tblAuftrag.KundenID_F, qryKundeAdressePLZ.KundenNr, qryKundeAdressePLZ.Kundenname, tblAuftrag.Auftragsbeschreibung, tblRechnung.RechnungID, tblRechnung.RechnungNr, tblRechnung.ReArtID_F
HAVING (((tblRechnung.ReArtID_F)=2))
ORDER BY tblRechnung.RechnungID DESC;
Problem 2:
Für das Erstellen einer Teilrechnung brauche ich die letzte TR und die letzte Schlussrechnung des Kunden.
Die letzte TR und SR aus tblRechnung ist kein Problem. SQL Code der Abfrage:

Code [Auswählen] Erweitern

SELECT Max(IIf([ReArtID_f]=3,[rechnungNr],0)) AS letzteSR, Max(IIf([ReArtID_f]=2,[rechnungnr],0)) AS letzteTR
FROM tblRechnung;
Ich schaffe es aber nicht die Kundennummer aus der tblAuftrag zu integrieren.
Versuch über VBA:

Code [Auswählen] Erweitern

Dim letzteTRNr As Variant
letzteTRKunde = DLookup("RechnungID", "tblrechnung", "ReArtID_F like'2' and 'tblAuftrag.KundenID_F like 'me.kunden_nr'")
Siehe Fehlermeldung.

Vielleicht kann mir jemand helfen und meine ev. Denkfehler aufklären

Gruß Günther

[Knobbi38]

Hallo Günther,


ich fürchte, hier hast du zunächst ein Problem mit deinem Datenmodell. In einer Rechnung wird der Verweis auf einen Auftrag eingetragen, nicht umgekehrt. Damit erledigt sich auch dein Problem mit Teilrechnungen/Lieferungen, nach dem Motto: Auftrag (1 : n) Rechnungen.

Ein anderes Thema ist, daß die Kundendaten nicht per Fremdschlüssel, sondern immer als Kopie in eine Rechnung kopiert werden. Eine Rechnung darf sich nicht automatisch mit ändern, wenn die Kundenstammdaten z.B. wegen einer Adressänderung geändert werden. Einen Verweis auf die aktuellen Kundenstammdaten wäre zwar redundant, könnte man aber später trotzdem nochmal gebrauchen und wäre somit hilfreich.

Somit würden sich deine Problem in Luft auflösen.

Du hast aber noch ein Verständnisproblem mit dem Like-Operator. Erstens gibt der Operator ohne Platzhalter keine Sinn und im in einer SQL-Anweisung werden nur Texte in Hochkomma eingeschlossen, numerische Inhalte nicht.

Code [Auswählen] Erweitern

and 'tblAuftrag.KundenID_F like 'me.kunden_nr'

Nimmt man es genau, suchst du im Feld KundenID_F nach eine Text "me.kunden_nr". Gibt das für dich einen Sinn? 

Gruß
Knobbi38 

[gsaccess]

Hallo Ulrich,

danke für deine ausführliche Antwort.

ich fürchte, hier hast du zunächst ein Problem mit deinem Datenmodell. In einer Rechnung wird der Verweis auf einen Auftrag eingetragen, nicht umgekehrt. Damit erledigt sich auch dein Problem mit Teilrechnungen/Lieferungen, nach dem Motto: Auftrag (1 : n) Rechnungen.

Wie ich geschrieben habe, werden häufig mehrere Aufträge in einer Rechnung abgerechnet. Dass es zu einem Auftrag mehrere Rechnungen gibt ist bisher nicht vorgekommen, könnte ich aber mit berücksichtigen.
Könnt ich das mit einer Zwischentabelle, in der jeweils die Rechnungsnummer und die Auftragsnummer eingetragen wird, lösen? Oder macht das keinen Sinn. eine 1:n Beziehungen wird also da nicht funktionieren.

Ein anderes Thema ist, daß die Kundendaten nicht per Fremdschlüssel, sondern immer als Kopie in eine Rechnung kopiert werden. Eine Rechnung darf sich nicht automatisch mit ändern, wenn die Kundenstammdaten z.B. wegen einer Adressänderung geändert werden. Einen Verweis auf die aktuellen Kundenstammdaten wäre zwar redundant, könnte man aber später trotzdem nochmal gebrauchen und wäre somit hilfreich.

Ist ein guter Tipp. Werde ich versuchen umzusetzen und die Rechnungsadresse und Lieferadresse in die Rechnung kopieren.
Wenn ich das richtig verstandnen habe, müsste ich dann aber jede Rechnungs- und Lieferadresse in der tblRechnung nochmals speichern. Damit werden gleiche Dateien 2 x gespeichert. (Redundanz?)

Nimmt man es genau, suchst du im Feld KundenID_F nach eine Text "me.kunden_nr". Gibt das für dich einen Sinn?

Das macht wirklich keinen Sinn.
Aber der code ohne Hochkomma bringt auch Fehler.

Code [Auswählen] Erweitern

letzteTRKunde = DLookup("RechnungID", "tblrechnung", "ReArtID_F = 2 and tblAuftrag![KundenID_F] = & me.kunden_nr")Funktioniert dlookup überhaupt über 2 Tabellen. Im Netz finde ich nur codezeilen bei denen nach 2 Kriterien derselben Tabelle gesucht wird. Oder ist der code auch so noch nicht richtig?

Gruß Günther

[MzKlMu]

Hallo,
wenn es mehr als eine Tabelle ist, musst eine Abfrage verwenden mit beiden Tabellen.

[Knobbi38]

Hallo Günther,

"ReArtID_F = 2 and tblAuftrag![KundenID_F] = & me.kunden_nr"

Das kaufmännische Und-Zeichen ist der Verkettungsoperator für Strings, hier also vollkommen fehl am Platz. Außerdem kann Dlookup den Ausdruck me.Kunden_Nr nicht auswerten, für Dlookup ist das einfach nur ein String und führt damit zum Syntaxerror. Für solche Angaben muss der String zusammengesetzt werden:

Code [Auswählen] Erweitern

"ReArtID_F = 2 and tblAuftrag![KundenID_F] = " & me.kunden_nr
Wie in der Doku beschrieben, hat Dlookup hier nur die Tabelle "tblrechnung" im Zugriff, wie soll dann das Kriterium aus "tblAuftrag" ausgewertet werden. Entweder du machst das mit einem  SQL Aufruf oder aber du verwendest für Dlookup eine Query mit einem Join über beide Tabellen. (#3)

Wenn ich das richtig verstandnen habe, müsste ich dann aber jede Rechnungs- und Lieferadresse in der tblRechnung nochmals speichern. Damit werden gleiche Dateien 2 x gespeichert. (Redundanz?)

Ja genau. Es ist ja nicht wirklich eine Redundanz, weil sich die Stammdaten ändern können und dann sind die Informationen nicht mehr redundant.

Wie ich geschrieben habe, werden häufig mehrere Aufträge in einer Rechnung abgerechnet.

Das wäre dann das, was man gemeinhin unter einer "Sammelrechnung" versteht. Dafür bräuchte man tatsächlich ein Zuordnung Rechnung (1 - n) Auftrag, was in deinem Fall dann zu einer m:n Tabelle führen würde.

Gruß
Ulrich

[Beaker s.a.]

Hallo Günther,

Ist ein guter Tipp. Werde ich versuchen umzusetzen und die Rechnungsadresse und Lieferadresse in die Rechnung kopieren.

Adressen gehören in eine Extratabelle mit FK zum Kunden und Kennzeichen ob
Liefer- oder Rechnungsadresse.

Wenn ich das richtig verstandnen habe, müsste ich dann aber jede Rechnungs- und Lieferadresse in der tblRechnung nochmals speichern

Ich habe dass mal so gemacht
In einer Abfrage habe ich die gesamte Adresse in einem Feld zusammengesetzt,
welches ich dann auf den Belegen verwendet habe (RowSource für Kombi), - auch
da dann nur ein Textfeld nötig.


gruss ekkehard

[Knobbi38]

Adressen gehören in eine Extratabelle mit FK zum Kunden und Kennzeichen ob
Liefer- oder Rechnungsadresse.

Definitiv nicht! Eine Begründung dafür habe ich bereits vorher hier im Thread angegeben.

In einer Abfrage habe ich die gesamte Adresse in einem Feld zusammengesetzt,
welches ich dann auf den Belegen verwendet habe (RowSource für Kombi), - auch
da dann nur ein Textfeld nötig.

Ein unnötiger Schritt, den man sich sparen kann, wenn eh alle Felder kopiert werden.

Gruß
Knobbi38

[Beaker s.a.]

Hallo Ulrich,

Definitiv nicht!

Ist eine starke Ansage. Ich bin damit immer gut zurecht gekommen.
Ich kann zwischen AdressArten wählen, ein/e Umzug/Adressänderung
ist ein neuer DS und nicht (mehr) aktive Adressen können deaktiviert
werden.
Ob die redundante Speicherung in nur einem Belegfeld Vor- oder Nachteile
hat kann ich nicht beurteilen. Wie gesagt, - s.o..

gruss ekkehard

[Knobbi38]

@Ekkehard

Ich kann zwischen AdressArten wählen, ein/e Umzug/Adressänderung
ist ein neuer DS und nicht (mehr) aktive Adressen können deaktiviert
werden.

bei manchen Anwendungen ist das o.a. Modell sicherlich sinnvoll, aber in der Buchhaltung eher weniger. Da interessiert eine Adresshistorie nicht und bedeutet nur einen zusätzlichen Aufwand.

VG
Ulrich

[gsaccess]

Das finde ich spannend, dass die Fachleute so unterschiedliche Ansätze haben.
Ich habe in meinem Datenmodell - dies habe ich in einer früheren Anfrage im Forum mit Klaus geklärt(siehe Bild) eine tblAdressenart in der ich zwischen Rechnungsadresse und Lieferantenadresse unterscheide. Ich könnte hier mehrere Adressen anlegen und immer die Zahl der letzten in die Rechnung bzw. tblRechnung übernehmen und mitspeichern. In der Rechnung wird dann nur die Adressennumer für die Rechnungsadresse und Lieferadresse (2 Zahlen) gespeichert. Bisher war immer nur eine Rechnungsadresse bzw. Lieferadresse erforderlich da auch nachträglich ausgestellte Rechnungen grundsätzlich an die aktuelle Adresse gehen. Ich bin nicht sicher ob der Aufwand des laufenden Abspeicherns der Adressen notwendig ist. Werd ich mir aber überlegen. Immer die gesamte Adresse in der Rechnung mitspeichern gefällt mir nicht wirklich. Zuerst Adresse zusammenfassen in ein Textfeld und dann in der Rechnung wieder aufteilen.
Die nicht aktiven Adressen inaktiv zu setzen sehe ich auch als gute Möglichkeit, dann kann immer nur eine aktive Rechnungs- und Lieferadresse übernommen werden.

Wie in der Doku beschrieben, hat Dlookup hier nur die Tabelle "tblrechnung" im Zugriff, wie soll dann das Kriterium aus "tblAuftrag" ausgewertet werden. Entweder du machst das mit einem  SQL Aufruf oder aber du verwendest für Dlookup eine Query mit einem Join über beide Tabellen. (#3)

Das habe ich nun so gelöst, dass ich die Kundennr in der tblRechnung mitschreibe. Dann kann ich mit dlookup abfragen.

Gruß Günther

[markusxy]

Wenn ich das richtig verstandnen habe, müsste ich dann aber jede Rechnungs- und Lieferadresse in der tblRechnung nochmals speichern. Damit werden gleiche Dateien 2 x gespeichert. (Redundanz?)

Ja genau. Es ist ja nicht wirklich eine Redundanz, weil sich die Stammdaten ändern können und dann sind die Informationen nicht mehr redundant.

Natürlich ist das eine Redundanz.
Mit etwas Überlegen, kann man ja auch Lösungen finden die Beides können.
Ohne Redundanz mit absoluter Nachvollziehbarkeit. 
Weil das Kopieren der Daten bringt auch keine übersichtliche Nachvollziehbarkeit von Änderungen.
Jede Lösung hat immer Vor- und Nachteile. Es geht auch immer um die Frage des Ziels.
Aber mit etwas weniger Dogmatismus, hat man auch die Chance auf bessere Lösungen zu kommen.
Es tut nicht weh, andere Meinungen zuzulassen.

[Knobbi38]

Hallo Markus,

Jede Lösung hat immer Vor- und Nachteile. Es geht auch immer um die Frage des Ziels.

Richtig, daß habe ich aber auch schon erwähnt. Unter Abwägung der Vor- und Nachteile erscheint mir das Kopieren als die einfachste Lösung und leicht umsetzbar, wohlgemerkt, hier geht es nur um das Thema Auftrag->Lieferung->Rechnung.
 

Aber mit etwas weniger Dogmatismus, hat man auch die Chance auf bessere Lösungen zu kommen.
Es tut nicht weh, andere Meinungen zuzulassen.

Dogmatismus wollte ich hier nicht betreiben und wenn das so herüber gekommen ist, möchte ich mich dafür Entschuldigen. Mir ging es vielmehr darum, überhaupt auf die Problematik hinzuweisen, da das "Kopieren" von Daten jedem, der öfter mit DB zu tun hat, in den meisten Fällen zunächst widerstrebt.

VG
Ulrich

[gsaccess]

Jetzt habe ich doch noch eine Frage zum Kopieren der Adressen. Bei mir stehen die Adressdaten in einer qryKundeAdressePLZ zur Verfügung.Die Daten dieser Abfrage wurden bisher über ein Kombinationsfeld im Formular angezeigt.(über das Kombinationsfeld.column().
Nun möchte ich dies über kopieren der Daten umsetzen. Das könnte aus meiner Sicht so aussehen:

Code [Auswählen] Erweitern

Kundenname= DLookup("[Kundenname]", "qryKundeAdressePLZ")
Straße= DLookup("[Straße]", "qryKundeAdressePLZ")
Ort= DLookup("[Ort]", "qryKundeAdressePLZ")...Kann ich diese Funktion als public sub in allen Formularen zur Verfügung stellen und dann über call aufrufen?
Dabei kommt die Meldung sub nicht definiert.
Oder habt ihr das oben beschriebene Kopieren besser gelöst?

Gruß Günther

[Beaker s.a.]

Dabei kommt die Meldung sub nicht definiert.

Und was wird angezeigt, wenn du bei der Fehlermeldung auf "debuggen" klickst?

[Knobbi38]

Hallo Günther,

das wird so nicht gehen. Erstens braucht die Query qryKundeAdressePLZ sicherlich einen eindeutige ID, damit nur eine Adresse ausgewählt wird und zweitens sind die Formularfelder natürlich als "private" deklariert, somit nicht von außerhalb des Formulars sichtbar.

Schreibe dir eine Funktion, welcher die aktuelle ID als Argument übergeben wird. Diese Funktion erstellt ein neues Recordset basierend auf der Query mit der ID als Kriterium und gibt dieses dann an den Aufrufer zurück.

Der Aufrufer, in diesem Fall eine Sub im Formular weist dann die Daten zu und schließt das Recordset wieder.

Gruß
Knobbi38