Sort the combobox displayed value (1 Viewer)

[Notiophilus]

Combobox displayed value

Hello! New user here and quite new to databases, too, so forgive me if this is an easy question

I have a combobox which lets you select from a list of pseudonyms. Each pseud belongs to an author; authors can have multiple pseudonyms and exactly one main pseudonym. This is the table structure (picture of relationships attached below):

tblStory: StoryID (PK), AuthorID (FK), Title, etc. 
tblAuthor: AuthorID (composite PK, autonumber), MainPseudID (composite PK) 
tblPseud: PseudID (PK, autonumber), Pseud, AuthorID (FK)

The combobox list shows all the pseudonyms and then stores the corresponding AuthorID. At the moment, the pseudonym displayed in the combobox (once a selection has been made) is simply the first alphabetically for that particular author: for instance, if a story has AuthorID = 5, and Author 5 goes by anon, anonymous, and unknown, the combobox for that story will display "anon".

How can I make the main pseudonym (tblAuthor.MainPseudID = tblPseud.PseudID) be displayed instead?

Control source is tblStory.AuthorID
Row source:

SELECT tblPseud.AuthorID, tblPseud.Pseud, tblAuthor.MainPseudID 
FROM (tblAuthor LEFT JOIN tblPseud ON tblAuthor.AuthorID = tblPseud.AuthorID) 
       LEFT JOIN tblStory ON tblAuthor.AuthorID = tblStory.AuthorID 
ORDER BY tblPseud.Pseud;

Thanks!

 

[CJ_London]

you would need to use a union query - the first part gets the MainPseudID and the second part gets the rest as before and orders it but excludes what was returned in the first part

 SELECT tblPseud.AuthorID, tblPseud.Pseud, tblAuthor.MainPseudID 
FROM (tblAuthor LEFT JOIN tblPseud ON tblAuthor.MainPseudID 
= tblPseud.PseudID) 
       LEFT JOIN tblStory ON tblAuthor.AuthorID = tblStory.AuthorID 
 UNION SELECT tblPseud.AuthorID, tblPseud.Pseud, tblAuthor.MainPseudID 
FROM (tblAuthor LEFT JOIN tblPseud ON tblAuthor.AuthorID = tblPseud.AuthorID) 
       LEFT JOIN tblStory ON tblAuthor.AuthorID = tblStory.AuthorID 
WHERE tblPseud.PseudID<>tblAuthor.MainPseudID 
 ORDER BY tblPseud.Pseud;