Here comes a tricky one. I need to make a query to list tied agents and their budget factor for each sales day throughout a year. There is 100 sales agents and about 200 sales dates (per year).
Ideally, I would like a solution. But good help on where and how to start would be appreciated.
One solution for me would be, to simply make a lot of nested queries. But my final solution needs to perform well also, so tips on how to do this the most efficient way is really appreciated!
The starting point is that each tied agent has a budget factor on 1 each day. Then reductions should be pulled off, and the final result should be a list of all days for all tied agents with a budget factor per tied agent per day. All of this should be calculated for a time period, typically a year or a month.
The following is a short explanation of the tables and a specification of fields (only relevant fields are included)
One table contains every date with a field specifying a budget reduction percentage for that date. For example weekends and holidays etc. have a weight on 0%, and regular sales days have a weight on 100%. The values are stored as 0-1 values (actual percentage).
Another table holds information about the tied agent, and the period for which he or she is "active" as tied agent (date-interval). This is relevant regarding budget allocation, since we only want to allocate budget to this tied agent in his/her active period. The tied agent has a foreign key to a employee table, this is because an employee can in theory have more than one tied agent relations during a lifetime, i.e. if he or she changes department, then there will be a tied agent relation to each of the departments at two different time periods. These time periods can never overlap. "dtmActiveTo" can be null (it mostly is), and in that case, the tied agent is active ongoing.
A third table contains information about each employees holidays, school etc., where the employee is having no budget allocated. Each record holds a date-interval, for which, the employees budget should be completely removed (or reduced to 0).
A fourth table contains other budget reductions related to a tied agent, i.e. if the tied agent is only working part-time etc. Each record consists of a reduction percentage in a date interval, and a priority. Priority is because these records can have overlapping time intervals, so the calculation should start by reducing budget with priority 1 reduction first, and 2 thereafter etc. The values in the dblReductionPct are stored as 0-1 values (actual percentage). Also here, the "dtmEnd" field can be null. And as before, in that case, the reduction is ongoing.
As said, the result should be a list of all sales days (0% weight days can be removed at the beginning) for all tied agents with the final budget factor for each tied agent each sales day.
For what purpose?
By this information it is afterwards possible to allocate X amount of dollars as yearly/monthly/weekly budget etc. to each tied agent each sales date, depending on the budget factors.
I'm using Access 2007 in a split backend/frontend environment with all data in the backend.
Thanks for any, any help!!!