Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Database Design (Access 2000)

    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?

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Simple Database Design (Access 2000)

    Your best bet is to buy Microsoft Project.

    Jim

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Database Design (Access 2000)

    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?

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Database Design (Access 2000)

    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.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Simple Database Design (Access 2000)

    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.
    Regards
    John



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Database Design (Access 2000)

    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.

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Database Design (Access 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •