jk87
Goto Top

Access - Abfrage für Bücherstand

Hey Admins,

habe folgendes Problem:

Erstelle gerade eine AccessDatenbank zur Bibliotheksverwaltung.
Tabellen: t_buecher, t_entlehner, t_entlehnungen, t_retounierungen

Ich möchte nun per Abfrage einen Bücherstand erhalten, welcher Titel, Untertitel, Autor, ISBN, Stück (t_buecher), Entlehntestück (t_entlehnungen), Retouniertestück (Summe pro EntlehnungsID t_retounierungen) und Fehlendestück (Entlehntestück - Retouniertestück) ausgibt.

Access gibt mir immer die Entlehntenstück (t_entlehnungen) aus, auch wenn bereits teilweise Bücher retouniert wurden. Dadurch ergibt sich das Problem, dass die Entlehntenstück mit der gleichen EntlehnungsID summiert werden. Ich möchte aber dass pro EntlehnungsID nur einmal die Entlehntenstück ausgegeben werden.

Beispiel:
Nehmen wir an von Buch A gibt es 10 Exemplare (t_buecher), nun entlehnt eine Person 5 dieser 10 Exemplare (t_entlehnungen).
Die Person gibt nur 2 Exemplare zurück (t_retounierungen) und später noch 1 Exemplar (t_retounierungen).

Die Abfrage sollte mir nun folgendes Ausgaben:
Entlehntestück: 5 (nicht 10 nur weil 2mal retouniert wurde!)
Retouniertestück: 3 (Summe)
Fehlendestück: 2 (5-3)

Hoffe ihr versteht die Problemstellung und könnt mir auf die Sprünge helfen.

Danke im Voraus!

JK



Nachtrag:
Zum besseren Verständnis, die Beziehungen mit den Tabelleninhalten:
417011136705e6915b6ad49ecc7b8414-beziehungen

Content-Key: 30359

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

Printed on: December 4, 2022 at 21:12 o'clock

Member: misterdemeanor
misterdemeanor Apr 13, 2006 at 18:16:41 (UTC)
Goto Top
Grüß Dich,
also irgendwie komme ich da nicht recht mit.
Poste Mal die Struktur Deiner Tabellen :
Tabelle A hat das Feld 1, 2, 3 und 4
Tabelle B hat das Feld 1, 2 und 3
usw...

Grüße
Felix
Member: JK87
JK87 Apr 13, 2006 at 18:35:38 (UTC)
Goto Top
Bitte Felix, ein Nachtrag mit den Beziehungen und den Tabelleninhalten!
Hoffe du kannst mir jetzt helfen!

JK
Member: Biber
Biber Apr 14, 2006 at 12:03:41 (UTC)
Goto Top
Moin JK,

Da musst Du eine Unterabfrage machen, da Du ja von den Retournierungen die summierten Werte brauchst.
Ansonsten einfach runtertippen..
Soweit ich das im Kopf durchdenken kann, müsste das Access-taugliche SQL-Statement so aussehen:


Dieser Schnipsel hier...
SELECT r.EntlehnungsID, Sum(r.RetournierteStück) AS RetournierteStück
FROM t_retournierungen r GROUP BY r.EntlehnungsID) as SumRetour

... ist die nötige Unterabfrage, die Du im Access auch als separate Abfrage speichern kannst.

und so eine Anzeige bringen:

Titel Untertitel Autor ISBN Stück EntlehnteStück RetournierteStück FehlendeStück
Buch A wer A sagt.. A.Schreiber 3-404-13513-X 10 5 3 2

Hope That Helps
Biber
Member: JK87
JK87 Apr 15, 2006 at 10:29:59 (UTC)
Goto Top
That helps very much!

Einfach perfekt Biber, funktioniert genau so wie ich mir das vorgestellt habe.
Auf dich ist halt immer verlass, nochmals danke!

JK
Member: JK87
JK87 Apr 15, 2006 at 16:09:29 (UTC)
Goto Top
Kenn mich mit SQL in Access nicht wirklich aus, und würde daher noch gerne wissen wie der Code aussieht, wenn ich als letzte Spalte noch das Soll ausgeben möchte.
Stück-Fehlende Stück

