Breaking Single Header Record into Multiple Detail Records

[brianmac]

I have a database to capture information from an invoice for tracking inventory. The user will enter the invoice date, invoice number, product name, quantity on the invoice and the unit costs for that product on the form and or subform; after they are done I would like to take that single entry and break into multiple rows in another table to be able to track inventory and cost of goods sold.

Example:
RowId Date Invoice Product Qty Unit Cost
1 01/01/06 12345 ABC 2 $10.00
2 01/01/06 12345 XYZ 3 $15.00

RowId Invoice Product Unit Cost
1 12345 ABC $10.00
2 12345 ABC $10.00
3 12345 XYZ $15.00
4 12345 XYZ $15.00
5 12345 XYZ $15.00

 

[HandsOnAccess]

Is breaking down really necessary??
You can directly update your inventory table.
No need to break it.

Regards,

 

[brianmac]

I only want the user to enter the header info and I need to create the detail info so that I can track the individual items when they are consumed and remove from available inventory. The bases for this database is to track inventory and consumption of that inventory. I don't need the details broken into individual records until I need to mark them as consumed. If I purchase 10 bottles of product ABC for $10/bottle in January and then purchase 5 more bottles of ABC in February for $12/bottle. At the end of February have used 5 bottles since January, I want to make sure I decrement the inventory purchased in January for $10/bottle. So my inventory would have a value of:

5 bottles of ABC at $10/bottle = $50
5 bottles of ABC at $12/bottle = $60

If I do not break the invoice into detail records I am not sure I would know how to evaluate my inventory correctly. If there is a better way to do that I would be open.