Development Hours Tracking Database Help

[gopherking]

I am in the middle of creating a database to track the Development hours spent on specific projects on a monthly basis and am seriously questioning my table setup right now as far as reporting and form design. First off, I have two tables that allow users to input the number of hours by month. The first table, Avail_Dev_Hrs, has the following fields in it:

AvailDevHrsID - PK
AvailDevHrsYear - Year that hours are associated with
JAN - Month hours are entered into
FEB - Month hours are entered into
MAR - Month hours are entered into
APR - Month hours are entered into
MAY - Month hours are entered into
JUN - Month hours are entered into
JUL - Month hours are entered into
AUG - Month hours are entered into
SEP - Month hours are entered into
OCT - Month hours are entered into
NOV - Month hours are entered into
DEC - Month hours are entered into

This table is to be used to show how many available hours there are each month of each year for development. The idea is that this table would be used as the starting point and then as hours are added to the second table, a calculation can occur between the two to show the remaining hours.

The second table, Actual_Dev_Hrs, has the following fields in it:

DevHrsID - PK
RequestID - Unique record year and hours are associated to (can be multiple years)
DevHrsYear - Year that hours are associated with
JAN - Month hours are entered into
FEB - Month hours are entered into
MAR - Month hours are entered into
APR - Month hours are entered into
MAY - Month hours are entered into
JUN - Month hours are entered into
JUL - Month hours are entered into
AUG - Month hours are entered into
SEP - Month hours are entered into
OCT - Month hours are entered into
NOV - Month hours are entered into
DEC - Month hours are entered into

The main reason that I chose this setup is that a request can have hours that span over mutliple years (larger projects will take more hours of coding). This way I don't need to have fields for each month/year in a table but can rely on the user to input the right year that each month hours should be applied against. In the second table, the RequestID field is setup to allow duplicates being that multiple years may be needed.

Now, the concern that I have is that the users would like to be able to see a range of months on a report/form, but they could span over multiple years. Right now, I have reports/forms setup to show only 12 months for any given year (does not span multiple years). I am fine if I always show 12 months on a report/form, but am not sure if my table structure would allow me to setup a report/form to allow user input as to which month they would like to start with and then display the next 12 consecutive months.

Did I do this right or does someone have a better idea of how to handle this?!?

 

[spikepl]

Data of same type do not warrant a special column, so your design is not normal practice, and is coloured by what you wish to DISPLAY. That should not prevent a sensible design of data structure.

Hours spent on some given item go into a table

tblHours
--------
HoursID
ProjectID (if you link to projects)
EmployeeID ... and so on
Amount
ReportingDate

This can be presented in whichever way you want, and is much more flexible than preset columns. The column-thing is an Excel way of looking at data, which you should forget about when dealing with Access.