Sprich:
TitelUntertitelAutorISBNStückEntlehnteStückRetournierteStückFehlendeStückSoll
Buch Awer A sagt..A.Schreiber3-404-13513-X105328

thx

JK
Member: Biber
Biber Apr 15, 2006 at 18:10:49 (UTC)
Goto Top
Moin Jörg,

genau wie Du geschrieben hast:

Grüße
Biber
der sehr traurig ist, dass Werder nur 1:1 gespielt hat.. face-sad
Member: JK87
JK87 Apr 16, 2006 at 09:32:43 (UTC)
Goto Top
Biber, das funktioniert leider nicht wie ich will.
Habe folgende Abfrage:

SELECT b.Titel, b.Untertitel, b.Autor, b.ISBN, b.Stück, Sum(e.EntlehnteStück) AS SummevonEntlehnteStück, Sum(SumRetour.RetournierteStück) AS SummevonRetournierteStück, Sum(EntlehnteStück-RetournierteStück) AS FehlendeStück, [Stück]-([EntlehnteStück]-[RetournierteStück]) AS Soll
FROM t_buecher AS b LEFT JOIN (t_entlehnungen AS e LEFT JOIN [SELECT r.EntlehnungsID, Sum(r.RetournierteStück) AS RetournierteStück
FROM t_retournierungen r
GROUP BY r.EntlehnungsID]. AS SumRetour ON e.EntlehnungsID = SumRetour.EntlehnungsID) ON b.BuchID = e.BuchID
WHERE ((([EntlehnteStück]-[RetournierteStück])>=1))
GROUP BY b.Titel, b.Untertitel, b.Autor, b.ISBN, b.Stück, [Stück]-([EntlehnteStück]-[RetournierteStück]);

entspricht

TitelUntertitelISBNStückSummevonEntlehnteStückSummevonRetournierteStückFehlendeStückSoll
Die deutsche Literatur in Text und DarstellungMittelalter 131500960141775215
Die deutsche Literatur in Text und DarstellungMittelalter 2315009605717105512
Die deutsche Literatur in Text und DarstellungMittelalter 231500960571721116


Möchte aber dass das so augegeben wird:
TitelUntertitelISBNStückSummevonEntlehnteStückSummevonRetournierteStückFehlendeStückSoll
Die deutsche Literatur in Text und DarstellungMittelalter 131500960141775215
Die deutsche Literatur in Text und DarstellungMittelalter 2315009605717126611

Jedes Buch soll nur einmal vorkommen.

Hoffe ich habe mich verständlich ausgedrückt!
Danke für deine Hilfe

JK
Member: Biber
Biber Apr 16, 2006 at 13:01:36 (UTC)
Goto Top
Moin JK,

na gut, dann muss da eben auch noch eine Aggregat-Funktion ("SUM(..) ...group by b.buchid") rein. Ist auch nicht aufregend.
Dann es abe mehrere Strategie-Varianten:

a) entweder einzelne, aber komplexe Abfragen verfeinern und optimieren... also das oben stehenden Statement noch um zwei, drei Zeilen ergänzen.

Risiken dabei sind, dass
- im Falle eines Tippfehlers/eines Syntax-Errors die Fehlersuche einfach lumpig ist, soweit ich mich an ACCESS erinnere. Nix mit "Cursor steht an der Stelle des Errors" oder so.

- Du in drei Wochen nicht mehr weißt, wie dieses Statement zustande kam, denn ACCESS erlaubt (glaube ich) immer noch nicht das Abspeichern von Kommentaren im SQL-Statement. Ist also kaum dokumentierbar.

b) Die vorhandenen Abfragen einzeln als "Bausteine" speichern und und sinnvoll miteinander kombinieren.

Wenn Du b) wählst:
Speichere die Abfrage oben ab unter einem sprechenden Namen. Denn die wirst Du wieder brauchen, wenn Du Auswertungen/Abfragen über alle "Entlehner" machst.
Auf die gespeicherte Abfrage "BücherJeEntlehnerID" kannst Du dann wieder nach dem obgen Muster eine Abfrage machen:

Gruß Biber
Member: JK87
JK87 Apr 16, 2006 at 14:20:07 (UTC)
Goto Top
Sorry, aber ich bin einfach zu böld dafür.
Kannst du mir das bitte noch einmal erklären mit dem SQL-Statements für mein Soll (InventurAbfrage)! thx

