MS Access - using multiple queries in 1 report

Question

I have about 35 queries in a large MS Access (2007) database that I would like to use in a report. However, I don't want to create reports for each query, but would rather include multiple queries in one report. The other questions relating to this on SO seem to be related to tables, but I am mainly concerned with just queries.

I have looked at several solutions so far, none of which seem to help:

1) DLookup - returns one value. I need to populate a report with many values from each query.

2) Subreports - I have tried to create text boxes to link my query data, but since they are unbound, it won't let me.

3) Yelling at it. I keep yelling at Access and it doesn't seem to help.

4) One giant query to get the values from all the smaller queries - it doesn't recognize the expressions I built in the smaller queries. Example: CustomerCount = DCount("[Customer_ID]","[Customers]"). Error: 'CustomerCount' is unrecognized.

Any ideas would be greatly appreciated.

Thanks,

When you create the giant query does it have the values you created listed? Does it say CustomerCount? Can you post the smaller query with the CustomerCount value? — Taryn
+1 for possible solution #3. When I was using Access, I always thought this should help. — Matt Gibson
I don't understand this question. What type of data is in these queries that you want to combine them into a single report? Please supply some sample SQL. Otherwise, I don't know how anybody could even begin to provide useful suggestions. — David-W-Fenton
@matt, Access can be the perfect tool in the right hands and circumstances. Ugly but agile. — Teson
@user247245 You may have mistaken me for someone who said that Access couldn't be the perfect tool in the right hands and circumstances. — Matt Gibson

Taryn Taryn · Accepted Answer · 2011-06-21T22:21:44

If you are creating a report from one query that is made up of multiple subqueries, you can do this by populating textboxes. If looking at the properties on the report > Data tab > Control Source, you would place the name of the your subquery from your giant query and then the value you that you want.

For example:

Master query

    SELECT 
        [Deb<30].CountOfACCT_ID, [Deb<30].SumOfB001, 
        [Deb<60].CountOfACCT_ID, [Deb<60].SumOfB001, 
        [Deb<90].CountOfACCT_ID, [Deb<90].SumOfB001, 
        [Deb>90].CountOfACCT_ID, [Deb>90].SumOfB001, 
        TodaysHD.CountOfACCT_ID, TodaysHD.SumOfB001, 
        TodaysLD.CountOfACCT_ID, TodaysLD.SumOfB001, 
        Part.CountOfACCT_ID, Part.SumOfB001, 
    FROM [Deb<30], [Deb<60], [Deb<90], [Deb>90], TodaysHD, TodaysLD, Part;

Each of the items in the FROM portion are subqueries being pulled into the master query. Then in your report you would populate the Control Source with [Deb<30].CountOfACCT_ID or whatever value you need from the master query. I have this setup in multiple reports in my application so it should work.

MS Access - using multiple queries in 1 report

2 Answers