Hi Nauman,

I meant Request instead of Loan, mistook what you were doing, but the principle is the same.

So lets start by creating a few tables.

BOOKT
BookID - AutoNumber
BookName - Text
InitialQty - Number

EMPLOYEET
EmployeeID - AutoNumber
EmployeeName - Text

REQUESTT
RequestID - Autonumber
BookID - Number [ForeignKey]
RequestQty - Number
EmployeeID - Number [ForeignKey]

(This table has been added to track who has requested the books)

Fill in some values

BOOKT
1  Book1  2000
2  Book2  2000
3  Book3  2000

EMPLOYEET
1  Fred
2  John
3  Alice
4  Sue

Now lets request some books:

REQUESTT
1  1  100  1
2  1  100  3
3  2  50    2
4  3  300  4

Next create a query.

TOTALREQUESTQ
Pull in the BookID and the RequestQty
Click the Sigma symbol (Σ) to add the totals row to the fields and GroupBy BookID, Sum RequestQty.
You will now have a total amount of Books requested by all Employees.

Now create a second query
REMAININGQ
Pull in the BookT and TotalRequestQ, the BookIDs should link.
Add the BookID, BookName, InitialQty to the Query.
Now we want to create a calculated field
Type in:- Remaining: [InitialQ]-[SumOfRequestQty]
Click the Run (!) and you should have

1  Book1  2000  1800
2  Book2  2000  1950
3  Book3  2000  1700

Further Queries can be made to see which employee has what. You could add a date field to the REQUESTT to log what day they are requested, removed from inventory, the list goes on.

Al