Access Reporting using Field Values as X and Y axis values

[Banniskirk]

Good day,

While I have some experience with VBA programming in Excel I am fairly new to VBA and SQL in Access.

I am putting together a Access based tool to monitor inventory of product sold across a network of stores.

I am using VBA to automatically imports daily data into a simple table (one table per product) with the following fields (I changed the names to make it easier to follow):

[NumKey] [Date] [Productname] [Storenumber] [Inventory]

I need to output the data into a report/table that uses the recent dates as the X-axis values and the Storenumbers on the Y-axis with the table being populated with the inventory quantities.



I would imagine there is a way to do this with SQL but I have no idea how. The only way I would know how to this would be to use variables VBA to create the report in Excel cell by cell.

Any help/suggestions would be appreciated!

[isladogs]

I'm confused reading this.
Do you want an Access report containing a chart or do you want it to have a table your data?
Or do you want to do this in Excel?

[Banniskirk]

Here is a simplified mockup of what the ACCESS table data looks like:



and here is what the data needs to look like to be readable by the analysts:



Ideally I am looking for a way to do this in ACCESS.

[John_G]

one table per product

Before you go any further - don't do it that way, it is poor database design (and will lead to other problems down the road). Use one table for all the data, and since the product name is in the data records, you can filter it as required. This way you can easily make one chart for multiple products (e.g. WidgetA and WidgetB and WidgetC combined). With one table per product that would be a major headache, as would all sorts of other manipulations you might want to do with the data.

[June7]

Your desired output looks like the result of a CROSSTAB query.

I agree should not be a table for each product. Have 1 table and apply filter criteria as needed.

Review http://allenbrowne.com/AppInventory.html