Another Table/Relationship Question

[Squeally]

Hello All! I'll first apologize as I haven't used access in 9 or 10 years or so. I have developed 2 tables for the data I collected. It's basically a 2-page data sheet. So, follow me here...there must be a page one. However, there does not have to be a page 2. If there is, it must relate to a specific page 1. There can also be multiple page 2s (with different information of course) that must relate back to a specific page 1. What is the best way to do this? The only duplicate data are GPS coordinates in each table, but that may have just been a way to keep the datasheets together. If anyone needs specific information, it can be provided...I just didn't want to bore you. Hopefully, that is reasonably clear. Thanks!

Tom

[June7]

Records of associated tables are related by primary and foreign key fields. Sounds like your PK/FK is GPS coordinates.

[Squeally]

Thanks June! That's what I was thinking, but I was having trouble getting it to work (due to my ignorance of course!). Should I get rid of the auto-generated primary key and choose lat and long?

[June7]

The autonumber only serves as PK if the value is saved in related table as FK in a number (long integer) field.

Leaving the autonumber field doesn't hurt, just don't designate it as PK if the value is not in related table as foreign key.

If both tables have autonumber field then there is no relationship based on those fields.

Are Lat and Long in separate fields? Are these pairs unique?

Most db designers would recommend the autonumber be used as PK. This would require updating a field in related table with the autonumber value as a foreign key.

[Squeally]

The lat/long are separate fields with 10 decimal places each (generally recorded in decimal degrees). So, every pair would be completely unique.

[June7]

You can guarantee uniqueness? That would be a compound PK. I avoid that whenever possible but it should work.

[Squeally]

I can guarantee that no lat/long combination will be repeated with 10 decimal places. Out of curiosity, why do you avoid compound PKs?

[June7]

Because if a related table is needed, both values must be saved into related table as foreign key. Queries must join the tables by linking on both pairs of fields.