I need to make a monthly stats report for a service agency. There are distinct sets of stats that have to be reported on that call for three separate queries. 1) A count of all the males and females for the given period; 2) a count of all the new clients and their immigration status; 3) a count of all the returning clients and their immigration status. To achieve this, I have an underlying select query to prep the data and create unique clients (Since we want to count people, not the number of times they came in) then I run this through 3 separate crosstabs to provide the 3 different sets of data.
I have a dynamic crosstab working (based on post 134439) beautifully for the separate crosstabs since the number of columns varies in the crosstab. So far so good. I *thought* it would be a simple matter to put these three as subreports on a main report that I would use as a shell to organize the info. Sadly I get the following message:
“You can’t use a pass-through query or a non-fixed column query as a record source for a subform”.
Any suggestions short of printing out three separate reports? At the very least I would like them all to print at the same time, preferably on the same sheet of paper. The client would like it all to appear in a long grid, not three separate ones. I am well and truly stumped with this one.