Sorting Problems with linked tables

[simonsimon]

Hi,

I am having trouble doing a sort in a query (and report too)

I have a field called SP ITEM which looks up data in another table to get more information related to the SP ITEM.

Whenever I sort this SP ITEM it doesn't actually sort by the SP ITEM it sorts by the ID field from the other table. How do i make it sort by the SPITEM not the ID?

I also have the exact same problem with the sorting and grouping in the reports.

I've attached a screenshot (zipped in a .doc) of the query to hopefully make what i'm saying clearer...

Thanks!
simon

 

[dkinley]

It's because you have it linked to the ID in SP Master List. By this observation, I would assume that you are storing the SP ITEM ID in the table called PROJECT SP LIST. If so that is why.

Look at your column in the query called SP ITEM. It is sorting SP ITEM on table PROJECT SP LIST; hence, sorting by the ID stored there not the name in the other table. You need to change that table name to SP Master List (click the down arrow right there on that row and it will do it when you click it.

As a point of clarification, in these situations for the primary table, I use something like pkSPItemID and if I store that in another table I will use fkSPItemID. This helps me keep it straight at glance about what I am storing (a foreign key) and where it came from (primary key of table of same name). To continue with the example for illustration, I would name my table tblSPItem with a primary key of pkSPItemID. Then in tblOtherStuff it would have a primary key of pkOtherStuffID and another field called fkSPItemID.

Hope that helps ...

-dK