Union Query order By clause Error

[saraqpost]

I have the following Union Query and when I try to run it, I get "The
OrderBy Expression ContactKey =1 includes fields that are not selected
by the Query."

I'm confused as ContactKey is in the first select and the Union:

SELECT tblContacts.ContactKey, tblContacts.ClientKey, [ContactLastName]
& ", " & [ContactFirstName] AS Name, tblContacts.City,
tblContacts.MainContact FROM tlkpRelationship RIGHT JOIN (tblContacts
LEFT JOIN tlkpRelationshipGroup ON tblContacts.RelationshipGroupKey =
tlkpRelationshipGroup.RelatioshipGroupKey) ON
tlkpRelationship.RelationshipKey = tblContacts.RelationshipKey WHERE
(((tblContacts.ClientKey)=[Forms]![frmNewClient]![txtClientKey]) AND
((tblContacts.ContactRecordStatus)="A")) UNION SELECT 1 as ContactKey,
806 as ClientKey, " Self " as ContactLastName, " " as City, " " as
MainContact from tblContacts
ORDER BY ContactKey = 1, Name;


What I am trying to do is load a combo box with the Contacts (from
tblContacts with the clientKey = clientkey on the form) and put "Self"
as the first choice. The user is being asked who is responsible for
the medical billing for the elderly client. I do this same type of
thing (Union Query) with "Add New" when choosing a client who is on the
phone.

Other suggestions are welcome. I had thought of having the user choose
"Self" or "Other" and then show the Contacts if they choose "Other",
but this way seemed more direct.

Thanks very much.

Sara

 

[Duane Hookom]

Try set the Order By to
ORDER BY 1;

 

[saraqpost]

AMAZING!! So simple.

Thanks
Happy New Year.

Sara

Try set the Order By to
ORDER BY 1;
--
Duane Hookom
Microsoft Access MVP

I have the following Union Query and when I try to run it, I get "The
OrderBy Expression ContactKey =1 includes fields that are not selected
by the Query."

I'm confused as ContactKey is in the first select and the Union:

SELECT tblContacts.ContactKey, tblContacts.ClientKey, [ContactLastName]
& ", " & [ContactFirstName] AS Name, tblContacts.City,
tblContacts.MainContact FROM tlkpRelationship RIGHT JOIN (tblContacts
LEFT JOIN tlkpRelationshipGroup ON tblContacts.RelationshipGroupKey =
tlkpRelationshipGroup.RelatioshipGroupKey) ON
tlkpRelationship.RelationshipKey = tblContacts.RelationshipKey WHERE
(((tblContacts.ClientKey)=[Forms]![frmNewClient]![txtClientKey]) AND
((tblContacts.ContactRecordStatus)="A")) UNION SELECT 1 as ContactKey,
806 as ClientKey, " Self " as ContactLastName, " " as City, " " as
MainContact from tblContacts
ORDER BY ContactKey = 1, Name;


What I am trying to do is load a combo box with the Contacts (from
tblContacts with the clientKey = clientkey on the form) and put "Self"
as the first choice. The user is being asked who is responsible for
the medical billing for the elderly client. I do this same type of
thing (Union Query) with "Add New" when choosing a client who is on the
phone.

Other suggestions are welcome. I had thought of having the user choose
"Self" or "Other" and then show the Contacts if they choose "Other",
but this way seemed more direct.

Thanks very much.

Sara

 

[saraqpost]

AMAZING!! So simple.

Thanks
Happy New Year.

Sara

Try set the Order By to
ORDER BY 1;
--
Duane Hookom
Microsoft Access MVP

I have the following Union Query and when I try to run it, I get "The
OrderBy Expression ContactKey =1 includes fields that are not selected
by the Query."

I'm confused as ContactKey is in the first select and the Union:

SELECT tblContacts.ContactKey, tblContacts.ClientKey, [ContactLastName]
& ", " & [ContactFirstName] AS Name, tblContacts.City,
tblContacts.MainContact FROM tlkpRelationship RIGHT JOIN (tblContacts
LEFT JOIN tlkpRelationshipGroup ON tblContacts.RelationshipGroupKey =
tlkpRelationshipGroup.RelatioshipGroupKey) ON
tlkpRelationship.RelationshipKey = tblContacts.RelationshipKey WHERE
(((tblContacts.ClientKey)=[Forms]![frmNewClient]![txtClientKey]) AND
((tblContacts.ContactRecordStatus)="A")) UNION SELECT 1 as ContactKey,
806 as ClientKey, " Self " as ContactLastName, " " as City, " " as
MainContact from tblContacts
ORDER BY ContactKey = 1, Name;


What I am trying to do is load a combo box with the Contacts (from
tblContacts with the clientKey = clientkey on the form) and put "Self"
as the first choice. The user is being asked who is responsible for
the medical billing for the elderly client. I do this same type of
thing (Union Query) with "Add New" when choosing a client who is on the
phone.

Other suggestions are welcome. I had thought of having the user choose
"Self" or "Other" and then show the Contacts if they choose "Other",
but this way seemed more direct.

Thanks very much.

Sara