Custom Right Click Menu

[theDBguy]

This I know, If I enter [ContactTitle] in the OrderBy property, it sorts them alphabetically. Would I would like it to sort by is the TitleID of the associated title so instead of sorting the table as

Capt
Capt
Dep
Lt
Lt
Sgt

it would sort it based on their ID from the titles table and would look like

Capt
Capt
Lt
Lt
Sgt
Dep

Well, you know what? If Titles is a lookup table, then the ContactTitle field in the Contacts table should have been a Foreign Key to the Titles table. If so, you would have been storing the ID field in it, and therefore be able to sort by it.

 

[NearImpossible]

If I understand you correctly, change the form's recordsource to

SELECT Contacts.*
FROM Contacts INNER JOIN Titles ON Contacts.ContactTitle = Titles.Title
ORDER BY Titles.TitleID;

if I do this, it orders correctly, however I'm not able to add any new records as its a query result.


I did get it to work how I wanted, I just added a hidden TitleID field to the table and set that for the OrderBy.

 

[isladogs]

Or you can set Unique Records = Yes to make it editable.
The SQL becomes

SELECT [B][COLOR="DarkRed"]DISTINCTROW[/COLOR][/B] Contacts.*
FROM Contacts INNER JOIN Titles ON Contacts.ContactTitle = Titles.Title
ORDER BY Titles.TitleID;

 

[theDBguy]

I did get it to work how I wanted, I just added a hidden TitleID field to the table and set that for the OrderBy.

Hi. I think you basically just added the Foreign Key I was referring to earlier.