Table Design Question

[DavidSt]

I have a database that I use to track my hiking activities. In this database
I have a master list of all trails such as:

ABC Trail
DEF Trail
HIJ Trail

Now here is my problem...Most times when I hike somewhere; I take a
combination of trails such as: ABC Trail/HIJ Trail.

How do I best capture that combination without defining a table that has
multiple trail fields such as: TrailPart1, TrailPart2 etc.

Many thanks,

 

[Duane Hookom]

Consider creating a table of hikes

tblHikes
===============
HikeID autonumber primary key
HikeStartDate
HikeEndDate
HikeComments
Hike...

tblTrails
=======
TrailID
TrailName
TrailLength
Trail...

tblHikeTrails
==============
HikeTrailID
HikeID link to tblHikes.HikeID
TrailID link to tblTrails.TrailID
Comments

 

[fredg]

I have a database that I use to track my hiking activities. In this database
I have a master list of all trails such as:

ABC Trail
DEF Trail
HIJ Trail

Now here is my problem...Most times when I hike somewhere; I take a
combination of trails such as: ABC Trail/HIJ Trail.

How do I best capture that combination without defining a table that has
multiple trail fields such as: TrailPart1, TrailPart2 etc.

Many thanks,

By creating a separate tblTrailsHiked table and linking it to the main
table using a HikeID prime key field. You can then have many trails
linked to the same HikeID.

tblHikesTaken
HikeID 'Autonumber No duplicates
HikeDate

tblTrailsHiked
HikeId ' Long Integer
TrailName

Also, since you may hike part of the same trail more than once, I
would have a separate table of all trails, and use a combo box to
select the trail for the TrailName field.

Look at the Northwind.mdb sample database that ships with Access to
see how this all comes together.