Simple Database Design (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

Your best bet is to buy Microsoft Project.

Jim

Reply | Quote

The relationship between Project and Employee will be many to many, therefore you will need an intersection table between these. This intersection table will hold the key to the Project table, the key to the Employee table and other data specific to that Employee’s work on that Project, such as the time data for each “block” of work.

If a Project has many SubProjects, but a SubProject belongs to only one Project, the relationship between Project and SubProject is one to many. Hold the key to Project in the SubProject table.

If there is a place in which an Employee’s total time is held (let’s say on a weekly basis; as in 40 hours per week), you should be able to use queries against this design to get the information you want.

Is this enough to get you started?

Reply | Quote

If you want to use Access for this task (MS Project is an alternative, but I wouldn’t jump to the conclusion that it’s the “best” bet), the basic framework for the tracking functions you describe could be built around three tables. The first would contain employee information, the second would contain project information, and the third would be a “participation” table that would link employees (referencing the employee table) to projects (referencing the projects table). This third table would, in addition to these links, contain the dates of participation and any other relevant information about the employee’s participation in the project. Once this is set up and the tables are populated, you could begin building queries that would provide the types of information you’re looking for (who worked on what, how much, when, etc.).

If this is all somewhat boggling, I’d suggest picking up a book on the subject (I found “MS Access Step-by-Step” very educational).

Hope this gets you going.

Reply | Quote

Not sure whether you need this, but if you want to know when an employee worked on a particular project then you need a fourth table : work_records table, which hangs off the participation table.
The participations table is about staff assigned to projects, and may include anything about the assignment as a whole, eg. target hours. But for each assignment there will be many work records, each with a date, hours completed etc, so these go in a separate table.

Reply | Quote

Other Loungers have already given you useful tips on the tables you need.

If you’re not very experienced with Access, you might play around a bit with some of the databases you can create with the Database wizard (start Access; in the dialog that appears, under “Create a new database using”, select “Access database wizards, pages, and projects”. Click OK, then select the “Databases” tab. You might find the Resource Management and Time Billing (don’t know the exact names in English) interesting. Create the database, then look at the table structure, relations etc.

Reply | Quote

I’m up and running. Just wanted to say this has all been really useful and thanks for your help.

Reply | Quote