Custom Right Click Menu (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 14:58
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:58
Joined
Jul 12, 2019
Messages
225
If I understand you correctly, change the form's recordsource to
Code:
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

MVP / VIP
Local time
Today, 21:58
Joined
Jan 14, 2017
Messages
18,186
Or you can set Unique Records = Yes to make it editable.
The SQL becomes
Code:
SELECT [B][COLOR="DarkRed"]DISTINCTROW[/COLOR][/B] Contacts.*
FROM Contacts INNER JOIN Titles ON Contacts.ContactTitle = Titles.Title
ORDER BY Titles.TitleID;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:58
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom