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

-- 
Message posted via http://www.accessmonster.com

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

"EMILYTAN via AccessMonster.com" <u33296@uwe> wrote in message 
news:74ffa15dcdb29@uwe...
>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
>
> -- 
> Message posted via http://www.accessmonster.com
> 

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 Spencer wrote:
>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
>>
>[quoted text clipped - 8 lines]
>> will
>> produce LoanID, 1 is for borrowing item and 1 is for returning item

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1

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" <u33296@uwe> wrote in message 
news:75095b72d8a02@uwe...
> 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 Spencer wrote:
>>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
>>>
>>[quoted text clipped - 8 lines]
>>> will
>>> produce LoanID, 1 is for borrowing item and 1 is for returning item
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1
>