lookup field not working with Union

[sysadmin42]

Everything works fine in the below statement, except that the [~Clients].State field, because it's a lookup, only shows the ID number of the state, rather than its value in the States table. Any thoughts?

SELECT [~Clients].[First Name], [~Clients].[Last Name], [~Clients].Address1, [~Clients].Address2, [~Clients].City, [~Clients].State,[~Clients].Zip
FROM [~Clients]
INNER JOIN states
ON [~Clients].State=[states].ID
WHERE (([~Clients].MailingList)=Yes AND ([~Clients].Address1)<>NULL)

UNION  SELECT [Contacts-Other].[First Name], [Contacts-Other].[Last Name], [Contacts-Other].[Address1], [Contacts-Other].[Address2], [Contacts-Other].[City], [Contacts-Other].[State], [Contacts-Other].[Zip]
FROM  [Contacts-Other]
INNER JOIN states
ON [Contacts-Other].State=[states].id
WHERE (([Contacts-Other].MailingList)=Yes AND ([Contacts-Other].Address1)<>NULL)

UNION SELECT [~Clients].[Other First Name],[~Clients].[Other Last Name],[~Clients].Address1, [~Clients].Address2, [~Clients].City, [~Clients].State, [~Clients].Zip
FROM [~Clients]
INNER JOIN states
ON [~Clients].State=[states].id
WHERE (([~Clients].MailingListOtherContact)=Yes AND ([~Clients].[Other First Name])<>NULL);

 

[RoyVidar]

Erm - I think a read at The Evils of Lookup Fields in Tables will explain a little about what is happening (and why most of the members here discourages usage of table level lookups) ;-)

The quick and dirty workaround, is including the states table to the query, and include the the field containing the state name (which is also what you need to do in a proper approach).

But you should consider removing the table level lookups, and only create lookups (combos) in forms.

Roy-Vidar

 

[RoyVidar]

Should perhaps read the SQL before answering - since you've joined the states table alredy, it should just be a matter of substituting the [~Clients].State with States.<state name field>, same goes for the other tables, I suppose.

Roy-Vidar

 

[sysadmin42]

you were right

i took out [~Clients].State and replaced it with [States].State, kept the INNER JOIN as is, and perfecto.

and I was thinking about getting rid of all lookups, and you just confirmed it. they are evil.

thank you.