new user! Can anyone create this somewhat simple database?

[Kegan Longridge]

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!

 

[Lynn Trapp]

"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.

First, most of the people here are volunteers. If you expect someone to do
your work for you, then you would need to hire them as a consultant. Second,
from the look of it, the application you want will take a lot more than 5
minutes to create.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Kegan Longridge]

Thanks Lynn, I appreciate your commitment, so I will attempt to have someone
help me with a text walkthrough. Any help is greatly appreciated. It is not
that I don't want to do it myself, but do not havethe knowledge to achieve
this.

I have a database with 3 levels.

District (which must have many areas)
Areas (which must have patrollers and contractors)
Patrollers and Contractors (which might do more than one job for each area)

This is my third post and I haven't recieved any information which I could
understand. I am a begginner and need a walkthrough sort of explanation.

I currently have set up:

District table: (following are fields for this table)
-districtID-primary key
-districttype

Area/Patroller Table:
-areapatrol-primary key
-districtID

Area/Contractor Table:
-areacontractor-primary key
-districtID

(at this point you can tell that I have districtID-primary key related to
the distID in Area/Patroller and Area/Contractor tables)

Patroller Table:
-patrollerID-primary key
-areapatroller
-patrollername
-patrollertype

Contractor Table:
-contractorID-primary key
-areacontractor
-contractorname
-contractortype (areapatroller and areacontractor fields related back
to primary keys from areapatroller and area contractor tables)
..........................................................................................................................

So here is the problem. I all the pat(patroller) and con(contractor)
records to relate to an area and then have the area relate back to a
district.
When I make a single form with many subcategories in order of district to
areapat/con and then pat/con it does not want to work and says errors such as
cannot enter duplicate value and such.

If someone can help me in a beginner fashion and use very basic terminology,
explaining it during the way, I would be very greatful.

Thank you for any help you can give, "especially you volunteers"

 

[Lynn Trapp]

Kegan,
Let's start by having you close Access and take out a pencil and piece of
paper. You already have a good start on what you need to do. You should
probably create, at least, 3 tables but they won't look like what you have
here.

Districts
District_ID
District_Type
--Other fields related specifically to a district

Areas
Area_ID
District_ID
--Other fields related specifically to an area

Workers
Worker_ID
Area_ID
Worker_type - Patroller or Contractor (If a worker can be both at the same
time you will need another table to relate to this one)
--Other fields related to a worker.

You have to remember that we do not know anything about your business, so a
lot of this is shooting at a black target in the dark.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html