Dezember 07, 2021, 12:48:00

Neuigkeiten:

Ist euer Problem gelöst, dann bitte den Knopf "Thema gelöst" drücken!


Refactoring Select Abfrage

Begonnen von Clumsy, November 24, 2021, 20:08:21

⏪ vorheriges - nächstes ⏩

Clumsy

Hallo zusammen, ich erlaube mir nochmals eine Frage zu posten.

Folgende Abfrage funktioniert, sieht aber einfach nicht richtig aus. Das muss doch besser gehen?

SELECT P.*, K.*, (SELECT SUM(C1.Gesamtpreis) FROM tbl_VorgangArtikel C1 WHERE P.ID = C1.IDVorgang) AS Preis, IIf(IsNull((SELECT SUM(C2.Betrag) FROM tbl_Zahlungen C2 WHERE P.ID = C2.Vorgang)-Preis),-Preis,(SELECT SUM(C2.Betrag) FROM tbl_Zahlungen C2 WHERE P.ID = C2.Vorgang)-Preis) AS Offen
FROM tbl_Vorgaenge AS P LEFT JOIN tbl_Kunden AS K ON P.Kunde = K.ID
WHERE P.Vorgangsart='Rechnung' AND IIf(IsNull((SELECT SUM(C2.Betrag) FROM tbl_Zahlungen C2 WHERE P.ID = C2.Vorgang)-(SELECT SUM(C1.Gesamtpreis) FROM tbl_VorgangArtikel C1 WHERE P.ID = C1.IDVorgang)),-(SELECT SUM(C1.Gesamtpreis) FROM tbl_VorgangArtikel C1 WHERE P.ID = C1.IDVorgang),(SELECT SUM(C2.Betrag) FROM tbl_Zahlungen C2 WHERE P.ID = C2.Vorgang)-(SELECT SUM(C1.Gesamtpreis) FROM tbl_VorgangArtikel C1 WHERE P.ID = C1.IDVorgang)) < 0;

Folgende Tabellen verwende ich:

tbl_Vorgaenge
ID | Kunde | Vorgangsart | (Zahlungsziel, Notiz, ...)

tbl_VorgangArtikel
ID | IDVorgang | Artikelname | Gesamtpreis

tbl_Zahlungen
ID | Vorgang | Betrag

tbl_Kunden
ID | Vorname| Nachname | (Strasse, Ort, ...)

Die Abfrage liefert mir die Vorgänge (inkl. Kundendaten, gesamter Rechnungsbetrag und offener Rechnungsbetrag) welche Rechnungen(=Rechnungsart) sind und noch nicht vollständig bezahlt wurden(Offen<0).
Insbesondere verstehe ich nicht, warum ich am Ende nicht einfach schreiben kann ... WHERE Offen < 0
Ich bedanke mich für jede Unterstützung!
Beste Grüße, Flo

markus888

Hier mal ein Ansatz zum Nachdenken - hab da aufgrund der Inkonsistenzen bei der Benamsung nicht mehr genau auf deine Namen geachtet. Außerdem entspricht das TSQL, das Prinzip sollte aber klar werden.
Aber das Prinzip sollte klar sein. In einem Subselect werden die Summen ermittelt, der Rest wird erst später hinzugefügt.

Dann noch mein Hinweis - dein Datenmodell hat noch Verbesserungsbedarf.
Vorgangsart oder Artikelname müssen natürlich ID's sein.

SELECT *
FROM (
SELECT VA.IDVorgang
,SUM(VA.Gesamtpreis) Gesamtpreis
,NZ((
SELECT SUM(Z.Betrag)
FROM tbl_Zahlungen Z Z.Vorgang = VA.IDVorgang
), 0) Zahlungen
FROM tbl_VorgangArtikel VA
GROUP BY VA.IDVorgang
) D
INNER JOIN tblVorgang V ON V.IDVorgang = D.IDVorgang
INNER JOIN tblKunden K ON K.IDKunde = V.IDKunde
WHERE V.Vorgangsart = 'Rechnung'
AND D.Gesamtpreis <> D.Zahlungen
10 Jahre Access

Clumsy

Hi Markus, herzlichen Dank für die große Mühe, die du dir gemacht hast!
"Leider" fahre ich jetzt gleich für ein paar Tage in den Urlaub und kann den Code erst am Montag ausprobieren. Darauf freue ich mich jetzt aber schon! :)
Darf ich dich direkt fragen, worin genau du den Verbesserungsbedarf siehst? Ich bin stets bemüht mich zu verbessern.
Nochmals vielen Dank und beste Grüße, Flo

markus888

Zitat von: Clumsy am November 25, 2021, 11:26:49Darf ich dich direkt fragen, worin genau du den Verbesserungsbedarf siehst?

Ich zitiere mich mal:

ZitatVorgangsart oder Artikelname müssen natürlich ID's sein.

