Sales data structure problem
I need some help on a structure problem with some sales data. This probably needs some background:
I'm tracking the sales figures for a number of different retailers. I have:
Weekly Sales Master table
Daily Sales table
Each retailer has a kp_RetailerID and the Weekly Sales Master table has a kf_RetailerID field, which acts as the match.
The daily sales table is matched to the Weekly Sales Master table by a date field - daily sales record within the week of the related Week record has its daily sales data totalled and returned in the SalesAmount field in the Weekly Sales Master table. It's the Weekly Sales Master table that people generally look at (although they can drill down into the daily detail). I need this table rather than just summing up the Daily Sales amounts by week, because sometimes there is no daily sales data and the user needs to be able to put in a manual weekly sales amount into the Weekly Sales Master table.
This is all working fine.
The problem is that previously there were some retailers that were too small to track the individual sales data for, and they got manually bundled up into a retailer called "Other". However, now that we're importing sales data directly into the database, all of the individual sales data for each of those small retailers will be in the system anyway. So they need to somehow be grouped under the "Other" retailer (or at least viewed that way).
How do I get it so that this data can be viewed as a group under this retailer, whereas all the other retailers are viewed individually? And how can it be managed so that if one of this little retailers becomes bigger, it can then be broken away into it's own individual retailer and the grouped sales data under "Other" would adjust accordingly?
Can I give the "Other" retailer numerous kp_IDs separated by carriage return? And then if it needs to be broken out individually, remove its ID from the "Other" kp_IDs?
Or does someone have another suggestion?
Sorry if the explanation is unclear, and thanks to anyone who has made it this far in the reading!