JK
Member: Biber
Biber Apr 16, 2006 at 17:17:01 (UTC)
Goto Top
*grummel*

...jetzt hatte ich grad ein ausführliche Antwort geschrieben und dann einen TimeOut ...*grr*

Okay - die Kurzfassung (eine Variante):
-in Deine Abfrage noch das Feld b.Buchid rein und die Group-Bedingung da drauf.

Ist alles ungetestet, sonst kann ich aber Dienstag mal in Ruhe gucken.
Heut ist ja Ostern...
Gruß
Biber
Member: JK87
JK87 Apr 17, 2006 at 08:14:14 (UTC)
Goto Top
Funktioniert leider nicht, hoffe du kannst mir das am Dienstag richtig sagen!
thx

JK

PS: Frohe Ostern!
Member: Biber
Biber Apr 18, 2006 at 19:30:48 (UTC)
Goto Top
Na, JK,

das kann aber ausufern..*gg

Also, zum Aufwärmen machen wir ein Statementle für die gesammelten Entlehnungen, also auch der Entlehnungen, für die es noch keine Rückläufer gibt.
Da kommt dann sowas raus:
EntlehnungsDatum EntlehnungsID Buchid Entlehner Titel Untertitel ISBN~ EntlehnteStück SummevonRetournierteStück FehlendeStück EntlehnungsAnmerkung
18.04.20062854Entlehner A 3. November 1918 Drei Akte 3 03
18.04.20062460Entlehnerin B Das Leben ist ein Traum Schauspiel 2 2 0
17.04.20061124Entlehner C Abriss der Deutschen LiteraturLiteratur3700312784 1 01
17.04.2006 720Entlehner DAbriss der Deutschen LiteraturLiteratur 370030959712 0 12
22.02.2006 32 Entlehner D Die deutsche Literatur in Text und Darstellung Mittelalter 2 31500960572 1 1
22.02.2006 22 Entlehner C Die deutsche Literatur in Text und Darstellung Mittelalter 2 3150096057 10 91 3DK
17.02.2006 11 Entlehner C Die deutsche Literatur in Text und Darstellung Mittelalter 1 31500960147 52

Wie Du siehst, ist da aber z.B. die BuchID "2" noch doppelt drin (richtigerweise).
(ist ein bisschen lumpig zu formatieren hier)

Wenn wir diese Abfrage für schlechte Zeiten und spätere Auswertungen abspeichern unter dem Namen "a_13_biber_entlehnungen", dann können wir
-->erstens Deine Abfrage oben abfackeln:
SELECT b.Titel, b.Untertitel, b.Autor, b.ISBN, b.Stück,
Sum(e.EntlehnteStück) AS SummevonEntlehnteStück,
Sum(SumRetour.RetournierteStück) AS SummevonRetournierteStück,
Sum(EntlehnteStück-RetournierteStück) AS FehlendeStück,
[Stück]-([EntlehnteStück]-[RetournierteStück]) AS Soll
...ist dann darstellbar durch ein:
(sieht ein bissi lesbarer aus *gg)
---> zweitens beim Buch-Bestand(Inventurabgleich) darauf zurückgreifen.

Das geht dann relativ unaufwändig:

Die Abfrage würde die obere Abfrage nochmal aggregieren auf der Ebene "Buchid".
Also ein äußeres "SUM..GROUP BY BuchID" ..auf das vorhandene "SUM...GROUP BY EntlehnerID."
Nachteil: Die bringt nur alle Bücher, für die auch "Entlehnungen", wie es bei Euch in der Alpenregion heißt, vorhanden sind.

Da Du aber alle Bücher, ob jemals entliehen oder nicht, auf einer Liste haben willst, kombinieren wir die Abfrage mit einer gleich aussehenden Ergebnismenge, die alle BuchIDs aus der Büchertabelle holt, die NICHT in der Entlehner-Tabelle stehen.
Also noch mal ein UNION drunterpappen:

Ergebnis:
BuchidTitelUntertitelISBNStückEntlehntRetourniertFehlendeSoll
1Die deutsche Literatur in Text und DarstellungMittelalter 131500960141775215
2Die deutsche Literatur in Text und DarstellungMittelalter 23150096057171210215
3Eisbrecher 1 Eisbrecher38520754912200022
......
....

