Unanswered: 3 crosstabs reported in one report with UNION?
Anybody had experience bring together 2 or more crosstab queries using the UNION?
ALL 3 crosstabs come from 3 sourcing queries from the same table so of course normally runnning 3 crosstabs at the sametime throws an error
I have to pull 3 crosstabs together. All are laid out as
The first is those number of people starting a training programme
Period number. (This is set 1 to 12)
Then we have the above format but for those leaving and the third is for those number acheiving the training programme.
I will be able to set up a simple text box situation in a later report to show the % of leavers acheiving the training.
Have you tried to do this yet? If so, what error did Access give you? Could you show us the SQL you used, as that would be a big help in helping you.
If you have tried it and feel there is no way it is going to work, what I always do in that type of a situation is to create a work table, then just clear it out at the start of my procedure (DELETE * FROM wrkMyTable; ), then append the crosstab output into the wrktable. My report would be built on the work table and run after all three cross tab queries were appended to the work table.
As yet I haven't tried to do a union query with the 3 crosstabs. I,m not at all confident with SQL as yet so the question was more to see if anyone had undertaken something like this before, that way I would at least know if it were possible or not, if it is I could learn and/or get support to build something to do the job.
I have not tried that with crosstabs, but my gut says it won't work. The reason for that is that Crosstabs are VERY picky. They don't like being messed with. But, that would NOT stop me from trying. You can't hurt anything by trying. The best way to do this would be to get the SQL from each crosstab query, one at a time. Paste the first crosstab SQL into a blank SQL view of a new query, then on a new line, enter UNION. Go to the next line, and paste the 2nd crosstab SQL, new line, enter UNION, new line paste 3rd crosstab SQL, the see if it works.
If not, I would not mess with it, but go directly to the work table. When I'm going to use a work table, I first build it with a build table query from the query that I will be using as the append query. Here are the steps I normally take.
1. Build a query using the first crosstab query as the only recordset in the top half of the query design view.
__ a. Double click the title bar of the crosstab query recordset in the top half of the query grid. This will select all the fields
__ b. Drag all the fields into the grid (lower) protion of the query design view.
__ c. Using the menus, Query/Make Table Query..., then name the worktable
__ d. Run this query, using the red exclaimation point (!), or menus Query/Run.
__ e. Change this query type from Make Table into Append query.
__ f. Save the query and give it a meaningful name
2. Make a new query based on the 2nd crosstab query. Make this an Append query with the new work table as the table to receive the output.
3. Do the same as 2 except with the 3rd crosstab query.
You can now use the work table as the RecordSource for your report.
Besure to delete all the records from this table before adding any new ones each time you run this report.