Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: Combining TRANSFORM and UNION

    I'm trying to do a crosstab report with a totals row at the bottom. The way I am trying to do this is by making a summary crosstab query and UNIONing the two of them, but I get syntax errors when I try to UNION crosstab queries. Here is what I have so far:

    TRANSFORM Sum(([QTY Ordered]-[QTY SHipped 1]-[QTY Shipped 2]-[QTY Shipped 3])*[unit price]) AS weekly_total
    SELECT DateAdd("d", DatePart("w",[Due Date]),[Due Date]) AS Week_start
    FROM Jobs
    WHERE (((Jobs.[Qty Ordered])>[QTY Shipped 1]+[QTY Shipped 2]+[QTY Shipped 3])) AND [Due Date] IS NOT NULL
    GROUP BY DateAdd("d", DatePart("w",[Due Date]),[Due Date])
    PIVOT Jobs.Customer
    UNION
    TRANSFORM Sum(([QTY Ordered]-[QTY SHipped 1]-[QTY Shipped 2]-[QTY Shipped 3])*[unit price]) AS weekly_total
    SELECT "Total" AS Week_start
    FROM Jobs
    WHERE (((Jobs.[Qty Ordered])>[QTY Shipped 1]+[QTY Shipped 2]+[QTY Shipped 3])) AND [Due Date] IS NOT NULL
    GROUP BY 1
    PIVOT Jobs.Customer;

    THe syntax errors says 'Missing operator in the expression:" and quotes from the first PIVOT statement down to the bottom. Both of the crosstab queries work as single queries.

  2. #2
    Join Date
    Sep 2003
    Posts
    7
    bump

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Combining TRANSFORM and UNION

    Originally posted by laces_out
    I'm trying to do a crosstab report with a totals row at the bottom. The way I am trying to do this is by making a summary crosstab query and UNIONing the two of them, but I get syntax errors when I try to UNION crosstab queries. Here is what I have so far:

    TRANSFORM Sum(([QTY Ordered]-[QTY SHipped 1]-[QTY Shipped 2]-[QTY Shipped 3])*[unit price]) AS weekly_total
    SELECT DateAdd("d", DatePart("w",[Due Date]),[Due Date]) AS Week_start
    FROM Jobs
    WHERE (((Jobs.[Qty Ordered])>[QTY Shipped 1]+[QTY Shipped 2]+[QTY Shipped 3])) AND [Due Date] IS NOT NULL
    GROUP BY DateAdd("d", DatePart("w",[Due Date]),[Due Date])
    PIVOT Jobs.Customer
    UNION
    TRANSFORM Sum(([QTY Ordered]-[QTY SHipped 1]-[QTY Shipped 2]-[QTY Shipped 3])*[unit price]) AS weekly_total
    SELECT "Total" AS Week_start
    FROM Jobs
    WHERE (((Jobs.[Qty Ordered])>[QTY Shipped 1]+[QTY Shipped 2]+[QTY Shipped 3])) AND [Due Date] IS NOT NULL
    GROUP BY 1
    PIVOT Jobs.Customer;

    THe syntax errors says 'Missing operator in the expression:" and quotes from the first PIVOT statement down to the bottom. Both of the crosstab queries work as single queries.
    Have you tried Unioning them together and then performing the crosstab?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  4. #4
    Join Date
    Sep 2003
    Posts
    7

    Re: Combining TRANSFORM and UNION

    Originally posted by basicmek
    Have you tried Unioning them together and then performing the crosstab?
    Well, I've tried this, and a few variations, with no luck. If I put parentheses around the two unioned selects it gives me an error as well.

    TRANSFORM Sum(([QTY Ordered]-[QTY SHipped 1]-[QTY Shipped 2]-[QTY Shipped 3])*[unit price]) AS weekly_total
    SELECT DateAdd("d", DatePart("w",[Due Date]),[Due Date]) AS Week_start
    FROM Jobs
    WHERE (((Jobs.[Qty Ordered])>[QTY Shipped 1]+[QTY Shipped 2]+[QTY Shipped 3])) AND [Due Date] IS NOT NULL
    GROUP BY DateAdd("d", DatePart("w",[Due Date]),[Due Date])
    UNION
    SELECT "Total" AS Week_start
    FROM Jobs
    WHERE (((Jobs.[Qty Ordered])>[QTY Shipped 1]+[QTY Shipped 2]+[QTY Shipped 3])) AND [Due Date] IS NOT NULL
    GROUP BY 1
    PIVOT Jobs.Customer;

  5. #5
    Join Date
    Sep 2003
    Posts
    7
    P.S. The two select statements UNIONed together as above works, but adding the TRANSFORM and PIVOT breaks it.

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by laces_out
    P.S. The two select statements UNIONed together as above works, but adding the TRANSFORM and PIVOT breaks it.
    This may sound like a basic question, but have you tried to create a single union query to combine the data and then create a separate crosstab in the QBE window using the union query as the source?

    If so and the problem still persists, could you post a sample database with some sample data in it? I'd like to play around with it a bit if you wouldn't mind.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  7. #7
    Join Date
    Sep 2003
    Posts
    7
    Originally posted by basicmek
    This may sound like a basic question, but have you tried to create a single union query to combine the data and then create a separate crosstab in the QBE window using the union query as the source?

    If so and the problem still persists, could you post a sample database with some sample data in it? I'd like to play around with it a bit if you wouldn't mind.
    That worked for me, actually. Thanks.

Posting Permissions

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