Tracking Inventory Lent Out

  • Thread starter Thread starter RichardY
  • Start date Start date
R

RichardY

I have created a database to track when a unit is borrowed and returned
I setup three tables - Client Table, Unit Table, and Sign-Out Table.

The Sign-Out Table captures the following information:

- Order ID (Primary Key set as an AutoNumber)
- Unit ID (a one-to-many relationship with the Unit Table)
- Client ID (a one-to-many relationship with the Client Table)
- Date Out (the date when the unit was borrowed)
- Return Date (the date when the unit is expected to return)
- Quarter (the quarter the unit was borrowed in, i.e., Q1, Q2, Q3, o
Q4)

Using a Crosstab query and counting the Order ID field, I was able t
generated a report that showed how many times a particular unit wa
borrowed over the period of a fiscal year (i.e., Q1-Q4 for a particula
year). My problem is that I want the report to also show all the unit
that have not been lent out in the fiscal year.

Please help
 
Forget about the Crosstab query and perhaps try this?

1)Create new Query in design view loading Sign-Out table
only.
2)From menu bar click "View ... Totals".
3)Select Unit ID and leave "Total" as "Group By".
4)Select "Client ID" (or other field where there is data
in every record) change "Total" to "Count".
5)Enter "Date Out" and "Return Date" or if it's
simpler "QTR" manually to restrict the data output
6)This query will give you lent-out occurrences by product
lent
7)Now create ANOTHER Query loading Unit table and Query1.
8)Link from Unit table to Query 1 using item code
9)Select Item code from Item table and "Countof ..." field
from Query1
10)Edit the join properties, choosing option 2
11)Sort descending by "Countof ..." field
12)You can now link your Report to this second query and
it will then show all items whether lent out or not during
the period.

Hope this works for you ...
 
Back
Top