Hi, its me again:

I'm using SQL 7.

Forget about all the SPT stuff and just imagine two tables engaged in a relation. The parent uses an integer primary key and the child uses an integer foreign key that relates back to the parent table. Now, if you want to relate the tables, you must have an index on the foreign key for it to work. However, what if you want your child records physically sorted on another field, say last name which is a char field. How would you do this and maintain the relation?

The reason I'm asking is because I have a form with a relation. You can skip through child records with navigation buttons, but the child records are indexed on, of course, the foreign key so as you move through the records the last names are not in sequence. I would like to maintain the relation, but have the last names in sequence.

I've been experiementing with indexes, but no luck so far. If I were using a char keys, it would be just a matter of concatenating the foreign key with last name, but since they are different data types, it can't find a way to make it work.

Thanx,
Charlie