13.04.2006, aktualisiert am 01.05.2006

8368

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

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 30359

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

Ausgedruckt am: 25.11.2024 um 16:11 Uhr

20 Kommentare

Neuester Kommentar

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

Bitte Felix, ein Nachtrag mit den Beziehungen und den Tabelleninhalten!
Hoffe du kannst mir jetzt helfen!

JK

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:

SELECT b.Titel,b.Untertitel,b.Autor,b.ISBN, b.Stück, 
e.EntlehnteStück, 
RetournierteStück,
EntlehnteStück-RetournierteStück as FehlendeStück
FROM (t_buecher AS b LEFT JOIN t_entlehnungen e ON b.BuchId = e.BuchId)
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;

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

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

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:

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

thx

JK

Moin Jörg,

genau wie Du geschrieben hast:

SELECT b.Titel,b.Untertitel,b.Autor,b.ISBN, b.Stück, 
e.EntlehnteStück, 
RetournierteStück,
EntlehnteStück-RetournierteStück as FehlendeStück,
b.Stück-FehlendeStück as Soll
FROM (t_buecher AS b LEFT JOIN t_entlehnungen e ON b.BuchId = e.BuchId)
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;

Grüße
Biber
der sehr traurig ist, dass Werder nur 1:1 gespielt hat..

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

Titel	Untertitel	ISBN	Stück	SummevonEntlehnteStück	SummevonRetournierteStück	FehlendeStück	Soll
Die deutsche Literatur in Text und Darstellung	Mittelalter 1	3150096014	17	7	5	2	15
Die deutsche Literatur in Text und Darstellung	Mittelalter 2	3150096057	17	10	5	5	12
Die deutsche Literatur in Text und Darstellung	Mittelalter 2	3150096057	17	2	1	1	16

Möchte aber dass das so augegeben wird:

Titel	Untertitel	ISBN	Stück	SummevonEntlehnteStück	SummevonRetournierteStück	FehlendeStück	Soll
Die deutsche Literatur in Text und Darstellung	Mittelalter 1	3150096014	17	7	5	2	15
Die deutsche Literatur in Text und Darstellung	Mittelalter 2	3150096057	17	12	6	6	11

Jedes Buch soll nur einmal vorkommen.

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

JK

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:

Select * from ( 
select titel, untertitel , isbn, Sum( SummevonEntlehnteStück), 
Sum(SummevonRetour...), 
Sum(...alle folgenden num. Felder...)  from BücherJeEntlehnerID group by buchid)

Gruß Biber

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

*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.

SELECT b.buchid,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.buchid;

Ist alles ungetestet, sonst kann ich aber Dienstag mal in Ruhe gucken.
Heut ist ja Ostern...
Gruß
Biber

Funktioniert leider nicht, hoffe du kannst mir das am Dienstag richtig sagen!
thx

JK

PS: Frohe Ostern!

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.

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;

Da kommt dann sowas raus:

EntlehnungsDatum	EntlehnungsID	Buchid	Entlehner	Titel	Untertitel	ISBN	~ EntlehnteStück	SummevonRetournierteStück	FehlendeStück	EntlehnungsAnmerkung
18.04.2006	28	54	Entlehner A	3. November 1918	Drei Akte		3	0	3
18.04.2006	24	60	Entlehnerin B	Das Leben ist ein Traum	Schauspiel		2	2	0
17.04.2006	11	24	Entlehner C	Abriss der Deutschen Literatur	Literatur	3700312784	1 0	1
17.04.2006	7	20	Entlehner D	Abriss der Deutschen Literatur	Literatur	3700309597	12	0	12
22.02.2006	3	2	Entlehner D	Die deutsche Literatur in Text und Darstellung	Mittelalter 2	3150096057	2	1	1
22.02.2006	2	2	Entlehner C	Die deutsche Literatur in Text und Darstellung	Mittelalter 2	3150096057	10	9	1	3DK
17.02.2006	1	1	Entlehner C	Die deutsche Literatur in Text und Darstellung	Mittelalter 1	3150096014	7	5	2

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:

Select Titel, Untertitel, Autor, ISBN, Stück, EntlehnteStück, SummeVonretournierteStück,FehlendeStück, Soll
from a_13_biber_entlehnungen
order by buchid

(sieht ein bissi lesbarer aus *gg)
---> zweitens beim Buch-Bestand(Inventurabgleich) darauf zurückgreifen.

Das geht dann relativ unaufwändig:

SELECT first(b.buchid) as Buchid, first(b.titel) as Titel, first(b.Untertitel) as Untertitel ,first(b.Isbn) as ISBN, first(b.stück) as Stück, 
sum( iif(entlehnteStück is null,0, entlehnteStück)) as Entlehnt,
sum( SummeVonRetournierteStück) AS Retourniert, 
sum( FehlendeStück) AS Fehlende,
first(b.stück)-(sum( entlehnteStück)-sum( SummeVonRetournierteStück)) as Soll
FROM t_buecher b  left join   a_13_biber_entlehnungen as a13 on b.buchid=a13.buchid
group by a13.buchid

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:

SELECT first(b.buchid) as Buchid, first(b.titel) as Titel, first(b.Untertitel) as Untertitel ,first(b.Isbn) as ISBN, first(b.stück) as Stück, 
sum( iif(entlehnteStück is null,0, entlehnteStück)) as Entlehnt,
sum( SummeVonRetournierteStück) AS Retourniert, 
sum( FehlendeStück) AS Fehlende,
first(b.stück)-(sum( entlehnteStück)-sum( SummeVonRetournierteStück)) as Soll
FROM t_buecher b  left join   a_13_biber_entlehnungen as a13 on b.buchid=a13.buchid
group by a13.buchid

union

SELECT
  Buchid, Titel, Untertitel ,ISBN, Stück, 
0 as Entlehnt,
0 AS Retourniert, 
0 AS Fehlende,
Stück as Soll
from t_buecher b
where b.buchid not in (select distinct e.buchid from t_entlehnungen e)

Ergebnis:

Buchid	Titel	Untertitel	ISBN	Stück	Entlehnt	Retourniert	Fehlende	Soll
1	Die deutsche Literatur in Text und Darstellung	Mittelalter 1	3150096014	17	7	5	2	15
2	Die deutsche Literatur in Text und Darstellung	Mittelalter 2	3150096057	17	12	10	2	15
3	Eisbrecher 1	Eisbrecher	3852075491	22	0	0	0	22

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]

Rückmeldung wäre nett...

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

Stress - was soll denn das sein? *gg

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

Stressarmen Montag wünsch ich Dir
Biber

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

Moin Jörg,

zwei Varianten,

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

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
where(e.EntlehnteStück- iif( r.RetSum is Null, 0, r.RetSum)>0)
ORDER BY e.EntlehnungsDatum DESC;

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

und dann:

Select * from AlleEntlehnungen where FehlendeStück > 0

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