Hello,
I'm new here and as for Access I consider myself maybe a little better than a beginner.
Anyhow, I've spent the last couple of weeks building an Access database to replace an Excel sheet. The Excel sheet has shown monthly sales statistics for our and our competitor's products (quantities sold and market share). The statistics have been ordered by market share (percentage, descending) and have shown the top 10 products in each product segment, including our products no matter if they're in the top 10 or not. Typically in each segment we have two of our products, one usually among the top 10 and the other usually around 15-20. What we now present in Excel is something like this:
Rank Product Quantity Market Share 1 Competitor 1 1000 20,0% 2 Competitor 2 900 15,0% 3 Competitor 3 800 10,0% 4 Competitor 4 700 8,0% 5 Competitor 5 600 7,0% 6 Our product 1 500 6,0% 7 Competitor 6 400 5,0% 8 Competitor 7 300 3,0% 9 Competitor 8 200 2,0% 10 Competitor 9 100 1,5% 17 Our product 2 20 0,3%
The point in using Access instead of Excel is that we want a database where we can just import the sales data (with product name, quantity and date) and then pull our monthly reports from it, rather than keeping an Excel sheet for each month (we've considered using a Pivot also but decided against it).
The Access base I've built consists of one main table with the sales data and queries grouped by Product as in the table above. I've also built queries and reports for comparing one month to another month which is of course interesting for us. However I do not know how to construct a report like the table above, ie a kind of Select Top 10 but also "Selecting" any other of our products and showing them in the table.
If you need additional information from me about this, just ask away