I am working on building a sales tracking and analysis system. I think I know what I need to do to achieve my goals but there may be a simpler route.....

We are tracking multiple products sold through a network of 3rd party stores. Our products are also kept in the third party warehouses and we don't have specific sales data by store.

We do have daily inventories per store that we can source from the web. I have created a macro that pulls this info in excel, re-formats and then access pulls it into a table with the following fields:

ID
Inventory Date
Product ID
Store Number
Quantity

I have been using Pivot table to output a table that can be read to look at changes in inventory.

I want to be able to calculate things like average weekly sales per store. To do so I will need to consider any additions to store inventory in order to properly calculate sales from the inventory numbers. For example, if the product comes in cases of 12 units and the store inventory increases by 11 units from one day to the next we can assume that one case was received and one unit was sold that day. Of course that store could have received 2 cases (24 units) and sold 13 but I have no way of determining this from the available data so I can live with the more conservative calculations of sell through rates.

I know how to calculate such things using VBA in excel on table like the one obtained from the Pivot table function, so I would need to output from access to excel do this and then probable import the results for a given period (lets say total received, total sold, sold per week, number of days between new inventories, number of days without any inventory etc.) to a new table in access to then output a concise report that can be sorted to identify potential over and under achievers.

Would anyone have any insight into whether I could do this in a simpler manner directly in access or do I absolutely have to pass through excel to do this?

Many thanks for any insight you might have!



Ken