Query criteria based on a seperate table

[NeilT123]

Is it possible to have a query based on a single table and then set the criteria to equal the value in another table without introducing the table into the query.

The table the query is based on is called tblFieldDtls and has a field [Farm Account Number]

At the moment I have the criteria for [Farm Account Number]set to
=[tblSelect]![Farm Account Number]
The [tblSelect] is another table in the database.

When I run the query an enter parameter box appears and if I manually enter the Farm account number the query runs and returns the correct result.
I have tried putting various versions of

into my = statement but without success.

Is it possible to point the criteria to another table to run automatically? If so what is the correct method?

Thank you in advance for any help

 

[PHV]

Join tblFieldDtls and tblSelect on [Farm Account Number]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeilT123]

Hi PHV

Where do you mean to join the tables? There is only one table in the query, I am trying to avoid introducing the tblSelect into the query.

I have joined the tables in the relationship window but that does not help. Should I be joining the tables elsewhere?

 

[PHV]

I am trying to avoid introducing the tblSelect into the query
How do you expect to test a table value without reading the table ?

 

[NeilT123]

I am trying to read the table value through the criteria option in the query. I don't know whether it can be done hence why my original question was:

"Is it possible to have a query based on a single table and then set the criteria to equal the value in another table without introducing the table into the query."

 

[lespaul]

no, you have to join into the other table. Why are you trying to avoid doing this? It's the way it's suppose to work.

Understanding SQL Joins



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[NeilT123]

Why are you trying to avoid doing this? It's the way it's suppose to work

I have an input form based on a query. That query is based on a table called tblFieldDtls.
If I add the table tblSelect into the query the input form does not allow me to add new records so I was looking for a way to run the query based on tblFieldDtls but using one of the fields in tblSelect as a criteria.

All I really want to be able to do is set the criteria for the query to = [Farm Account Number] which is in my tblSelect table.

P.S Thank you for the links

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886