Which Relationship Setup?

This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which one
is correct for which I need.

The relationships I'm thinking about are here (please have a look at these
as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES (which
are particular parts of the river) and each reach contains a number of cross
section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily basis,
so the arrive back at the end of the day, go into the database and log their
PROGRESS by selecting a dat, which team they are etc and which reach they
are working on. All the sections are already stored in the database so they
then select a sections (which is controlled by the reach) and then log how
they have surveyed it and who the land owner is etc. The progress with
therefore be logged in a form with a subform. The main form contains the
daily progress data such as the date etc and the subform logs the data
relative to each section.

The link shows two relationships and I can't work out which is correct for
which I need. The three tables causing me an issue are the tblProgress,
tblReach and tblSections.

Any pointers?

Andy

What would you do if, for some reason, they had to resurvey the same
section? Is it possible that the values in tblSections would change?

If so, Option 1 makes sense. If not, Option 2 does.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which
one is correct for which I need.

The relationships I'm thinking about are here (please have a look at these
as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES
(which are particular parts of the river) and each reach contains a number
of cross section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily basis,
so the arrive back at the end of the day, go into the database and log
their PROGRESS by selecting a dat, which team they are etc and which reach
they are working on. All the sections are already stored in the database
so they then select a sections (which is controlled by the reach) and then
log how they have surveyed it and who the land owner is etc. The progress
with therefore be logged in a form with a subform. The main form contains
the daily progress data such as the date etc and the subform logs the data
relative to each section.

The link shows two relationships and I can't work out which is correct for
which I need. The three tables causing me an issue are the tblProgress,
tblReach and tblSections.

Any pointers?

Andy

Thanks Doug!

People are going to talk! I'll have to ban you from answering my queries
;-)

Thanks for all your help over the last day or two.

Andy


"Douglas J. Steele" wrote in message
...
What would you do if, for some reason, they had to resurvey the same
section? Is it possible that the values in tblSections would change?

If so, Option 1 makes sense. If not, Option 2 does.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which
one is correct for which I need.

The relationships I'm thinking about are here (please have a look at
these as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES
(which are particular parts of the river) and each reach contains a
number of cross section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily
basis, so the arrive back at the end of the day, go into the database and
log their PROGRESS by selecting a dat, which team they are etc and which
reach they are working on. All the sections are already stored in the
database so they then select a sections (which is controlled by the
reach) and then log how they have surveyed it and who the land owner is
etc. The progress with therefore be logged in a form with a subform.
The main form contains the daily progress data such as the date etc and
the subform logs the data relative to each section.

The link shows two relationships and I can't work out which is correct
for which I need. The three tables causing me an issue are the
tblProgress, tblReach and tblSections.

Any pointers?

Andy

I think 1 is correct EXCEPT that you have the relationship backwards.
SectionID should go in the progress table rather than ProgressID going in
the section table and ReachID should be removed from tblProgress. Progress
is your daily report and you want to report which sections you worked on
which days. tblProgress should be:
ProgressID
SurveyDate
TeamID
WeatherID
SectionID
Comments

Option 2 links Progress to Reach which will not give you the detail that you
want and it still has progressid in tblSections.

Your problem is solved by correcting the direction of the relationship.

"Andy Roberts" wrote in message
...
This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which
one is correct for which I need.

The relationships I'm thinking about are here (please have a look at these
as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES
(which are particular parts of the river) and each reach contains a number
of cross section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily basis,
so the arrive back at the end of the day, go into the database and log
their PROGRESS by selecting a dat, which team they are etc and which reach
they are working on. All the sections are already stored in the database
so they then select a sections (which is controlled by the reach) and then
log how they have surveyed it and who the land owner is etc. The progress
with therefore be logged in a form with a subform. The main form contains
the daily progress data such as the date etc and the subform logs the data
relative to each section.

