I am having trouble combining reports together. First off, I just started to learn how to use access, but there are time-constraints, so please bear with me and I'll try my best to explain my problem.
I have created 6 different reports that need to be combined into 1 single report. Every report is generated from a query and a crosstab. The crosstab is common in all 6 reports, just the query is different. The queries in the 6 reports are all left-join queries.
I chose one of the reports to be the main, and tried to insert the other 5 reports into the main report. Everything looked good in the design view, but when I tried to view it, I get a error message saying "You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property." Then I pressed ok, then I see my main report content there and 5 empty boxes where my other five reports contents should of been.
I know building queries upon queries upon queries is not good, but I don't see any way around it. (5minutes lag - every single click).
Could someone please help me out? Thanks in advance.
I'm assuming that the crosstab query is subordinate to the query, where the crosstab is run and then the query selects a subset of the results. If this is the case, I think the solution is simple. Run the crosstab query to create a new table before running any reports. Each report should be bound to a query that calls the TABLE, not the crosstab, and get its results from there.
If the opposite is true, that you need to run the query first and have the crosstab do some processing on the results, you have no recourse but to have the crosstab create new tables, and the reports should be bound directly to the tables. Of course this takes time, but it doesn't have to be done on the user's time necessarily. No one says that you have to take time to run the queries at report time. Have the queries run during lunch, break, over night, etc. and run the reports later.
In either scenario, even if additional processing is done after the new table is created, you will still get a report - a snapshot, really - that was valid as of a certain time.
Oh, yes, you have to delete either the data or the table each time you create or append to it.