clballas
asked on
Database design - actual vs budget model
I want to put together a database to produce actual vs budget comparisons and would like some direction in how best to set up the structure. The output will be weekly variance reports by account number for each department as well as for the total company.
The 52 weeks of the calendar year are subtotaled into months in a 5-4-4 pattern. For example Jan consists of the first 5 weeks of the year, Feb the next 4, Mar the next 4, Apr the next 5 and so on. Due to this 'fiscal month' setup I am unsure how to structure the tables so that I can easily construct reports for weekly, month-to-date, quarter-to-date, and year-to-date accumulations and comparisons.
Any advice is greatly appreciated.
The 52 weeks of the calendar year are subtotaled into months in a 5-4-4 pattern. For example Jan consists of the first 5 weeks of the year, Feb the next 4, Mar the next 4, Apr the next 5 and so on. Due to this 'fiscal month' setup I am unsure how to structure the tables so that I can easily construct reports for weekly, month-to-date, quarter-to-date, and year-to-date accumulations and comparisons.
Any advice is greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Some general remarks:
1) To be able to have both week and month reports, your data needs to be recorded on a daily basis. (A week can fall in two months!)
2) Weeknumbering differs depending on the algoritm for the first week. In the US this is in general different from Europe. Personally I created my own weeknumber function, as the first of january can give a week 53 of the previous year....
3) All groupings can be done using the access functions like FORMAT and/or MONTH
Nic;o)
Some general remarks:
1) To be able to have both week and month reports, your data needs to be recorded on a daily basis. (A week can fall in two months!)
2) Weeknumbering differs depending on the algoritm for the first week. In the US this is in general different from Europe. Personally I created my own weeknumber function, as the first of january can give a week 53 of the previous year....
3) All groupings can be done using the access functions like FORMAT and/or MONTH
Nic;o)
ASKER
Thanks Jim and Nico for your suggestions. I am awarding these points to Jim because his was the first reply and I will also post a question for Nico because those comments are also helpful. So reply to that posting and I will award those points to you Nico. I am interested in how the week number function that you created is different from the one in Access.
Hi clballas,
I change the year when the week returned is larger as 5 and the month is january.
When you start the year with the first week having atleast 3 days, then it's possible for 1-1-2001 to endup for week 53 however when you combine this with the year function on the date, you'll end up with a week 53 for 2001 having data of the first of january.
As I always combine Year and week to prevent a weekmix over years...
Just check:
Debug.Print Format(#1/1/2000#, "YYYY-WW", vbMonday, vbFirstFourDays)
Nic;o)
I change the year when the week returned is larger as 5 and the month is january.
When you start the year with the first week having atleast 3 days, then it's possible for 1-1-2001 to endup for week 53 however when you combine this with the year function on the date, you'll end up with a week 53 for 2001 having data of the first of january.
As I always combine Year and week to prevent a weekmix over years...
Just check:
Debug.Print Format(#1/1/2000#, "YYYY-WW", vbMonday, vbFirstFourDays)
Nic;o)
Jim.