• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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

0
CSG-Analysis
Asked:
CSG-Analysis
  • 4
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
CSG-AnalysisAuthor Commented:
I have that in the table as Number>>Double>>General Number
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What about the other tables?  

JimD.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CSG-AnalysisAuthor Commented:
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.  
0
 
CSG-AnalysisAuthor Commented:
AHHHHHHHHHHHHHH!!!!!!!!!!!

I was pulling from the wrong field.

Thanks for the help.
0
 
CSG-AnalysisAuthor Commented:
I did not add this to the database as it was basically EUE.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now