Easier way to accomplish this task?

[Matt Knoll]

What I am trying to create is an access database that can be used to lookup
monuments by description. I will try to describe this the best I can and
how I am looking to do it. I just wonder if there is something I am missing
or if there is an easier way.

For example Section 1 Township 28 Range 8 SouthQuarter would then point me
to the record of Monument A, however that Monument A needs to also be
pointed to by Section 2 Township 27 Range 8 NorthQuarter.

Is the easiest way to go about this to enter each Monument in thier own
table and designate them an ID then list the possibles in another table and
just point each one to the correct ID?

I realize it might actually be easiest to just enter each full record, I
guess I am trying to cut down on the redundancy and make it easier to
update.

Thanks
Matt Knoll

 

[Rebecca Riordan]

Matt,

You have good instincts, but you're not _quite_ there. I suspect that you
have a many-to-many relationship between monuments and descriptions -- there
is (or could be) more than 1 monument in Section 1 Township 28 Range 8 South
Quarter, right?

So, you need three tables. One for monuments, one for descriptions, and a
third one to store just the primary keys from each. This is called a
"junction table", and reconciles the m-to-m.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

 

[Matt Knoll]

I think it's only one to many, I omitted a bit of detail, these monuments
mark the corners or quarter corners of a section.

SouthQuarter actually designates the exact location of the monument so there
is only one Section 1 Township 28 Range 8 SouthQuarter. For example, for
any given section there are only 9 spots a section corner can be,
NW,SW,NE,SE,E 1/4,W 1/4,S 1/4, N 1/4, or center.

But your repsponse does lead into my next question. Which method 2 table or
3 table would be better for the lookup process. In that I mean when I go to
lookup monuments if I enter incomplete data for example 1,28,8 but don't
specify SouthQuarter. Will I be able to list all monuments that match 1,28,8
easily?

Thank you very much for the help so far.
Matt

 

[John Vinson]

I think it's only one to many, I omitted a bit of detail, these monuments
mark the corners or quarter corners of a section.

SouthQuarter actually designates the exact location of the monument so there
is only one Section 1 Township 28 Range 8 SouthQuarter. For example, for
any given section there are only 9 spots a section corner can be,
NW,SW,NE,SE,E 1/4,W 1/4,S 1/4, N 1/4, or center.

But your repsponse does lead into my next question. Which method 2 table or
3 table would be better for the lookup process. In that I mean when I go to
lookup monuments if I enter incomplete data for example 1,28,8 but don't
specify SouthQuarter. Will I be able to list all monuments that match 1,28,8
easily?

It sounds to me like you need a table with a four-field Primary Key:
Section, Township, Range, and Corner. This would let you look up any
combination - i.e. all the monuments in Section 1, Township 28; or
(though this would be odd) all the NW monuments in Range 8, whatever
section or township they were in.

I presume the table would have at least one additional field with a
description (e.g. of the nature of the monument - "brass plaque in 3'
concrete footing" or "rusty spike in half-rotten willow tree stump"
<g>

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Matt Knoll]

I don't quite understand, are you suggesting I use only 1 table for the
whole thing?
I was looking at using 2 tables because 4 corners can be the same monument.
It just seems easier to have to only update the monument once and have all 4
instances of it
updated. Because the monuments have a lot of additional infomation, up to 3
witnesses,
distances, date they were last located, etc. So it seemed easier to keep
this information
seperate and have the location point to the monument via an id number.

 

[John Vinson]

I don't quite understand, are you suggesting I use only 1 table for the
whole thing?
I was looking at using 2 tables because 4 corners can be the same monument.
It just seems easier to have to only update the monument once and have all 4
instances of it
updated. Because the monuments have a lot of additional infomation, up to 3
witnesses,
distances, date they were last located, etc. So it seemed easier to keep
this information
seperate and have the location point to the monument via an id number.

OK... I didn't understand that. Sounds like you have a one (monument)
to many (one to four) corners relationship. Then yes, you need two
tables.

You could still use the four-field primary key for the Corners table;
just have a unique MonumentID in the Monuments table, and a foreign
key for it in the Corners table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps