I have a database and cannot set up tables and relationships to make it work.
Table INFO:
First Table Includes: Region ID#(this is the primary key) and 3 other fields
Second Table Includes: Auto ID(autonumber and primary key) District ID# and
Region ID#
Third Table: Patrol ID# District ID# and 5 more fields with info on patrolling
Fourth Table: Contractor ID# District ID# and 5 more fields with info on
contracting
Here is the problem:
I need a 3 layer database:
There are 30 regions which have 125 districts each. Each district may need
more than one entry. Then for all of these districts is information from 2
other tables, the patrol table and the contractor table.
"I see it like a large expanding cone, where it begins with region and
expands out to contractor info and patroller info"
I want to be able to change regions and have the districts be separate for
each region. I also want to be able to change the contractor and patrol
information and have it seperate for each district. yet it still must relate
back to regions.
I have tried many different setups, but the problem seems to be getting
around the primary key and allowing duplicate values for the 2
tables(contractor and patroller) when linking them to the districts, and
still having the districts associated with the regions.
Here is a further, perhaps better explanation of the problem:
What I am trying to do is have a 3 level relationship:
Contractor
Patroller
District Contractor
Region District Patroller
District Contractor
Patroller
Where it is broken down into Region, which has many districts, and each
district has both contractor and patroller information linked to them, which
in turn must also only be for the region selected.
"so each region has many districts and
distinct informaion on patroller and contractor"
I found the problem to be that I could not have primary keys for each table
because they do not allow duplicate values...
Someone tried to help with these comments on an earlier posting, but I
cannot decipher and create what he was instructing:
I hope I understood you correctly. A little guesswork is involved in the
following:
You mean existing DistrictIDs aren't unique and might occur in more than one
region? In that case you should establish a unique index on the
*combination* of RegionID and DistrictID to ensure that any given DistrictID
remains unique within a Region. I would call my autonumber field something
like RegDistID and make sure you use that for relationships with your
"downstream" data.
Well, no need for those tables to have duplicate values. One record in
Contractors for each Contractor. One record in Patroller for each Patroller
(Patrol?). NO District or Region info in these tables.
2 additional tables, to connect Districts with Contractors & Patrollers:
DistrictContractors: Unique index on combination of RegDistID and
ContractorID
DistrictPatrollers: Unique index on combination of RegDistID and
PatrolID (PatrollerID?)
This way a single Contractor or Patroller can be "assigned" to as many
RegDistIDs as is appropriate, but can't be assigned to any specific
RegDistID more than once.
"I am fairly new at access database building and have been assigned to
create one for my company. I have tried many different setups, with many
different relationships, but had no success. If anyone thinks they can
create this setup, can they please e-mail me at (e-mail address removed). I
would be in great debt if anyone could take 5 minutes to create the layout
and send it to me, for I seem to be weak in access.
Thank you for your time in reading this and your replies. Have a great day!
