Running an inventory query

[EDEd]

Folks,
New guy here putting together a database to track a few inventory items. My job is using Access 2003. I want to be able to take a weekly inventory of products. I figured out how to use a query to print a list of products that should be reordered based upon minimum sotkc level - current level = reorder quantity. I am trying to figure out how to track the usage of a product. Can I make that query note the current date and inventory level and update some table each time. So I could look at an item and see each week how many of that item was ordered?
Wow a lot of typing for that question.
Thank you in advance.


Ed

[ajetrumpet]

it's impossible to answer without seeing the data Ed. Inventory is complicated...

basically, (IMO), a good inventory setup IS going to have a transactions table. or 2, or maybe even 3 of them. if inventory goes out, you should always record it, regardless of where you put it. if you have the right fields then in the ''recording'' table, you can easily write the following to see how much of a certain product went out any given week:

select productname,   sum([quantityfield])

from table

group by productname

having [productname] = "criteria section" and datefield between #startdate# and #enddate#