Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having trouble with 2 queries from the same data...

    My table has 3 columns (CapOp, Spend, Date). CapOp can have only 2 values, none are null.

    When I do this query[codebox]SELECT Format([Date],"YYYYMM") AS Expr1, Sum([Spend]) AS TheSpend
    FROM MyTable
    GROUP BY Format([Date],"YYYYMM");[/codebox]I get different values than I do for [codebox]TRANSFORM Sum(Spend) AS SumOfSpend
    SELECT CapOp
    FROM MyTable
    GROUP BY CapOp
    PIVOT Date;
    [/codebox]
    and I can not figure it out.

    ANyone know why I might get different values? It's driving me mad!!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Abraxus' post='797212' date='10-Oct-2009 15:19']I am having trouble with 2 queries from the same data...

    My table has 3 columns (CapOp, Spend, Date). CapOp can have only 2 values, none are null.

    When I do this query[codebox]SELECT Format([Date],"YYYYMM") AS Expr1, Sum([Spend]) AS TheSpend
    FROM MyTable
    GROUP BY Format([Date],"YYYYMM");[/codebox]I get different values than I do for [codebox]TRANSFORM Sum(Spend) AS SumOfSpend
    SELECT CapOp
    FROM MyTable
    GROUP BY CapOp
    PIVOT Date;
    [/codebox]
    and I can not figure it out.

    ANyone know why I might get different values? It's driving me mad!![/quote]
    One gives a total by date the other by capop and date.

    The one by date should be a grand total of the other.

    Is this not the case?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In addition to patt's reply, the first query groups by month, while the crosstab query uses the full date as column field, not the month.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I figured out the problem. It only took me, however, a day and a half.

    To validate my numbers, I took the results of the query and copy/pasted them to Excel. I would then AutoSum the data to see the totals.

    For some odd reason on one set of data in my cross tab, it worked fine, but in the second set, it included the header row, which was the date in YYYYMM format. It wasn't until I compared the numbers to see what the difference was that I was able to see the pattern. One month was off my $200,810 the next by $200,811, the next by $200,812 and so on.

    I could not believe that it was that simple...UGH!!!!!!!

    Looks like it's back to Excel Formulas 101 for me.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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