relationships! going in circles

[EssieP]

I just don't get it. I want to make related databases but it rarely works.

I have a simple database to log cycle rides.
the idea is that I go cycling once a day, sometimes I pass through lots of villages, sometimes only a few. I want to be able to set up a form to enter new rides, and use a subform to enter the places that I rode through.

the tables are: (the ID fields are keyfields)
---
tblRides:IDride, date, duration, mileage
---
tblroute:IDride, IDplace
---
tblwaypoints: IDplace, placename, county

I just cannot get the tables to relate properly. Even with ID fields set to the right type, the relationships always fail. It tells me that it requires the key fields to be of the same type (done that).

So my question is - is there a way to set up a simple relationship so that it works? There is nothing in the many textbooks I have that helps here. I have 3 that I followed step by step , with screenshots for each and they still give inexplicable error messages.

I'm not really a new user, because I have struggled with this for at least a year on and off. This really is the only program I get annoyed with it just seems to be lucky-dip whether it's going to work or not. Is it true that you all get this basic function going. I have asked the technicians at work and they can't get these files to work in a predictable way either.

I actually have one such database that does work, I got as far as making the form/sub-form and it all seems just right. Others before and since refuse to co-operate. Why does it seem to random?

I am using office 2000.

 

[daveUK]

Delete TblRoute, you don't need it. Just use the other 2 tables. TblRides will be the main form with TblWayPoints as the subform.

Forgot to add that IDRide will be the foreign key in TblWayPoints.

 

[EssieP]

none the wiser

I did that, and it will only allow me to do a one-to-one relationship, but isn't that wrong because one ride can have any number of visited waypoints. In fact some rides will need a placename twice, on the way out and coming back home. I thought it would need a one-to-many?

Is there a way to predict what kind of relationship you are going to get before you go to the relationships window? or is it just chance?

 

[Pat Hartman]

The Route table should identify each segment from point A to point B.
tblroute:IDride, FromIDplace, ToIDplace

Bothe of the IDPlace fields will relate to the waypoints table. Add a validation rule to the table properties that prevents the two place IDs from being the same:
FromIDplace <> ToIDplace

 

[EssieP]

The Route table should identify each segment from point A to point B.
tblroute:IDride, FromIDplace, ToIDplace

Bothe of the IDPlace fields will relate to the waypoints table. Add a validation rule to the table properties that prevents the two place IDs from being the same:
FromIDplace <> ToIDplace

I'm confiused now, Dave UK told me to delete tblWaypoints. I'd be less confused if someone could explain the reason for such pieces of advice.

 

[Pat Hartman]

As I read your post, waypoints are cities or places where the trip is broken. So having this table will let you create combos to help with data entry. You won't have to type the name of each waypoint. You can choose it from a list. So, if I take a train from Stratford to New York City, the segments would be as follows:

Stratford-Bridgeport
Bridgeport-Fairfield
Fairfield-Westport
Westport-Norwalk
Norwalk-Stamford
Stamford-125th Street
125th Street-Grand Central Terminal

You could just list the waypoints as you indicated, but processing will be (I think) more difficult since you will always need to work with two records at a time.

 

[yhgtbfk]

I did that, and it will only allow me to do a one-to-one relationship, but isn't that wrong because one ride can have any number of visited waypoints. In fact some rides will need a placename twice, on the way out and coming back home. I thought it would need a one-to-many?

Is there a way to predict what kind of relationship you are going to get before you go to the relationships window? or is it just chance?

So why not have:

tblRides:IDride (key), datefrom (key), timefrom (key), dateto, timeto, mileage, IDplace (fk)
tblwaypoints: IDplace (key), placename, county

ie:
Ride no1, 10/07/2003, 10:00am, 10/07/2003, 11:00am, 10km, Place1
Ride no1, 10/07/2003, 11:00am, 10/07/2003, 12:00pm, 10km, Place2
Ride no1, 10/07/2003, 12:00pm, 10/07/2003, 1:00pm, 10km, Place1

The first three columns will always be unique as you cant be in two different places at the same time.

For simplicity, you could even make the date and time as one field.

 

[EssieP]

Interesting idea but it's one that will be difficult to gather data. When I get home I can remember which towns & villages I rode through, but not what time of day I arrived at each.

However if I were to list a succession of villages on each ride I could develop the system later by adding in the distances between each place and generating routes for future use. That way if I wanted to do a 30 mile ride I could choose from a variety of waypoints combinations.

Recording journey segments/times is just not practical. Life is just too short to spend time writing such things down.

At heart I really want to be able to build relational databases and use forms with sub-froms in a predicatable way not just build, test delete and rebuild until one just works by chance. Currently I can get one to function properly in about 1 in 8 attempts. The core of my thread here is to get that probability up to certainty.