Aug 15, 2014, updated at Aug 18, 2014 (UTC)

7236

Access 2010 - Abfrage über mehrere Tabellen

Hallo zusammen!

Ich bin gerade etwas am Verzweifeln mit Access 2010. Meine Datenbank besteht aus folgenden Tabellen / Beziehungen:

Sie soll Verträge verwalten zwischen unterschiedlichen Gesellschaften. Alle Gesellschaften sind in tblGesellschaft enthalten.
Jede Gesellschaft kann Empfänger oder Zahlender sein.
tblVertrag enthält alle Verträge, die sich in einzelne Positionen aufteilen (tblVertragPosition). Jede Einzelposition bekommt ein Kennzeichen zugeordnet (=Art der Leistung). Alle Kennzeichen sind in tblKennzeichen gespeichert.
Die beiden Tabellen tblKtoEmpfangend und tblKtoZahlend enthalten die Info, welches Konto für welche Kombination Gesellschaft und Kennzeichen verwendet werden soll.
tblSteuer und tblMonat enthalten Kennzeichen für die einzelnen Steuerarten und Monate.
Wird ein Vertrag angelegt, müssen Empfänger und Zahlender festgelegt werden. Aus diesen beiden und dem Kennzeichen in tblVertragPosition ergibt sich dann das jeweilige Konto aus tblKtoEmpfangend bzw. tblKtoZahlend.

Ich möchte jetzt einen Bericht zu den Verträgen mit Einzelpositionen, in dem für jede Einzelposition das korrekte Konto enthalten ist inkl. Name der Gesellschaften und Name der Kennzeichen, mit Angabe von Steuer und Monat.

Ich habe schon gestern den ganzen Tag mit sämtlichen JOIN Arten experimentiert. Die Art, wie Access dann noch Klammern darum möchte, macht es auch nicht gerade einfacher. Auch mit einzelnen Unterabfragen, die dann in die Hauptabfrage laufen, habe ich es versucht.

Funktioniert das überhaupt mit Access, wie ich das gern hätte? Muss ich die Beziehungen überarbeiten, damit es funktioniert?
Für einen kleinen Denkanstoß wäre ich sehr dankbar

Viele Grüße
Eisbrecherin

Please also mark the comments that contributed to the solution of the article

Content-Key: 246571

Url: https://administrator.de/contentid/246571

Printed on: April 20, 2024 at 14:04 o'clock

12 Comments

Latest comment

Moin,

dein DB-Schema sieht auf den ersten Blick erstmal korrekt aus. Wenn du die Datenquelle für den report mit dem Abfrageeditor per Drag'n'Drop zusammenbastelst sollte das eigentlich passen.

Wo genau liegt denn dein Problem?

lg,
Slainte

Hallo!

Mir fehlt etwas der Durchblick beim doppelten Einbinden der Tabellen. Gesellschaft, Kennzeichen und Steuer brauche ich ja doppelt. Mein Abfrage-Diagramm sieht damit so aus:

Wenn ich das ausführe, bekomme ich auch drei Einträge. Allerdings sind die Einträge falsch:

Die Gesellschaft Empfänger stimmt, Gesellschaft Zahlender stimmt aber nicht. Beim Kennzeichen sollten bei beiden unterschiedliche Kennzeichen stehen. Die Beschreibung dagegen stimmt wieder.

Irgendwo ist da noch der Wurm drin.
Vielleicht sollte ich mal die beiden Tabellen mit den Konten in eine zusammenführen und dann halt ein Konto Zahlend und eines empfangend. Würde das mehr Sinn machen?

Viele Grüße
Eisbrecherin

Vielleicht sollte ich mal die beiden Tabellen mit den Konten in eine zusammenführen und dann halt ein Konto Zahlend und eines empfangend. Würde das mehr Sinn machen?

Das macht die Sache einfacher, sicher. Wenn dann dabei noch Redundanzen innerhalb der Daten reduziert werden, umso besser

