[SOLVED] WHERE statement in lookup coloumn

[frksdf]

I have an İssue about using SQL statments with a table in which there is look-up column.

 
SELECT Supp_Name,Supp_Company FROM Supplier WHERE Supp_Name="ATK"

This code working very well. It retrieves Supplier names and Supplier Company names that supplier names are ATK.
But;

 
SELECT Supp_Name,Supp_Company FROM Supplier WHERE Supp_Company="ATK"

If I run this code; I got the error message that states "Data type mismatch in criteria expression".

By the way; Supp_Company field is a look-up coloumn that retrieves the data from Comp_name field in Company table.



Is it impossibe to use lookup-coloums in WHERE statement ?
(Access 2007 sql query - not SQL server)
Thanks for your help and interest ?

[jzwp11]

What you describe is a typical problem associated with having lookups at the table level. Even though Access has this capability, it is generally recommended to not have lookups in your tables. This site explains the issues in more detail.

The best approach is to spin off the lookup items (whatever they are) into their own table and just reference the key field in the primary table. For example, let's say you have a table of addresses with a lookup field for state names. You would separate the state names into their own table...

tblStates
-pkStateID primary key, autonumber
-txtStateName

Then in your address table just reference the pkStateID field as a foreign key

tblAddresses
-pkAddID primary key, autonumber
-txtStreetAddress
-txtCity
-fkStateID foreign key to tblStates

In a query you would bring in both tables and join them via the pkStateID-fkStateID. You can then select either of the ID fields or the name of the state depending on what you need.

[frksdf]

Thank you very much jzwp11;

Your answer has lots of valuable information.

Regards,,,

[jzwp11]

You're welcome.