CrystalSurfer
Matrix activist
- Local time
- Today, 06:15
- Joined
- Jan 11, 2006
- Messages
- 75
Hi all,
Please could someone suggest a possible query (or design) solution for the following:
I have a db that records hours of consultancy for our customers. Our customers can have an agreed level of support, giving them a yearly allocation of hours (4, 15 or indeed any number of hours negiotiated)
If the customer does not use up all their allocated hours in the year, we allow a carry over (of upto 4 hrs maximum) to be added to the new year's allocation. So, I want to calculate the total hours of support left for a customer.
For example;
Company A has 15 hours per year. 9 hours of consultancy are done in 2005 for them, leaving them with 6 hours at the end of 2005. 4 of those hours are carried over to be added to their 2006 allocation, thus giving them 15hrs(for 2006) + 4hrs(from 2005) = 19 hours total for 2006.
This needs to be calculated for each and every (calendar) year, which I have done through 'hard-coding' to run for each year specifically; qry2004, qry2005, qry2006, etc.
So each query sums up the hours spent in the year and deducts them from that years allocated total (firstly, adding any carry over from the previous years query) to arrive at a total number of support hours left for that customer.
So, the qry2006 refers to qry2005 (to retrieve any carry over) which in turn refers to the qry2004 (again to retrieve any carry over from that year), and so on.
How could I code this to run without 'hard-coding' the year, thus having to create a new query for each year?
In writing this, Ive thought that maybe one way would be to 'close' off a year and write a carry over total for each customer and have just 2 queries current year and previous year to calculate support hours left.
Eg; closing off 2004 and recording its carryover, then the current year (2006) qry starts with that and refers to 2005qry for it's carry over and finally including any 2006 hours. (This would mean that 2005 hrs could still be entered in 2006 and be included in the calcs!)
?
Thanks in anticipation
Please could someone suggest a possible query (or design) solution for the following:
I have a db that records hours of consultancy for our customers. Our customers can have an agreed level of support, giving them a yearly allocation of hours (4, 15 or indeed any number of hours negiotiated)
If the customer does not use up all their allocated hours in the year, we allow a carry over (of upto 4 hrs maximum) to be added to the new year's allocation. So, I want to calculate the total hours of support left for a customer.
For example;
Company A has 15 hours per year. 9 hours of consultancy are done in 2005 for them, leaving them with 6 hours at the end of 2005. 4 of those hours are carried over to be added to their 2006 allocation, thus giving them 15hrs(for 2006) + 4hrs(from 2005) = 19 hours total for 2006.
This needs to be calculated for each and every (calendar) year, which I have done through 'hard-coding' to run for each year specifically; qry2004, qry2005, qry2006, etc.
So each query sums up the hours spent in the year and deducts them from that years allocated total (firstly, adding any carry over from the previous years query) to arrive at a total number of support hours left for that customer.
So, the qry2006 refers to qry2005 (to retrieve any carry over) which in turn refers to the qry2004 (again to retrieve any carry over from that year), and so on.
How could I code this to run without 'hard-coding' the year, thus having to create a new query for each year?
In writing this, Ive thought that maybe one way would be to 'close' off a year and write a carry over total for each customer and have just 2 queries current year and previous year to calculate support hours left.
Eg; closing off 2004 and recording its carryover, then the current year (2006) qry starts with that and refers to 2005qry for it's carry over and finally including any 2006 hours. (This would mean that 2005 hrs could still be entered in 2006 and be included in the calcs!)
?
Thanks in anticipation