The link shows two relationships and I can't work out which is correct for
which I need. The three tables causing me an issue are the tblProgress,
tblReach and tblSections.

Any pointers?

Andy

Thanks for the advice

I'm sure you are right but... (bear with me)

The reason progress and reach are linked is because the reach dictates which
sections are "available" to progress as the reach seletion filters the
available sections. If I do what you say won't I loose the ability to
filter as above?

I appreciate your help

Andy

"Public" please no wrote in message
...
I think 1 is correct EXCEPT that you have the relationship backwards.
SectionID should go in the progress table rather than ProgressID going in
the section table and ReachID should be removed from tblProgress. Progress
is your daily report and you want to report which sections you worked on
which days. tblProgress should be:
ProgressID
SurveyDate
TeamID
WeatherID
SectionID
Comments

Option 2 links Progress to Reach which will not give you the detail that
you want and it still has progressid in tblSections.

Your problem is solved by correcting the direction of the relationship.

"Andy Roberts" wrote in message
...
This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which
one is correct for which I need.

The relationships I'm thinking about are here (please have a look at
these as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES
(which are particular parts of the river) and each reach contains a
number of cross section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily
basis, so the arrive back at the end of the day, go into the database and
log their PROGRESS by selecting a dat, which team they are etc and which
reach they are working on. All the sections are already stored in the
database so they then select a sections (which is controlled by the
reach) and then log how they have surveyed it and who the land owner is
etc. The progress with therefore be logged in a form with a subform.
The main form contains the daily progress data such as the date etc and
the subform logs the data relative to each section.

The link shows two relationships and I can't work out which is correct
for which I need. The three tables causing me an issue are the
tblProgress, tblReach and tblSections.

Any pointers?

Andy

No. You would use the relationship between section and reach if you wanted
to filter by reach. You could store reachID and SectionID in the progress
table if that makes more sense to you but it is unnecessary and technically
a violation of second normal form. Having a foreign key to Section from
Progress automatically gets you to Reach if you include the Reach table in
the join.

"Andy Roberts" wrote in message
...
Thanks for the advice

I'm sure you are right but... (bear with me)

The reason progress and reach are linked is because the reach dictates
which sections are "available" to progress as the reach seletion filters
the available sections. If I do what you say won't I loose the ability to
filter as above?

I appreciate your help

Andy

"Public" please no wrote in message
...
I think 1 is correct EXCEPT that you have the relationship backwards.
SectionID should go in the progress table rather than ProgressID going in
the section table and ReachID should be removed from tblProgress.
Progress is your daily report and you want to report which sections you
worked on which days. tblProgress should be:
ProgressID
SurveyDate
TeamID
WeatherID
SectionID
Comments

Option 2 links Progress to Reach which will not give you the detail that
you want and it still has progressid in tblSections.

Your problem is solved by correcting the direction of the relationship.

"Andy Roberts" wrote in message
...
This one is driving me mad!

I'm going round and round in circles and I think it all hinges on my
relationships between my tables. I can't seem to make my mind up which
one is correct for which I need.

The relationships I'm thinking about are here (please have a look at
these as I explain my requirements)

http://www.blue-bean.co.uk/rel.htm

We have to survey a large river and that river is made up of REACHES
(which are particular parts of the river) and each reach contains a
number of cross section locations that need to be surveyed.

What I want to do is log the progress of my survey teams on a daily
basis, so the arrive back at the end of the day, go into the database
and log their PROGRESS by selecting a dat, which team they are etc and
which reach they are working on. All the sections are already stored in
the database so they then select a sections (which is controlled by the
reach) and then log how they have surveyed it and who the land owner is
etc. The progress with therefore be logged in a form with a subform.
The main form contains the daily progress data such as the date etc and
the subform logs the data relative to each section.

The link shows two relationships and I can't work out which is correct
for which I need. The three tables causing me an issue are the
tblProgress, tblReach and tblSections.

Any pointers?

Andy