Using one query to select criteria in another query

[rjonas]

This seems like an easy thing to do but thus far my query has returned no results even though I know what I WANT it to do should have results....

I have a summary select query that essentially is a listing of vendors. I want to use this query in another query that will pull certain records based on the select criteria from the summary select query:

For example: In the table where I am wanting to select certain records, the transaction description field is where I will be searching for the vendor names contained in the summary select query.

If I were to input as criteria in the transaction description field: Like "*ABC Vendor*", I would get the results relating to ABC Vendor that I am seeking, however because I have a vendor listing of ~250 vendors, I want to use the select query itself to search for the vendor names in the other query, so I put in: Like "*[Vendor Listing]![Name]*" as the query criteria (where "Vendor Listing" is the name of the summary select query and "Name" is the field name in the summary select query) and I got zero records back...

Any thoughts on how I can make this happen?

Any suggestions would be greatly appreciated.

Ron

 

[pbaldy]

Well, perhaps the most intuitive is a subquery (Name is a reserved word, and thus not a good field name):

SELECT Blah FROM TableName WHERE NameField IN(SELECT NameField FROM QueryName)

The more efficient method is a JOIN

SELECT Blah FROM TableName INNER JOIN QueryName ON TableName.NameField = QueryName.NameField

 

[John Big Booty]

You could use a combo box that contains all your Vendors, and then use the following as criteria;

[Forms]![YourFormName]![YourComboName]

 

[rjonas]

Thanks for the replies....

@pbaldy: I think I didn't mention that there wouldn't be an exact match between the vendor listing query and the field in the table (the description field will like have other wording, like "cost accrual for ABC Vendor") to which I'm trying to extract records, which is why I didn't use a join.

@JBB: This is sort of what I was trying to do with the query, which is why I used Like "*[Vendor Listing]![Name]*". I also tried Like "*[Queries]![Vendor Listing]![Name]*" and that didn't work either...

Incidentally, I'm using "Name' as an example... That's not the real field name, it's just shorter and easier to use as an illustration...

Any other thoughts??

 

[John Big Booty]

This sample might give you some clues.