Simple Database design question...

[LittleLozza]

Help - a not very good Access bod needs help!

I am in need of some advice on the best way to build a database. In very basic terms, I have a whole list of projects that I need to log (about 700 at any 1 time plus sub-projects). I also have a list of employees.

I need to be able to record which employees are working on which project(s) at particlular times, recording start and end times. Then, I want to be able to run queires to show which employees have worked on which projects, the %age of employees time working on particular projects etc.

So, my question is... how do I structure these tables and what type of relationship should exist between them?

 

[VBslammer]

If you're a new Access user you should start by generating one of the built-in databases that come with Access. There is one called the "Resource Scheduler" that the wizard can generate in about 10 seconds that will probably be a good enough start for you.

 

[DonQuichote]

Start with the tables. I see the necessity of at least 3 tables in your database:

table tblProject with project id and name and other data,
table tblEmployee with employee id and other data (maybe a link to an existng database?) and
table tblWork containing fields for employee id and project id and fields for the time that the employee worked on the project.

You then create a relation between tblWork and tblProject and another relation between tblWork and tblEmployee

There are a few things to think about first:
How do you deal with subprojects?
How do you want to store the timing info(as a duration or as start and stoptime)?

Good luck

 

[LittleLozza]

This is exactly it. So simple yet so clever! Thanks for your advice.