Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Thanked 0 Times in 0 Posts

    help with database design (Access 2000)

    First off HELLO! I am new here and fairly new to Access.

    I was wondering, if I have a table with say, employee data, and I were going to build another table to track crews (groups of employees), what would be the easiest way to assign employees to a crew?

    thanks for your input,

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Virginia, USA
    Thanked 1 Time in 1 Post

    Re: help with database design (Access 2000)

    Welcome to the Forum! I've been hanging around these parts for about five years now, and I feel you've come to the very best place to get this kind of help. I owe a debt of gratitude to the good people who've helped me. The best way to start repaying the debt is to help someone myself. So here's what I'd do in your situation. (The card-carrying programmers here will have more to say, perhaps, but this'll get you started.)

    I'll assume you already have a spreadsheet or even an Access table with the employee data

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: help with database design (Access 2000)

    Hello! Welcome to Woody's Lounge.

    What you probably want is a setup where one employee can belong to several crews, and several employees can form a crew. This is called a many-to-many relationship between employees and crews. Many-to-many relationships are implemented by using three tables, in this case:
    - The employees table, with a primary key (for example) EmployeeID (preferably an AutoNumber field.)
    - The crews table, with a primary key (for example) CrewID (preferably also an AutoNumber field.)
    - An intermediary table describing crew members. This table contains EmployeeID and CrewID fields (both number, long integer fields, not AutoNumber) that are linked to the other tables, plus if necessary other fields that are specific to an employee/crew combination. Each record in this intermediary table describes a certain employee being a member of a certain crew. So if employee Jones is member of three crews, there are three records with Jones' EmployeeID (and different CrewID's). And if a certain crew consists of seven employees, there are seven records with this crew's CrewID (and different EmployeeID's)

Posting Permissions

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