Access query criteria linked to existing query field value

While setting up a query in Access, I realized that I'd like to setup dynamic criteria so as the results from another query change, they will drive this criteria.  Is there a way in the 'Criteria' field to link to an existing field within another query?

The existing field I want to link to is 'Customer' in a query called 'CustomerSubset'.

Who is Participating?
Golfer219Connect With a Mentor Author Commented:
Not sure if any of the answers were exactly what I was looking for - I wound up being able to accomplish what I was looking for via 5 separate queries.  Thanks for everyone's help.

Without knowing the exact query you are trying to build I'm not sure if the following is appropriate but it's worth a look.

How about including the query CustomerSubset in the new query and joing to the Customer field?

Jeffrey CoachmanMIS LiasonCommented:
Will the first query only return 1 value?
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Golfer219Author Commented:
Correct Boag2000 - the query is setup to only pull in one value.

Can the Criteria be setup something like
     =[Customer Subset].Customer?
Dale FyeCommented:
Well, you could:

Add the second query to the query grid, and join it to the table(s) that are already there on the [Customer] field.

Or you could write a criteria that looks something like:

WHERE [Customer] IN (SELECT [Customer] FROM qryCustomerSubset)

A lot depends on what you want to do with the result.  Do you want it to be updateable?
Jeffrey CoachmanMIS LiasonCommented:
...yes, can you take a step back and first clearly explain (with a specific example) the need for doing this?
Golfer219Author Commented:
Thanks Boag2000 =

Here's a little background....

I have an automated template in Excel that partly depends on a large data extract.  Instead of including all of this data all the time in Excel (it kills the calculation time of array formulas) I developed VB code to push 5 variables to an Access DB and create a new record in a given table.  After pushing the variables, the excel template then downloads the new query results into Excel.

My customer subset query (5 variables) is setup to show only the most recent record that has been pushed (i.e. if a user has pushed variables 52 times, it will only pull back the 52nd set of variables.)

The trick here is that all 5 variable are conditions for the same field in another table (let's call that table KPI and the field within the KPI table we need is called Customers).  Criteria for 'Customers' should be equal to the 5 variables from the customer subset query.

Hope this helps.  I can try to strip down the Access DB to remove confidential information and post to here if that's a last resort.

Thanks for everyone's help!
Jeffrey CoachmanMIS LiasonCommented:

Ummm, ...OK, first try fyed's suggestion....

Post a sample only if he agrees that it would help.
Dale FyeCommented:
OK, so as I understand it, you are pusing 5 values to a table in Access which you then want to use to in some way filter the records in another table, which you access via an Excel query.  Is that accurate?

What are the 5 variables?  You indicate that they are all "conditions for the same field in another table", do you mean that they are all values that that can be found in the [Customers] field?

How do you identify the "most recent record" in this Access table where you are pushing the variables?

Yeah, I think a stripped down version of the Access database and Excel file would be helpful.

Golfer219Author Commented:
Self Found Solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.