Hello again. Looking for great advice that I always get on this forum. I'm working on a new application that tracks sales.
Let's say the application has these characteristics:
Let's say the application has these characteristics:
- There are 800,000 salespersons each with unique ID (I).
- The number of product groups being sold could be 6 to 12 (P).
- Each product group could have up to 20 subgroups (S).
- Each subgroup will have 12 attributes which is where sales quantities are given (A).
- This sales data will be imported once a month.
- A date for the monthly sales data will either be included or it can be generated.
- Show me most recent sales for Salesperson (I1) for Attribute (A1) for Subgroup (S1) for Product (P1).
- Show me total sales for Attribute (A1) for Subgroup (S1) for Product (P1) for last 12 months.
- Show me best selling Attribute (A1) for Subgroup (S1) for Product (P1) for last 12 months.
- Show me most recent sales of all Attributes for all Subgroups for all Products.
- Show me total sales of all Attributes for all Subgroups for all Products for last 12 months.
- Since the number of potential fields for just this data could be 12x20x12 or 1,368, I can't put all products in one table (255 field Access limit). I suspect that Access' 2GB limit will also be a problem. Therefore, linking the tables from different databases (D1 thru D12) is an option. What would queries generally look like if I'm summing multiple product groups?
- Is this too much to ask of Access?
- If not, besides the obvious indexing that would be required, is there any other performance consideration?