Table Stucture Help - Tracking Varying Time Periods

[themach]

Hello,

I'm having a hard time trying to figure out how I should go about designing the table structure for the hours tracking for the database I'm working on. Basically, I have a table with projects (tblProj) that I need to track the number of labor hours worked per month. The problem lies in the fact that each project could have varying time periods, anywhere from less than a year to more than a year and can start and end at any time of the year.

I was thinking of creating a second table (tblProjHours) to store the hours for the project but I'm not sure what the most efficient way of doing this would be. My idea was to create a table as follows:

tblProjHours
ProjHoursID (pk)
ProjID (fk)
BeginDate
EndDate
Month1
Month2
Month3, etc....

I would create a large number of fields/columns, something like Month1 to Month36 just to make sure I have enough months to enter in the hours. This is of course inefficient since some projects would be way shorter than the maximum allowed months set by the table structure and there is always the possibility of surpassing the maximum allowed months based on the table structure. I was wondering if someone had any better ideas on how I should pursue the design. Thanks in advance!

TheMach

 

[The_Doc_Man]

NO.

The list of months you show is what is called a "repeating group" and is a serious no-no in terms of database normalization. I strongly urge you to read up on that topic.

You can search Access Help for normalization. For Wikipedia (.org) and Google searches, you want DATABASE NORMALIZATION, since there are other non-db-related types of normalization. E.g. diplomatic, mathematical, geometric, ...

For a Google search, limit your reading to some decent .EDU sites for colleges you know by reputation. A few vendor sites from a .COM domain would be OK as long as you recognize the names of the vendors or their products.

Tracking hours is not so bad a thing to do, but be careful. Be VERY careful. Despite the obviousness of it all, you DON'T want to track hours in a time field. You want to track it as either hours and fractions (using type SINGLE or DOUBLE) or as minutes (perhaps using type LONG) and then do format conversions. Search this forum for articles on timesheets, time difference, and anything to do with storage and tracking of time to see some detailed explanations of why this is so.

 

[themach]

The_Doc_Man,

Thanks for your help! I think I figured out the table structure now:

tblProject
ProjectID (pk)
Project

tblProjectHours
ProjectID (fk)
HoursID (fk)

tblHours
Hours(ID) (pk)
Cost Category
Month (date/time)
Hours (double)

I think this is right, but now I've run into a new problem when it comes to displaying the hours. Since Month is a record and not a field, it doesn't seem like I can display the hours horizontally. For a selected Project in the main form, I wanted to display its hours in a subform as follows:

[Month] Jan '07 Feb '07 Mar '07 Apr '07
CostCategory1 46 54 49 48
CostCategoryX 41 23 55 32

In other words, I need to display the Month record in a Continuous Form horizontally (which I believe is not possible) for each CostCategory and its associated hours. As I stated earlier, the problem is that the time period is different for each project. I've searched this forum and have been unable to find information or examples that could help. Any ideas? Thank you so much for your help thus far!

TheMach