Moin Eisbrecherin,

poste doch mal das generierte Statement, dann rücken wir das zurecht.

Und zum Verständnis:

In deinen Tabellen ist die Verknüpfung zu den Dimensionstabellen ja notgedrungen immer über diese ID-Felder, beispielsweise
steht in tblGesellschaft ja ein Feld "ID" und ein Feld "NameGes".

Die Verknüpfung zu (bspw.) tblKtoEmpfangend geht dann über das Feld "tblKtoEmpfangend GesellName", welches unabhängig vom Feldname in Wirklichkeit eine ID enthält, die auf "tblGesellschaft ID" refernziert????

Ich finde das DB-Modell nicht in Ordnung...zu viele irreführende Feldnamen, zu viele künstliche IDs.
Wonach sind denn die Vertragspositionen geordnet? Ich sehe da zwar den FK auf den zugehörigen Vertrag, aber keine "laufende Nummer" für Position 1, 2, 3....?

P.S. Aber deine verbale Beschreibung des Sachverhalts ist wirklich vorbildlich!

Grüße
Biber

Hallo Biber,

vielleicht sollte ich also lieber rausfinden, ob Access auch eine Spracherkennung hat? Das würde es einfacher machen

Ich habe jetzt erst mal die Felder umbenannt, damit man eher sieht, was ein Schlüssel in einer anderen Tabelle ist:

Und hier die zugehörige SQL Abfrage:

SELECT tblVertrag.VertragNr, tblKtoZahlend.Konto, tblVertragPosition.fk_KZ, tmpGesEmpf.NameGes, tmpGesZahl.NameGes, tblVertragPosition.Beschreibung
FROM ((tblKennzeichen AS tmpKZ_Zahl 
INNER JOIN (tblGesellschaft AS tmpGesZahl 
INNER JOIN tblKtoZahlend ON tmpGesZahl.ID = tblKtoZahlend.[fk_Ges]) 
ON tmpKZ_Zahl.ID = tblKtoZahlend.[fk_KZ]) 
INNER JOIN ((tblKennzeichen AS tmpKZ_Empf 
INNER JOIN (tblGesellschaft AS tmpGesEmpf 
INNER JOIN tblKtoEmpfangend ON tmpGesEmpf.ID = tblKtoEmpfangend.[fk_Ges]) 
ON tmpKZ_Empf.ID = tblKtoEmpfangend.[fk_KZ]) 
INNER JOIN (tblSteuer AS tmpVST 
INNER JOIN (tblSteuer AS tmpMWST 
INNER JOIN tblVertrag ON tmpMWST.ID = tblVertrag.[fk_Steuer]) 
ON tmpVST.ID = tblVertrag.[fk_Steuer]) 
ON tblKtoEmpfangend.ID = tblVertrag.[fk_KtoEmpf]) 
ON tblKtoZahlend.ID = tblVertrag.[fk_KtoZahl]) 
INNER JOIN (tblMonat 
INNER JOIN tblVertragPosition ON tblMonat.ID = tblVertragPosition.[fk_Div]) 
ON tblVertrag.ID = tblVertragPosition.fk_Vertrag;

Die Vertragspositionen sind nicht extra geordnet. Ist hier nicht nötig.

Viele Grüße
Eisbrecherin

Du solltest noch die Kardinalitäten für die FKs richtig einstellen bzw die referenzielle Integrität einschalten (ohne Löschweitergabe), dann ist das schon mal sauber - und Access bastelt schönere JOINs

Ggfs. kannst du noch die Tabellen tblKtoiEmpfangend mit tblKontoZahlend und die KZ und Zahl Tabellen zu jeweils einer Tabelle zusammen führen (dann wird der Select auch schon übersichtlicher)

/EDIT:

P.S. Aber deine verbale Beschreibung des Sachverhalts ist wirklich vorbildlich!

Dem kann ich nur zustimmen - das liest sich wie eine Klausuraufgabe :P

