Show Query names in 1 query (1 Viewer)

[cookiegary01]

Hi，

I'm looking for the smartest way to solve the following:
Certain data is imported to Access weekly from the company main system.
The Access database contains about 250 queries that checks the correctness of imported data, via criteria in each query.
Each query that checks data, is called Check_*. So Check_0001 through Check_0250.
Each query does a different check, and contains different data.
Each query result does at least contain the "Material Number" as unique identifier.

What I need is: A query that gathers all "Material numbers" and "Query name" from all the Check_* queries that contain data.

I need this to quickly check data before we release a new Material number in the main system.
Downloading the queries results to many Excel files in a specific folder is considered an option, but 1 extra query in Access (as mentioned above) has our preference.

Thanks in advance.

 

[pbaldy]

If you want a single query, you're looking at a UNION query:

SELECT FieldName, "1" As SourceQuery
FROM Check_0001
UNION ALL
SELECT FieldName, "2" As SourceQuery
FROM Check_0002
UNION ALL
...

I might lean towards a code based solution with a For/Next loop that opened a recordset on each query and put the results somewhere (temp table, Excel file, whatever).

 

[cookiegary01]

pbaldy,
I much appreciate your quick reply.
I'm missing "Query name" in the results, and also... only queries that have results need to be included.
Perhaps you could fit that in for me?

 

[pbaldy]

This was to get the query's number:

"1" As SourceQuery

You could use the whole name if you want:

"Check_0001" As SourceQuery

Queries with no results would simply have no records in the result.

 

[cookiegary01]

I got it.
Thanks, I will try that out for sure.
Last question: Why does a temp. table have your preference?

 

[pbaldy]

I said I might lean that way. I suppose it depends on what you wanted to do with the results. With code I could put up a message box or populate an email body with the results. You could of course still do that with the UNION query. Sometimes I just like to write code.

 

[cookiegary01]

Dear code builder pbaldy,
What I do with the results is:
I have a Material number that needs to be released. I want to quickly find if that number appears in any of those queries.
If it does appear, then I need to do go the specific queries. I check the query to see which field, in which area of the main system was incorrectly set up. Then I go to the main system and correct the discrepancy before releasing the material. In a nut shell, that is what I need to do. Search by material number in the UNION query results.

 

[pbaldy]

If the UNION query does what you need, I'd stick with it. As much as anything I was just throwing out options.