Loan module

  • Thread starter Thread starter EMILYTAN via AccessMonster.com
  • Start date Start date
E

EMILYTAN via AccessMonster.com

I want to build a query which will have LoanQty, OwedQty and ReturnedQty

OwedQty = LoanQty-ReturnedQty

However, this item can returned separately which means Item1 can loan 4 item.
Then return separately...
How to build this kind of query?

I have 3 different table. 1 is to keep track of the loan requestor which will
produce LoanID, 1 is for borrowing item and 1 is for returning item
 
Without knowing a lot more about your table structure this is difficult to
answer. You could try a query that would look something like the following.

SELECT R.LoanID,
, Count(B.LoanID) as ItemsLoaned
, (SELECT Count(C.LoanID) as ItemsReturned
FROM [ItemsReturned] as C
WHERE C.LoanID = R.LoanID) as Returned
, (ItemsLoaned - Nz(Returned)) as StillOut
FROM [Requestor Table] as R INNER JOIN [Borrowed] as B
ON R.LoanId = B.LoanID
GROUP BY R.LoanID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
My table structure:-

Loan
-loanID, Requestor
-example :- L00001, Mary

LoanBorrow
-lbID, loanID, ItemID, QuantityLoaned, QuantityOwed (Loaned-Returned)
- example :- 001, L00001, M1010-0990, 4, 2

LoanReturn
-lrID, lbID, ItemID, QuantityReturned
-example :- 111, 001, M1010-0990, 1
111, 001, M1010-0990, 1


I can make it work using VBA but it is not instance change as it uses forms
current events...so I want it to be in query...
Thanks...
John said:
Without knowing a lot more about your table structure this is difficult to
answer. You could try a query that would look something like the following.

SELECT R.LoanID,
, Count(B.LoanID) as ItemsLoaned
, (SELECT Count(C.LoanID) as ItemsReturned
FROM [ItemsReturned] as C
WHERE C.LoanID = R.LoanID) as Returned
, (ItemsLoaned - Nz(Returned)) as StillOut
FROM [Requestor Table] as R INNER JOIN [Borrowed] as B
ON R.LoanId = B.LoanID
GROUP BY R.LoanID
I want to build a query which will have LoanQty, OwedQty and ReturnedQty
Click to expand...
[quoted text clipped - 8 lines]
will
produce LoanID, 1 is for borrowing item and 1 is for returning item
Click to expand...
Click to expand...
 
Assumption:
Loan.LoanID is the Primary key
LoanBorrow.lbID is the Primary key

I would try:

SELECT L.LoanID
, L.Requestor
, B.ItemID
, Sum(B.QuantityLoaned) as Loaned
, Sum(R.QuantityReturned) as Returned
, Sum(B.QuantityLoaned) - Nz(Sum(R.QuantityReturned) ,0) as StillOut
FROM (Loan as L INNER JOIN LoanBorrow as B
ON L.LoanID = B.LoanID) LEFT JOIN LoanReturn as R
ON B.lbId = R.lbID
GROUP BY L.LoanID
, L.Requestor
, B.ItemID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

EMILYTAN via AccessMonster.com said:
My table structure:-

Loan
-loanID, Requestor
-example :- L00001, Mary

LoanBorrow
-lbID, loanID, ItemID, QuantityLoaned, QuantityOwed (Loaned-Returned)
- example :- 001, L00001, M1010-0990, 4, 2

LoanReturn
-lrID, lbID, ItemID, QuantityReturned
-example :- 111, 001, M1010-0990, 1
111, 001, M1010-0990, 1


I can make it work using VBA but it is not instance change as it uses
forms
current events...so I want it to be in query...
Thanks...
John said:
Without knowing a lot more about your table structure this is difficult to
answer. You could try a query that would look something like the
following.

SELECT R.LoanID,
, Count(B.LoanID) as ItemsLoaned
, (SELECT Count(C.LoanID) as ItemsReturned
FROM [ItemsReturned] as C
WHERE C.LoanID = R.LoanID) as Returned
, (ItemsLoaned - Nz(Returned)) as StillOut
FROM [Requestor Table] as R INNER JOIN [Borrowed] as B
ON R.LoanId = B.LoanID
GROUP BY R.LoanID
I want to build a query which will have LoanQty, OwedQty and ReturnedQty
Click to expand...
[quoted text clipped - 8 lines]
will
produce LoanID, 1 is for borrowing item and 1 is for returning item
Click to expand...
Click to expand...
Click to expand...
 
Back
Top