inventory comparisons - how many have I sold

[the_moto]

I have a simple inventory database for t-shirts. Now I need to be able to find out how many of each size shirt have been sold. Any ideas?

For example:

One record has the shirt name, quantity for 8 sizes, date. I want to be able to compare previous data with new data so I can determine if shirts have been sold. Then update another file or field with the difference and keep a running total.

And this needs to happen for each record.

Let me know if you need more details?

Thanks

[Vaughan]

Why not have a database that holds sale information: each record is a "sale" with a "product" field and a "quantity sold" field. Then just add 'em up.

[falkaholic]

There is a tricky way i have done this.

I have a invoicing system that has the standard lineitems database with the inventory database to draw product info from.

Instead of keeping a running total (which might be a better way) I made a calculation that does find the number sold from the lineitems from the last 30 days and total ever.

I did it using all calculation fields and one new relation.

I made a new calc field for the last 30 days that goes something like this:

If(Today-LineItemDate >30, "",InventoryID)

Then in the inventory database I made a new relation from the InventoryID to the new calc field. Then made a new calc field that does something like this:

Count( NewLineItems::ItemID )

That will give the the number of lineitems, which is the number sold.

For the total number sold its the same thing but with out the calc field in the lineitems.

The draw back to this system is that it has the calculate this stuff when you open the database since it can't store the results.