Sort order for topmost one-to-many relationship.

[MrServiceMan]

I am currently studying Robert Jennings book entitled Using Microsoft Access 2007 Special Edition. In reading about specifying of the sort order and top value limits established by inner joins (pg 455), the author makes this statement. “Access displays query result sets in the order of the index on the primary key field of the table that represents the one side of the top most one-to-many relationship of query tables.” An example of a query with Orders, Order Details, and Products tables displays rows in productID (the primary key field in the Products table) sequence. He says that it does so because the Products table has a one-to-many relationship with the Order Details table and indirectly with the Orders table. I do not understand why the Products one-to-many relationship with the Order Details table is ranked as the top most relationship in that the Orders table also has a one-to-many relationship (OrderID is the primary key field) with the Order Details table and an indirect relationship with the Products table. The Order Details table has two primary keys (ProductID and OrderID). My attempts to change the sequence in which the table relationships were formed as well as the location of the tables in the database had no effect on the result. Your assistance would be greatly appreciated….Thank you.

 

[WayneRyan]

If you do not specify a Sort Order in your query, Access will try to "help" you and will generally behave as you mentioned. I would never depend on that though.

If you do specify something in your ORDER BY clause, Access will enforce it.
If it doesn't appear to be doing what you want its usually because your numeric or date fields are stored as text.

Can you post a sample where the Order By is giving you problems?

Wayne

 

[MrServiceMan]

Thank you very much for your response Wayne. I don’t really have an example of a problem. In reading the book I was just trying to avoid manipulating the sort order before understanding what access was doing.