Die referenzielle Integrität ist bei den Beziehungen aktiviert. Wie kann ich die denn in der Abfrage einstellen? Sobald ich hier eine Tabelle doppelt aufnehme, verschwindet das.
Die Verknüpfungen in der Abfrage stehen auf 1 (nur die Datensätze, die in beiden Tabellen gleich sind).

Dann werde ich mich mal ans Verknüpfen der Tabellen machen.

Achso, das ist der Abfrage Editor.. sorry, ich dachte das ist das (gesamte) DB Schema... ok vergiss was ich über die Kardinalität usw gesagt habe .)

Moin Eisbrecherin,

leider muss ich jetzt mal das Wochenende vorbereiten und dich mit deinem Elend etwas alleine lassen.

Aber als Hinweis, bis ich wieder mal einen Rechner anschalte:
- stell die Abfragereihenfolge mal vom Kopf auf die Füsse - das generierte Statement nimmt als "zentrale Tabelle" für den ganzen Aufbau die "tblKennzeichen".... das kann nicht gutgehen.

Die "zentrale Tabelle", die als erstes genannt werden muss in dem ganzen FROM... INNER JOIN..INNER JOIN -Rattenschawanz ist die "tblVertragPosition", damit geht es los.

Das FROM sollte dann also von dieser Tabelle aus -beim Testen schrittweise, Verknüpfung um Verknüfung vorgehen:

-- nur ein Anfang ...
SELECT v.*, vpos.* , m.*

 FROM tblVertragPosition vpos
INNER JOIN tblMonat m ON m.ID = vpos.fk_Div
INNER JOIN tblVertrag v ON v.ID = vpos.fk_vertrag

...usw.

Und zum Testen erstmal mit einer WHERE-Bedingung "WHERE v=ID = 4711" oder eine andere Vertragsnummer, die existiert
Natürlich auch statt v.*, vpos.* usw nur die Felder, die du auch brauchst.

Ich klinke mich gern wieder ein, aber nicht in den nächsten 20 Stunden.

Grüße
Biber

Hallo Biber und Slainte,

ich habe meine Tabellen noch ein bisschen überarbeitet (gibt nur noch eine Tabelle für die Konten) und bin gerade dabei, die Abfrage INNER JOIN für INNER JOIN aufzubauen.
Momentan sieht sie so aus:

SELECT 
	vpos.KST_Empf, vpos.KST_Zahl, vpos.Beschreibung, m.Monat, v.VertragNr, kz_empf.NameKZ, s.MwSt, s.VSt, s.Satz, kto_empf.KtoEmpfangend, g_empf.NameGes, kto_zahl.KtoEmpfangend, g_zahl.NameGes
FROM ((((tblVertragPosition vpos
INNER JOIN tblMonat m ON m.Divisor = vpos.fk_Monat)
INNER JOIN tblVertrag v ON v.ID = vpos.fk_Vertrag)
INNER JOIN (tblKennzeichen kz_empf 
INNER JOIN (tblKonto kto_empf
INNER JOIN (tblVertrag v_empf 
INNER JOIN tblGesellschaft g_empf ON g_empf.ID = v_empf.fk_GesellEmpf)
ON v_empf.fk_GesellEmpf = kto_empf.fk_Ges)
ON kz_empf.ID = kto_empf.fk_KZ)
ON kz_empf.ID = vpos.fk_KZ)
INNER JOIN (tblKennzeichen kz_zahl
INNER JOIN (tblKonto kto_zahl
INNER JOIN (tblVertrag v_zahl
INNER JOIN tblGesellschaft g_zahl ON g_zahl.ID = v_zahl.fk_GesellZahl)
ON v_zahl.fk_GesellZahl = kto_zahl.fk_Ges)
ON kz_zahl.ID = kto_zahl.fk_KZ)
ON kz_zahl.ID = vpos.fk_KZ)
INNER JOIN tblSteuer s ON s.ID = vpos.fk_Steuer

