beginner w/Relationship prblm

[Kegan Longridge]

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"

 

[Brian Bastl]

Hi Kegan,

Everyone here answering questions is a volunteer. AFAIK, nobody receives
tangible compensation for their contributions, although the MVPs might enjoy
some extra percs and/or recognition not afforded us mere mortals as a result
of their tireless efforts and infinite wisdom.

What is the purpose of the database? Your specific answer(s) will help
others help you to determine appropriate table structures and relationships
for your particular database.

Brian

 

[DawnTreader]

Hello

another thought, dont keep post in new threads. i know it is frustrating
trying to get help through a system like this, but each time you start again
it makes it more difficult to keep track of your problem. additionally if you
keep it all in one thread then others can follow your thread and learn from
it as well.

my first suggestion in this situation is to write out all the field names
that you want to use for each subject of information. dont create fields yet
for the joins. just list what data you want to be grouped together in each
table. each table should be one type of thing. then after deciding all the
table field names then write out how each thing is related. this might give a
better idea of how to create the relationships.

another suggestion is to look at templates and see how they join related
information. there is also trainning available for the latest versions here
in the ms website.

i know where you are at though... i feel frustration at my lack of experince
and inability to get over a couple of hurdles myself.

one other point, dont worry about the thread getting lost, if you keep
posting in the thread it gets "bumped" to the top of the list.

 

[mnature]

Easiest way to do this is walk through it logically.

I have a database with 3 levels.

District (which must have many areas)
District table: (following are fields for this table)
-districtID-primary key
-districttype

This seems logical. I will set this up this way:

tblDistrict
DistrictID (PK)
DistrictType

Areas (which must have patrollers and contractors)

Here is where you are getting ahead of the logic. You need, at this point,
to just worry about the areas that you need to set up, which then relate back
to a particular district.

tblArea
AreaID (PK)
DistrictID
AreaType

Patrollers and Contractors (which might do more than one job for each area)

Now we worry about the patrollers and contractors (which I assume are two
completely different jobs). These are handled by tables which simply list
your patrollers and contractors.

tblPatroller
PatrollerID (PK)
PatrollerName

tblContractor
ContractorID (PK)
ContractorName

At this point, you are probably wondering how these tables could possibly be
related to any of the other tables. This is where the relational database
stuff gets a little complicated. You actually need additional tables to
complete the relationships. They will look like this:

tblAreaPatroller
AreaPatrollerID (PK)
AreaID
PatrollerID

tblAreaContractor
AreaContractorID (PK)
AreaID
ContractorID

These tables will join your Area table with the Patroller/Contractor tables.
You can then have a Patroller/Contractor who is associated with just one
area, or several areas.

Try creating all of these, and setting up the relationships, and then come
back with your questions about how to set up forms and reports. However,
using a wizard to set up forms will probably allow you to have subforms,
which is where you will see all of this come together as it should.