Satz 3 ist ein Buch, das noch nie entliehen wurde...
Die BuchId kannst Du natürlich in der Anzeige wieder weglassen, ich habe sie hier mit dabei, weil BuchId 1 und BuchId 2 den gleichen Titel mit sich schleppen. Das hätte sonst verwirrt.

Thats all. Und natürlich nur eine mögliche Lösung.
Kannst eventuell nochmal in der ACCESS-Hilfe gucken nach der IIF-Funktion, die ich oben verwendet habe, nach dem UNION und dem DISTINCT unten. Das würde jetzt zu weit führen - ist aber nicht so entscheidend.
Grüße Biber

[Edit Biber 10.8.2009]
Entlehner-Namen im Beispiel gemäß Anforderung von "Entlehner A" anonymisiert aus datenschutzrechtlichen Gründen.
[/Edit]
Member: Biber
Biber Apr 24, 2006 at 08:09:07 (UTC)
Goto Top
Rückmeldung wäre nett...
Member: JK87
JK87 Apr 24, 2006 at 11:20:10 (UTC)
Goto Top
Sry, dass ich dir noch kein Feedback geben konnte! Bei mir ist es wirklich stressig, momentan!
Danke dir vorerst für deine Hilfe, Feedback folgt so bald ich es getetest habe!

JK
Member: Biber
Biber Apr 24, 2006 at 11:23:59 (UTC)
Goto Top
Stress - was soll denn das sein? *gg

Danke für die Rückmeldung.. ich leg es uns für Juni auf Wiedervorlage. face-wink

Stressarmen Montag wünsch ich Dir
Biber
Member: JK87
JK87 Apr 29, 2006 at 13:21:04 (UTC)
Goto Top
Hey Biber,

die Abfragen funktionieren wunderbar,
habe noch eine letzt Frage wie kann ich eine Bedinung in diese Abfrage einfügen. Es sollen nur jene Entlehnungen angezeigt werden, wo die FehlendenStück >0 sind!

SELECT e.EntlehnungsDatum, e.EntlehnungsID,e.Buchid,
[L.Zuname] & " " & [l.Vorname] AS Entlehner,
b.Titel, b.Untertitel, b.ISBN,
e.EntlehnteStück,
iif( r.RetSum is Null, 0, r.RetSum) AS SummevonRetournierteStück,
[EntlehnteStück]-SummevonRetournierteStück AS FehlendeStück,
e.EntlehnungsAnmerkung
FROM ((t_entlehnungen AS e LEFT JOIN
[Select entlehnungsid, sum(retournierteStück) as retSum from t_retournierungen group by entlehnungsid ]. as r
ON e.EntlehnungsID=r.[EntlehnungsID]) INNER JOIN t_buecher AS b
ON b.buchid = e.buchid) INNER JOIN t_entlehner AS L
ON L.entlehnerid = e.entlehnerid
ORDER BY e.EntlehnungsDatum DESC;

Danke dir,

JK
Member: Biber
Biber Apr 29, 2006 at 20:50:06 (UTC)
Goto Top
Moin Jörg,

zwei Varianten,

a) direkt in Deine Abfrage oben als Where-Bedingung:

-oder- (lesbarer):
b) die Abfrage oben speichern als "AlleEntlehnungen"

und dann:

Variante b) wäre recommended, wie das auf Neudeutsch heißt.
Vorteil: immer schön nach Baukastenprinzip auf vorhandene Views/Abfragen zurückgreifen.

Gruß
Biber
Member: misterdemeanor
misterdemeanor Apr 30, 2006 at 03:14:32 (UTC)
Goto Top
Hey Biber,

hat JK schon Deine Konto/PayPal Daten?

Grüße
Felix
Member: Biber
Biber Apr 30, 2006 at 12:01:24 (UTC)
Goto Top
*gg @misterdemeanor
... ich dachte, ich gebe ihn bei meiner nächsten Bewerbung als Referenzkunden an.. face-wink

Consultinghonorar nehme ich nur bei komplexen Problemen.

Liebe Grüße
Frank / der Biber aus Bremen
Member: JK87
JK87 May 01, 2006 at 17:50:01 (UTC)
Goto Top
Kein Problem, kannst mich ruhig angeben!

Nochmals Danke,

JK