Ist Artikelname bei dir eine ID oder ein Text?

Falls es ein Text ist - warum?

Genauso bei Vorgangsart.


Mal abgesehen davon, dass hier das vielfache an Daten gespeichert werden müssen und der Vergleich mit einem Text weit langsamer ist und mögliche Änderungen des Artikelnamens fatal wären.


Gegen welche Normalisierungsregel verstößt das?
https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
10 Jahre Access

Clumsy

Hallo Markus,

da habe ich Dich falsch verstanden... Kritik absolut gerechtfertigt! :)
Ich habe nun einheitlich über alle Tabellen die Bezeichnungen IDVorgang, IDKunde, IDZahlung und IDVorgangArtikel verwendet.

Nach beinahe einer Stunde vergeblichem rumprobieren, wende ich mich nun nochmals an Dich.

Was habe ich geändert?
1. Du hast tbl_Vorgang verwendet, obwohl die Tabelle tbl_Vorgaenge heißt. (Mir ist bewusst, dass ich die Namensgebung im Plural von meiner Seite her schlecht gewählt wurde)
2. Um den Fehler einzugrenzen, habe ich die Inner Joins und die Where Klausel temporär gelöscht
3. Aufgrund der Fehlermeldung "Syntaxfehler (fehlender Operator) in Abfrageausdruck 'SUM(VA.Gesamtpreis) Gesamtpreis'." habe ich das Wort "AS" eingefügt. Daraufhin kam zumindest diese Fehlermeldung nicht mehr. Analog in den nächsten Zeilen.

Aktuell habe ich also folgende Abfrage:
SELECT *

FROM (
SELECT

VA.IDVorgang

,SUM(VA.Gesamtpreis) AS Gesamtpreis

,NZ((
SELECT SUM(Z.Betrag)
FROM tbl_Zahlungen Z Z.IDVorgang = VA.IDVorgang
), 0) AS Zahlungen
FROM tbl_VorgangArtikel VA
GROUP BY VA.IDVorgang
) AS D

Die Fehlermeldung lautet:
ZitatSyntaxfehler im Abfrageausdruck 'NZ((SELECT SUM(Z.Betrag) FROM tbl_Zahlungen Z Z.IDVorgang = VA.IDVorgang), 0)'.

markus888

Da fehlt vor der WHERE Bedingung das Wort WHERE.
Der Code ist nicht getestet.
Du solltest versuchen die Logik zu verstehen und den Code entsprechend anpassen.
Auch die Joins werden in Access ohne Klammern nicht funktionieren.

Idealerweise machst du die einzelnen Abfragen direkt im Abfrageeditor und fügst sie nur zusammen.
Dann setzt Access die Klammern automatisch




10 Jahre Access

Clumsy

Noch ein letztes Danke für deine Hilfe, Markus!
Ich habe es hinbekommen.

Für alle Nachfolgenden: NV() scheint nicht zu funktionieren, wenn ich die Abfrage mittels VBA aus Excel heraus an Access schicke. Habe stattdessen mit IIF() und isNull() gearbeitet.

ebs17

Zitatscheint nicht zu funktionieren
Wenn man (Jet-SQL-)Abfragen aus Access heraus ausführt, sorgt der sogenannte Expression Service dafür, dass man innerhalb der Abfragen Funktionen aus anderen Objekten nutzen und ausführen kann, womit der sonst sehr sparsame Sprachumfang von Jet-SQL wirksam ergänzt wird.
Solche Funktionen sind Funktionen der eigentlichen VBA-Bibliothek, aber auch Funktionen des Accessobjektes wie Nz und DLookup, und dann nicht zuletzt eigendefinierte Funktionen (bspw. SQLListe).

Bei Ausführung der Abfrage aus Excel heraus entfällt die Nutzung des ES.

Daneben kann es immer eine Überlegung in Sachen Stil und Performance sein, in SQL-Abfragen nur Anweisungen anderer Objekte einzusetzen, wenn die Hausmethoden nicht ausreichen oder ineffizient sind.
Mit freundlichem Glück Auf!

Eberhard

markus888

Zitat von: Clumsy am Dezember 04, 2021, 23:23:38Für alle Nachfolgenden: NZ() scheint nicht zu funktionieren, wenn ich die Abfrage mittels VBA aus Excel heraus an Access schicke.

Na ja, die Funktion ist in Excel nicht definiert.
Du kannst aus Excel heraus nur die Funktionen nutzen, die auch in Excel existieren.
Du hast auch nie erwähnt, dass du mit Excel arbeitest.

Edit:
Um die NZ Funktion in Excel zu nutzen, müsste man sie also entweder selbst schreiben, oder die erforderlichen Access Klassen einbinden - oder wie in deinem Fall Ersatzfunktionen einsetzen.
10 Jahre Access