CSG-Analysis
asked on
How to Stop Union Query from Rounding
Hello all,
I have a Union query in MS ACCESS 2007 that rounds.
In the middle section I know that there should be a very specific value, 148.95. I kow this because all the characteristics of the record are unique, so it should be a group of 1.
However it becomes 149 in the output of the query. As a matter of fact none of the records in that section have decimal values (suspicious) so I am sure that this rounding is happening accross the whole section.
The first and third sections have decimals in the output.
I have tried applying CCur and CDbl to the SUM funtion, to no avail. (I got those hints from researching in EE).
Any advice would be greatly appreciated.
~cheers ~ Sean
I have a Union query in MS ACCESS 2007 that rounds.
In the middle section I know that there should be a very specific value, 148.95. I kow this because all the characteristics of the record are unique, so it should be a group of 1.
However it becomes 149 in the output of the query. As a matter of fact none of the records in that section have decimal values (suspicious) so I am sure that this rounding is happening accross the whole section.
The first and third sections have decimals in the output.
I have tried applying CCur and CDbl to the SUM funtion, to no avail. (I got those hints from researching in EE).
Any advice would be greatly appreciated.
~cheers ~ Sean
SELECT Query1.[Order Number] AS [order ID], Query1.[Order Date] AS [Date], tblEmployee.[Name], Query1.SALES AS [Commission Value], Query1.[Part Number] AS [Product ID]
FROM Query1 INNER JOIN Employee ON Query1.[Agent Name] = tblEmployee.[SA ID]
GROUP BY Query1.[Order Number], Query1.[Order Date], tblEmployee.[Name], Query1.SALES, Query1.[Part Number]
UNION ALL
SELECT Table1.[Contract #] AS [Order ID], Table1.[Created Date] AS [Date], tblEmployee.[Name], Sum(Table1.[Contract Amount]) AS [Commission Value],Table1.[Contract #] AS [Product ID]
FROM Table1 INNER JOIN tblEmployee ON Table1.[Commissionable Agent] = tblEmployee.[SFDC ID]
GROUP BY Table1.[Contract #], Table1.[Created Date], Employee.[Name],Table1.[ESA Contract #]
UNION ALL
SELECT Query2.[Order ID], Query2.Date2, tblEmployee.[Name], Sum(Query2.Total) AS [Commission Value],Query2.Code as [Product ID]
FROM Query2 INNER JOIN tblEmployee ON Query2.Value = tblEmployee.[Yahoo ID]
GROUP BY Query2.[Order ID], Query2.Date2, tblEmployee.[Name], Query2.REXCode;
ASKER
I have that in the table as Number>>Double>>General Number
What about the other tables?
JimD.
JimD.
ASKER
The first is a query that is based on a table. That has Number>>Double>>currency.
The second is a query that is based on a linked table in a system I do not know very well. When I view the read only design view for that table it shows Number>>decimal.
I just wrote another query to simply look at the dollar values in the table. When I look directly in the table I see 148.95. But a straight forward, single field, all defaults select query rounds the value to 149.
The second is a query that is based on a linked table in a system I do not know very well. When I view the read only design view for that table it shows Number>>decimal.
I just wrote another query to simply look at the dollar values in the table. When I look directly in the table I see 148.95. But a straight forward, single field, all defaults select query rounds the value to 149.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did not add this to the database as it was basically EUE.
The first and third sections have decimals in the output.
>>
Sounds like a data type is not right (it's a long or single).
JimD.