Report based on multiple queries

[jereece]

I have 6 queries that performs some count functions. So the results of each query looks something like this.

Group Count
EHS 3
RGC 6
SAR 9
BRT 1
UTC 5
NAM 9

I need one report that shows the count results from all 6 queries. How can I do this?

Thanks,
Jim

 

[ted.martin]

Use Dlookup function for each field and point it to the appropriate query.

 

[jereece]

Use Dlookup function for each field and point it to the appropriate query.

When I add a text box to the report and add Dlookup, it puts the following in the critera "=DLookUp([«expr»],[«domain»],[«criteria»])". This does not look like what I need. Can you give me more details on how to do this?

Thanks,
Jim

 

[rainman89]

Here is a sample on how to use a dlookup
http://www.mvps.org/access/general/gen0018.htm

ITs helped me out of a jam many times

 

[Dennisk]

create one query and add each sub query to it so long as each sub query returns only one row. Then add each column to the grid and use it as the record source for the report.

Another way is to run the query against a recordset in code in a function and return the value.
then in the textbox where you require the value set the control sourse to the function
ie =Functionname()

 

[jereece]

create one query and add each sub query to it so long as each sub query returns only one row. Then add each column to the grid and use it as the record source for the report.

Another way is to run the query against a recordset in code in a function and return the value.
then in the textbox where you require the value set the control sourse to the function
ie =Functionname()

I tried your first suggestion, but I have more than one row. I can have up to 5 rows, but when I tried this it created many duplicates and I wound up with 360 rows. I don't understand how to do your second suggestion.

Thanks,
jim

 

[jereece]

Here is a sample on how to use a dlookup
http://www.mvps.org/access/general/gen0018.htm

ITs helped me out of a jam many times

This does not seem to work. First of all I am using a count query, not a table. So the count query creates it's own name, not the field name of the table it references. However, I tried it anyway and it returned "#Name?"

So I don't think this will work. I origainally had this pulling into Excel, but it stopped updating so maybe I need to go back and troubleshoot why Excel stopped working.

Thanks for the help.

Jim

 

[rainman89]

How about a report with subreports? you can have a report based on each query. and then just add them all to a single report

 

[tim419762]

You may want to try a Union query. Go into the SQL editor of a new query and type
Select * FROM query_name1 UNION ALL Select * FROM query_name2;

If the queries are congruent (the same number of columns), this should 'stack' the queries information on top of each other. You should be able to stack multiple queries on top of each other with the UNION ALL statement in between them.

 

[pbaldy]

Do the queries reference different tables? If not, I'd suspect you can get them all in one query.