Link to home
Start Free TrialLog in
Avatar of CSG-Analysis
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
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;

Open in new window

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<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.
>>

 Sounds like a data type is not right (it's a long or single).

JimD.
Avatar of CSG-Analysis
CSG-Analysis

ASKER

I have that in the table as Number>>Double>>General Number
What about the other tables?  

JimD.
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.  
ASKER CERTIFIED SOLUTION
Avatar of CSG-Analysis
CSG-Analysis

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I did not add this to the database as it was basically EUE.