Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Reverse Crosstab Query

    Hi all,

    I am trying to reverse a crosstab Excel table I inherited
    Part_No's in Col A
    Dates across the top beginning in Col B (dtJan, dtFeb, etc...)

    I tried following the post by pbaldy using a UNIION query
    How to Create a Reverse Crosstab Query - Access World Forums

    Mine:
    Code:
    SELECT Part_No, "Jan_Date" as dtDate, Jan_Date AS QTY
    FROM tblParts
    UNION ALL;
    Error:
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE", 'SELECT', or 'UPDATE"
    Any thoughts what I am doing wrong?

    thx
    w

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The provided answer was:
    You can use a UNION query:

    SELECT Item, "Size_01" as Size, QTY
    FROM TableName
    UNION ALL
    SELECT Item, "Size_02" as Size, QTY
    FROM TableName
    ...
    __________________
    Paul
    Microsoft Access MVP
    UNION or UNION ALL implies that another SELECT expression follows. UNION ALL; makes no sense.

    See: UNION (Transact-SQL)
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    UNION [ALL] must be used to link two (or more) SELECT statements that you want to present as a single table. You need a SELECT statement for each month (or other date value), joined by UNION statements:
    Code:
    SELECT Part_No, 'Jan_Date' AS dtDate, Jan_Date As Qty
    UNION ALL
    SELECT Part_No, 'Feb_Date', Feb_Date
    UNION ALL
    SELECT Part_No, 'Mar_Date', Mar_Date
    UNION ALL
    ...
    SELECT Part_No, 'Dec_Date, Dec_Date;
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Sinndho, Weejas;

    Thanks for your help.
    In test, I removed UNION ALL from the first SELECT statement.
    That resolved the issue.

    Thx
    w
    Last edited by goss; 01-09-13 at 08:16. Reason: Typo

Posting Permissions

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