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:
Category (10 different categories. Every item number falls into only 1)
Age (its perishable food)
I have created additional tables:
Item Detail table with the following fields:
Price ID number
Location table with teh following fields:
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:
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)
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.