[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access query criteria linked to existing query field value

Posted on 2011-05-04
10
Medium Priority
?
237 Views
Last Modified: 2012-06-20
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'.

Thanks!
0
Comment
Question by:Golfer219
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35688154
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?

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35689869
Will the first query only return 1 value?
0
 

Author Comment

by:Golfer219
ID: 35690286
Correct Boag2000 - the query is setup to only pull in one value.

Can the Criteria be setup something like
     =[Customer Subset].Customer?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 35690550
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35690907
...yes, can you take a step back and first clearly explain (with a specific example) the need for doing this?
0
 

Author Comment

by:Golfer219
ID: 35691830
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35693598
\_O.o_/

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

Post a sample only if he agrees that it would help.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35693727
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.

0
 

Accepted Solution

by:
Golfer219 earned 0 total points
ID: 35722191
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.

Thanks
0
 

Author Closing Comment

by:Golfer219
ID: 38103373
Self Found Solution
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question