Query help

[VB Joshua]

Hello,

I am trying to query between two tables ans can not produce the result i
want. I have a contacts table and a orders table. The order table requires a
Customer name(which comes from the customer table) and then a contact(from
the contacts table) for that customer. This is my sql that keeps asking me
for a parameter.

Select Contacts.Name
From Contacts
Where Orders.[Customer Name] = Contacts.[CompanyName]


I am trying to make my Contact field i the orders table a combo.... But i
have not been able to do this. If someone needs i can send the DB I am very
desperate.

 

[Ken Snell [MVP]]

You're asking the query to select records where the company name value in
the Contacts table is equal to a parameter
Orders.[Customer Name]

I think you want Orders.[Customer Name] to be the customer name from the
Orders table, but you have not included the Orders table in the list of
tables that the query is using for source data.

It also sounds as if you're using a LookupField in the table's design....we
do not recommend that you do this, as it can and usually does lead to
aggravations and confusions later on (because what you see is not what is
stored in the table).

You cannot use the value of a field in the table to be the "filter" for the
SQL of a LookupField for another field in that same table. The SQL must be
self-sufficient and has no idea that you're using it in a LookupField in a
table.

Also, do not use tables for data entry. Use forms instead. Not only do you
better protect your data, but you then can do more of what you seek to do
with how to filter a combo box's list of items based on a value that is on
the form.