Groupby/Sum query on a union query (1 Viewer)

[sportsguy]

I have an MS 2010 Access union query running off of a single SQLSERVER 2012 table, and the query runs fine. All the output columns are aligned.
I the first three columns are the fiscal week, location and the invoice number
The next two columns are the invoiced amount, and the Balance remaining,
the final tow column are the prior week's invoiced amount, and the prior week's Remaining Balance.

SELECT fiscal week, location, invoice number, Current_WeeK_ Invoiced_Amt, CurrentWeek_Remaining_Balance, 0 as Prior_Week Invoiced_Amt, 0 As prior_ Week_Remaining_Balance . . . .

UNION ALL 
SELECT fiscal week, location, invoice number, 0 AS Current_WeeK_ Invoiced_Amt, 0 AS CurrentWeek_Remaining_Balance, Prior_Week Invoiced_Amt, prior_Week_Remaining_Balance . . . .

With a certain invoice number 50 records show up properly, total amounts are in the 40,000 and 10,000 range. . . all good


The issue comes when I create a group by and sum query on the union query and perform a sum of the dollar columns, and the query returns 2Billion and 100Mill as totals. . and for multiple invoices, the deficit sized numbers are repeat exactly for each invoice. . .

Is this a bug or do I need to do something else?

thanks in advance, sportsguy

 

[plog]

Do the UNION in one query and save it: qryUNION. Then to get your final results, build another query using qryUNION as the datasource.

 

[sportsguy]

plog,

That's what I did, was that not clear?

So, assuming that I did what you suggested, and I did already,
why would I be getting the 2 Billion as a total out of a total of maybe $200K?

Thanks, sportsguy

 

[plog]

Sorry, I see that now. It is clear you have done the subquery.

What's unclear is if the issue is with the subquery (the UNION) or the aggregate query. Is the UNION returning the correct number of rows?

If you believe its the aggregate query can you post its SQL?

 

[sportsguy]

Thanks for re-reading. .

The queries are all written correctly, doesn't matter if I use all invoices/remits, 200K in each query, or the subtotaling query which selects one invoice, with two rows, and the select query returns two rows with $400 or zero in each row, but when the group by and sum is selected, the answer goes to $2 billion

There was some kind of workaround but I can't remember it, I am just doing small dev quick in Access before copying and pasting into T-SQL

union query

SELECT dbo_REPORT_WEEK.PeriodWeek AS ReportPeriod, dbo_REPORT_WEEK.PeriodWeek_Prior, dbo_OPEN_INVOICES_W.strDistrict, dbo_OPEN_INVOICES_W.BillTo_Acct, dbo_OPEN_INVOICES_W.Invoice_Nbr, 0 AS Invoiced, 0 AS Remaining, dbo_OPEN_INVOICES_W.Invoice_Date, dbo_OPEN_INVOICES_W.Due_Date, 0 AS cwAge, dbo_OPEN_INVOICES_W.Age AS pwAge, dbo_OPEN_INVOICES_W.Remaining_Balance AS pwRemaining
FROM dbo_REPORT_WEEK INNER JOIN dbo_OPEN_INVOICES_W ON dbo_REPORT_WEEK.PeriodWeek_Prior = dbo_OPEN_INVOICES_W.PeriodWeek
;

UNION ALL SELECT dbo_REPORT_WEEK.PeriodWeek AS ReportPeriod, dbo_REPORT_WEEK.PeriodWeek, dbo_OPEN_INVOICES_W.strDistrict, dbo_OPEN_INVOICES_W.BillTo_Acct, dbo_OPEN_INVOICES_W.Invoice_Nbr, dbo_OPEN_INVOICES_W.Invoiced_Amt, dbo_OPEN_INVOICES_W.Remaining_Balance AS cwRemaining, dbo_OPEN_INVOICES_W.Invoice_Date, dbo_OPEN_INVOICES_W.Due_Date, dbo_OPEN_INVOICES_W.Age AS cwAge, 0 AS pwAge, 0 AS pwRemaining
FROM dbo_OPEN_INVOICES_W INNER JOIN dbo_REPORT_WEEK ON dbo_OPEN_INVOICES_W.PeriodWeek = dbo_REPORT_WEEK.PeriodWeek;

The select query returns two rows, with the Invoiced or remaining either 0 or $400, depending upon the record

SELECT uquOpenInvoices.ReportPeriod, uquOpenInvoices.Invoice_Nbr, uquOpenInvoices.Invoiced, uquOpenInvoices.Remaining, uquOpenInvoices.Invoice_Date, uquOpenInvoices.Due_Date, uquOpenInvoices.cwAge, uquOpenInvoices.pwAge, uquOpenInvoices.pwRemaining
FROM uquOpenInvoices
WHERE (((uquOpenInvoices.Invoice_Nbr)="77984297"));

Take the same query and make it a group by sum, and the answer is $2B, and it doesn't matter what invoice number. . .

SELECT uquOpenInvoices.ReportPeriod, uquOpenInvoices.Invoice_Nbr, Sum(uquOpenInvoices.Invoiced) AS SumOfInvoiced, Sum(uquOpenInvoices.Remaining) AS SumOfRemaining, uquOpenInvoices.Invoice_Date, uquOpenInvoices.Due_Date, Sum(uquOpenInvoices.cwAge) AS SumOfcwAge, Sum(uquOpenInvoices.pwAge) AS SumOfpwAge, Sum(uquOpenInvoices.pwRemaining) AS SumOfpwRemaining
FROM uquOpenInvoices
WHERE (((uquOpenInvoices.Invoice_Nbr)="77984297"))
GROUP BY uquOpenInvoices.ReportPeriod, uquOpenInvoices.Invoice_Nbr, uquOpenInvoices.Invoice_Date, uquOpenInvoices.Due_Date;

thanks!

 

[sportsguy]

don't worry about it, I just copy and pasted it to T-SQL and the query works fine. .
There is something about MS Access 2010, ODBC link to SQLSERVER 2012 and union queries, if I recall, but I can't since I am old!

sportsguy

 

[plog]

I'm wondering if it really is 2 billion. Are you sure its not scientific notation for a very small decimal?

I know there are rounding issues in Access. So individiually you can have this:

Field1, Field2
A, 0
A, 0

But when you GROUP BY Field1 and SUM Field2 you get this:

Field1, SumOfField2
A, 9.47893569844791E-33

Are you sure its 2 billion and not scientific notation for a very small value? That's just a stab in the dark, if that's not it, I don't have a clue.

 

[sportsguy]

plog,

finance guy here, know a $2B when I see it. .

and then why would 400 + 0 = scientific notation of any kind?

There is something about ODBC Access, SQL 2012 and union queries. .

I am just too old, and I was lazy, so I copied it over to t-SQL, and I am fine. .

but thanks for trying. . .

sportsguy. . .