Inventory Carryover

[mrabrams]

I am sorry if this has been addressed before, but I can't make it
work for me. This is what I would consider a 'small' inventory db.
Here is what I have:
Supplier
Count_of_Inventory_Received
Date_Inventory_Received
Count_of_Inventory_Worked
Date_Inventory_Worked

My tables:
tbl_Supplier
Supplier_ID (PK)
SupplierName

tbl_Received:
Received_ID (PK)
Supplier_ID (FK)
Date_Received
Count_of_Received

tbl_Worked
Worked_ID(PK)
Supplier_ID (FK)
Date_Worked
Count_Of_Worked

Relationship is one to many on Supplier_ID.

I need a query (to be used for a report) to show the counts of each supplier
1)received
2)worked
3)still open

based on a date parameter.
I have no problem writing the query for received & worked, but it's the
"still open" that is giving me problems. I know I need to account for
inventory left over from the previous time frame.
Example:
Receive 100 units on 3/5/2001
Receive 130 units on 3/7/2001
Worked 50 units on 3/7/2001

Query (Date parameter "Between 3/4 and 3/9/2001") would give me
Total_Received:230
Toatl_Worked : 50
Total_LeftOver: 180

Then I receive 50 units on 3/11/2001
Then I work 40 units on 3/12/2001
Query (Date parameter "Between 3/11 and 3/16/2001") would give me
Total_Received: 50
Toatl_Worked : 40
Total_LeftOver: 10 ******* I want this to be 190 (180 from previous + the
10 from this week)

I do realize that storing the "Leftover" isn't correct, but how else could this be done?

Can someone help me out? Feel free to tell me the tables aren't correct or
whatever. I can take it!!! Especially being the weekend, any help
is much appreciated.

Thanks a lot.

Michael Abrams

 

[pcs]

think your problem is the Between Date range...the tbl_Worked date can fall outside the the date range you select.

could you add a LotNumber (probably an Autonumber field) to tbl_Received and tbl_Worked to tie the two together?

think you could then create queries that will account for the left_overs...

hth,
al

 

[mrabrams]

Thanks Al for the suggestion.
I'll be working on this at home this weekend and if I still can't make a go of it I'll post back with an update !!

Have a good weekend.

Michael Abrams