Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: 3 crosstabs reported in one report with UNION?

    Hi all

    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

    Row
    Training Programme

    Heading
    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.

    any ideas??
    Gareth

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.
    HTH,

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Thanks for coming back so quickly.

    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.

    Gareth

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.

    Please let us know how this works for you.

  5. #5
    Join Date
    Sep 2006
    Posts
    265
    The problem with crosstabs is that only one field is extrapolated. I would try:

    Period1blah: iif(Period]=1,Field,0) etc

    Then do the same for the other fields you what on the report.

    Simon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •