Solved

Database design - actual vs budget model

Posted on 2002-04-12
5
652 Views
Last Modified: 2011-10-03
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.  
0
Comment
Question by:clballas
  • 2
  • 2
5 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 6936560
clballas,

  The way to structure the tables is to simply include a date/time field along with the posting.

  There are a multitude of date/time functions that you can use to extract a month, week #, year, etc.

Jim.
0
 
LVL 57
ID: 6936568
Note the only other thing you'll need is a "Fiscal calendar" table with the last day of each month and the period it's considered to be.  This will serve as a lookup table to determine the fiscal period for a given date.

Jim.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6936888
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)
0
 

Author Comment

by:clballas
ID: 6938735
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6938858
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)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now