Hello,

I am looking to create a database that will track trends and do an inventory mix analysis. Every week I receive an inventory perpetual. Ultimately I would like to have it report a series of graphic charts.

I am relatively new to Access and am looking to find out if I am going about it very wrong, best methods to improve it, suggestions to getting past where i am stuck.

I have 3 inventory perpetuals named as of their effective date.

Inventory Perpetual has the following fields:
Cost
Quantity
Item Number
Category (10 different categories. Every item number falls into only 1)
Age (its perishable food)
Batch
Location

I have created additional tables:
Item Detail table with the following fields:
Item Number
Detail
Category
Price ID number

Location table with teh following fields:
Location number
Name
Address
City/State
Zip

I have created a query that breaks out each inventory perpetual by extended cost and puts each "batch" (think of it as an individual invoice on a Accounts Recievable" in an aging bucket (<30, 30-60, 60-90, ect). (called "aging query" going forward) (3 tables =3 queries)

I have created a summary query using the 3 "aging queries" that has the item number and the consolidated extended cost for each item. It looks like:

Item Number Extended Cost Date1 Extended Cost Date2 Extended Cost D3

I am looking to create the following charts as examples:

1. 100% stacked line chart (as excel calls it) using the top 10 item numbers (based either on the most recent perpetual or an average of alll historical perpetuals)
2. a stacked line chart using the 10 different categories (they will contain 100% of the inventory)

Current Problems:
None of the data tables/items have dates attached to them. So I am running into the problem of creating a time series. I suppose i can add an "as of" date to each item in each of the data tables (hopefully by writing a macro that would take the name of data table, which will always be its effective date)

having it automatically create an "aging query" (which i need to be visable for other reasons other than this database) and update the "aging trend" query.
I read about a producedure called "Sp_MSforeachtable". Would that be the best route for this?

Everything here is pretty specific so i am looking for feedback on my approach and suggestions on the best ways to proceed.

Thank you,