Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Cool Unanswered: Wacky query results

    Ok. SQL is usually very logical and predictable, but I've just hit some very strange results for a simple query:

    I'm hitting SQL Server through a linked table in Access00 and producing query results that match the original table. Unfortunately, I expected the total of the [amt] column to equal 0 and it was off by a small amount. So I figure the source table's data is wrong. But then I turn the query into an aggregrate query grouped by every column. Suddenly the [amt] column equals 0. Nothing has been changed other than grouping, yet the resulting dataset is different (both with the exact same number of records). It's almost as if there's a hidden record in the source table that's only being applied in the aggregate query. How can this be?

    This would seem especially troublesome since many RDBMS's don't allow grouping without an aggregate function being applied to at least one attribute (Oracle, for instance).

    Anyone run into this?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What datatype is it?

    Floats are known to cause bizarre little rounding errors...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Posts
    167
    The format is currency.

    The non-grouped (incorrect) query is as follows:

    SELECT dbo_export_expense_recs.invoice, dbo_export_expense_recs.amount, dbo_export_expense_recs.currencycode
    FROM dbo_export_expense_recs
    WHERE (((dbo_export_expense_recs.invoice)='testinvoice') );

    yields the following amt for recs.amount: $24.96

    The grouped (correct) query is as follows:

    SELECT dbo_export_expense_recs.invoice, dbo_export_expense_recs.amount, dbo_export_expense_recs.currencycode
    FROM dbo_export_expense_recs
    GROUP BY dbo_export_expense_recs.invoice, dbo_export_expense_recs.amount, dbo_export_expense_recs.currencycode
    HAVING (((dbo_export_expense_recs.invoice)=testinvoice));

    yields $43.28

    The non-grouped query seems to be dropping 18.32 somewhere.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If your grouping levels dictate that a given value will be repeated, it will be counted multiple times when you sum the column.

    That might be your issue. I can't really know what sort of quirks you'd run into with those grouping levels without the actual data. Try exporting the table to excel and getting a real, true value so you know which is which. I'm betting the grouping level is counting certain values multiple times.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2003
    Posts
    167
    That's what I originally thought - but both the grouped and nongrouped have the exact same number of records! The only thing that changes is the amount in 1 record of 8. This defies everything I know about grouping, and makes me wonder if what I'm really dealing with here is a JET vs SQLServer anomoly.

    If I figure out how to paste pictures in this forum, I'll post the sample.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I still recommend exporting the table to excel and getting the true value. I am convinced there is additional summing occuring.

    Also, why are you using group in the first place? There's no reason to do so unless you have an aggregate function in your query.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2003
    Posts
    167
    I've exported both results into Excel and there is no summing going on. The grouping was only tried out of desperation and it actually worked.

    I'm away from the data right now, but I'll paste the statements and data results tomorrow.

  8. #8
    Join Date
    Nov 2003
    Posts
    167
    How odd. Today I deleted the ODBC linked table and then re-linked it and the data is now correct. I don't understand this - and it makes me a little nervous to think that my linked tables may be passing bad data, but the data looks right for now.

    Maybe I should pull this query into a form_open event and use a dsn-less connection each time (since refreshing the connection seemed to help)?

    hmmmm...

Posting Permissions

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