Es ist noch nicht alles drin, aber der Teil zumindest funktioniert schon mal. Wenn mich Access komplett in den Wahnsinn treibt, wird der Rest hoffentlich auch noch

Ich markiere jetzt erst mal als gelöst, sollte es doch nicht so wollen wie ich melde ich mich nochmal.
Danke auf jeden Fall für eure Denkanstöße

Moin Eisbrecherin,

Zitat von @Eisbrecherin:

...
Es ist noch nicht alles drin, aber der Teil zumindest funktioniert schon mal.

So war der Plan, und ich denke, dieses Vorgehen hilft dir auch für die nächsten 20 Statements.
Ich denke, du bist auf dem richtigen Weg.

Wenn mich Access komplett in den Wahnsinn treibt, wird der Rest hoffentlich auch noch

Hoffentlich auch, wenn dich Access NICHT in den Wahnsinn treibt.

Ich markiere jetzt erst mal als gelöst, sollte es doch nicht so wollen wie ich melde ich mich nochmal.

Ich sage es voraus: Wenn das Statement drei DIN-A4-Seiten lang ist und nur noch eine einzige Tabelle drangeflanscht werden soll, dann wird dir Access mit einer lapidaren Meldung "Ungültige Anweisung in Nähe von ")", ankommen und du wirst schreien.

Wenn es zu holzig wird, dann kannst du mir auch den aktuellen Stand per Mail (Adresse in meinem Profil) senden.

Viel Glück
Biber

Hi Biber,

Zitat von @Biber:
Hoffentlich auch, wenn dich Access NICHT in den Wahnsinn treibt.

Sieht so aus, als hätte Access sein Ziel nicht erreicht...
Und manchmal sind WHERE Bedingungen einfach mehr wert als jeder JOIN

So sieht's jetzt aus:

SELECT
v.VertragNr, v.VertragAenderungjahr, v.VertragAenderungUnterjahr,
g_empf.NameGes AS GesEmpfangend, g_zahl.NameGes AS GesZahlend, 
vpos.KST_Empf, kto_empf.KtoEmpfangend, 
vpos.KST_Zahl, kto_zahl.KtoZahlend, 
kz.NameKZ, vpos.Beschreibung, 
s.MwSt, s.VSt, s.Satz,  
m.Monat,  vpos.BetragMonatNetto, (vpos.BetragMonatNetto / 100 * s.Satz) AS BetragSteuer, (BetragSteuer + vpos.BetragMonatNetto) AS BetragBrutto, vpos.GJ

FROM ((((tblVertragPosition AS vpos
INNER JOIN tblMonat AS m ON m.Divisor = vpos.fk_Monat)
INNER JOIN tblSteuer AS s ON s.ID = vpos.fk_Steuer)

INNER JOIN ((tblVertrag AS v 	
INNER JOIN (tblGesellschaft AS g_empf 	
INNER JOIN tblKonto AS kto_empf ON kto_empf.fk_Ges = g_empf.ID)
ON g_empf.ID = v.fk_GesellEmpf)
INNER JOIN (tblGesellschaft AS g_zahl 
INNER JOIN tblKonto AS kto_zahl ON kto_zahl.fk_Ges = g_zahl.ID)
ON g_zahl.ID = v.fk_GesellZahl)
ON v.ID = vpos.fk_Vertrag)

INNER JOIN tblKennzeichen AS kz ON kz.ID = vpos.fk_KZ) 

WHERE kto_empf.fk_Ges = g_empf.ID AND kto_empf.fk_KZ = kz.ID AND kto_zahl.fk_Ges = g_zahl.ID AND kto_zahl.fk_KZ = kz.ID;

Und wer jetzt noch was geändert haben will, muss schon ein paar Kilo Schoki springen lassen

German solved Question Databases Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 3 Comments

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments

WIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 3 Comments

End of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment