Structure and Searching

C

Cabgolfer

I am trying to create an employee contact database (sounds simple enough).
The catch is one employee can be responsible for multiple counties or states.
Once the database is created, I would like to be able to search any and all
employees working a particular county. Help please.
 
A

Armen Stein

I am trying to create an employee contact database (sounds simple enough).
The catch is one employee can be responsible for multiple counties or states.
Once the database is created, I would like to be able to search any and all
employees working a particular county. Help please.
Click to expand...

This is a good example of a many-to-many relationship.

You'll have an Employee table, County table, State table, and
EmployeeCounty table. This last one cross-references an Employee to a
County.

tblEmployee
EmployeeKey (primary key PK) (autonumber)
EmployeeFirstName
EmployeeLastName
.... other fields

tblCounty
CountyKey (autonumber) (PK)
CountyName
StateKey

tblState
StateKey (autonumber) (PK)
StateCode
StateName

tblEmployeeCounty
EmployeeCountyKey (autonumber) (PK)
EmployeeKey
CountyKey
(you can add a unique Index to these last two fields to prevent
duplicates)

Use the Relationships window to connect all the fields of the same
name together. Enforce Referential Integrity.

Use subforms to manage the list of Counties that an Employee is in (or
the list of Employees in a County).

When you query, join the tables together and specify a County. You'll
get Employees in that County.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Top