Table Relationships - Multiple use of the same table

[Iain]

I'm trying to work out a suitable relationship in a database containing a number of routes made up from locations and would like to know if the following relationship is sound.


Each route can consist of one location or be as long as the number of RelatedLocations entered.
It would be helpful for the user to select each Location and RelatedLocation by LocationName.
There is more that a thousand Locations which can be imported from another table and a combo box used to select.

[orange]

Please tell us more about what you are trying to do. Perhaps 3 or 4 lines about what the database is about.

[Iain]

This is still in the embryonic stage but very roughly:

The database is essentially a personal log of mountain walking routes completed by a club member.
Each Mountain can be climbed on it's own or with other mountains.
So, Mtns 1, 2, 3, 4, & 5 could be climbed on their own or in any combination depending on the route compiled by the club member.
E.g Mtn 1 could be climbed with along with Mtn 2 and Mtn 3 but another route might be possible in conjunction with Mtn 4 & 5.
The order the mountains appear on the log would reprsent the order they are climbed in.
The relationship in #1 was thought to be a way in which the same table might be used for selection of the mountains from a table populated with all the mountain names, to minimise data imput on the part of the user.

[orange]

Interesting, but not something I am familiar with. Do you have some sample data of

the routes compiled by the club members

?

[Iain]

Unfortunately not yet.
The mountain names are all Scottish Gaelic names and not user friendly to non Gaelic speakers hence why I gave a generic description in #1.
I suppose what I'm trying to achieve is a log record where one of the fields can hold multiple mountain names selected from the same table, and display these on a form like a small table or something, in a similar same manner which an attachment field works, where one record can have several attachments.

[Rod]

I noticed this thread some time ago and nothing has moved for four weeks. Orange please forgive me for jumping in but I have some ideas for Iain.

Iain: You appear to be using a multi-value field in the Routes table. I don't think this is such a good idea. Instead what I believe would work for you is a separate table of route locations - for brevity I shall call this table, Waypoints. Each Waypoint record is a member of a chain (a chain could have only one link). Each Waypoint record 'points' to the next record, or points to the previous record, or both (or simply has a sequence number thus avoiding all those multiple table requirements). Here's what a 'sequence number' solution may look like for tblWaypoints.

WaypointID (autonumber PK)
RouteID (FK)
LocationID (FK)
SequenceNumber (number)

The sequence number would have to be managed programmatically - quite easy to do actually.

[orange]

No problem Rod. The ball is in Iain's court and I've heard nothing back on this forum.
So, perhaps your post can get Iain's project re-activated.

orange

[Iain]

You appear to be using a multi-value field in the Routes table.

Hi Rod,

thanks for that suggestion.

I have actually given up on this idea and instead of the multi-value field, I now use a series of foreign keys, pointing to the hills table, each with it's own combo box. This allows me to select a series hills that effectively act as waypoints.

It is very basic but it serves the purpose and prevents any mispellings on the part of the user.

That effectively closes this thread.

Thanks again.