Access 2013 Multiple Queries need to summarize

[steffwood]

Hello,



I think you will recommend a UNION query on this, but let me give it a shot. Is there an easier way? What's the best way.

I have a main extract file, that is linked, so it will refresh with new data on a daily basis. I am creating a daily report.
I have three queries, File Complete, Files Received, PROD Files Received.
Fields are Customer, Task, Task Completed Date.

I want to summarize in a report in this output and have it pull X (count of tasks for each day)

Mon Tue Wed Thur Fri
Files Received X X X X X
Files Completed X X X X X
PROD Files Received X X X X X

What do you think? Do I still use a SQL Union statement? I want a nice summarized report and I've already put a condition in the queries that will pull tasks completed for the current week.
Eg: DatePart("ww", [End Date]) = DatePart("ww", Date()) and Year( [End Date]) = Year(Date())

Thanks for the help.

[June7]

Do all 3 queries draw from the same data source - main extract? If so, why do you have 3 queries?

[steffwood]

they all do come from the same extract. I have specific conditions for each, tried creating a one main crosstab query but it would give me an error stating too many crosstab column headers.
So I just made a separate query for each category, files received, completed and PROF files received.

[June7]

By 'specific conditions' you mean filter criteria?

These are CROSSTAB queries?

Building stable report based on CROSSTAB is not simple because of dynamic nature of field headers.

Might find this of interest http://allenbrowne.com/ser-67.html

[steffwood]

Thank you for the information.
I've managed to create a crosstab report and insert subreports within the main report.

:-) Looking great!

Now if I can only figure out how to export the main report with all subreports I would be happy!