Designing a database to track hours spent on projects for multiple (40+) users

[vishal_patel2]

Hi all!



First, I am a complete noob when it comes to Access. I don't know VBA and am just barely learning about Macros.

I'm currently building a database that let's users enter how many hours they spend on a weekly basis on the many projects they are working on. They will also input how many expected hours they will spend (but on a monthly basis) for the upcoming fiscal year. All of these entries will be editable in case of errors and new information.

To design the database, I've made these tables (red fields indicate lookup fields and underlined fields are primary keys):

1. Projects: (ProjID, ProjectName, Priority, Justification, Status, ProjectType, ProjectManager, etc.)
2. Users: (UserID, LastName, FirstName, BadgeNumber, Manager, Business Unit, and a calculated field called FiledAs ["LastName" + "," + "FirstName"]
3. ProjectType: (ID, and ProjectType) - used for the projects table as a lookup field
4. WeeklyRecord: (ID, FiledAs, ProjectName, FiscalYear, then 52 additional fields for each fiscal week)
5. FiscalYear: (ID, FiscalYear)

Is this a good way to about doing this? I know with database design, you need to condense any repeating fields like the fiscal weeks I had in table 4. But what I want is to be able to have a datasheet entry like the one below.

Attachment 8487


A user selects his/her name, then the fiscal year and a list of all projects they have been assigned shows up. They then can change the number of hours they spent on each project with the total on the bottom. There, of course, would have to be another place to assign projects so that they would prepopulate into the subform.

Am I approaching this the right way or is there a better way to do this?

Thanks!

[Robeen]

First piece of advice - don't use lookup fields in your tables.
For some reasons, check this link: http://access.mvps.org/access/lookupfields.htm

[vishal_patel2]

Thanks for your reply. Lookup fields actually have been giving me a hassle with queries and tables. How would I create the database without the lookup fields?