Stop from rounding up when combining queries

[ecugrad]

I'm utilizing Access 2003.

I'm having trouble with decimal places basically keeping my data from rounding up. I'm combining two queries, the IndPhoneMonitoringScores field in the below query when run shows my 2 decimal places with correct data,

SELECT [R&R_CombinedPhoneScores].BNumber AS [R&R_BNumber], [R&R_DeptPhoneMonitoringScores].Score AS [R&R_DeptPhoneMonitoringScore], [R&R_CombinedPhoneScores].Score AS [R&R_IndPhoneMonitoringScore]
FROM [R&R_CombinedPhoneScores] INNER JOIN [R&R_DeptPhoneMonitoringScores] ON [R&R_CombinedPhoneScores].DepartmentID = [R&R_DeptPhoneMonitoringScores].DepartmentID;

but when I pull the field into the below query, I lose my decimal places. I try to set the field to fix and two decimal places and I just receive 00's after the decimal. I'm confused as why the numbers after the decimal place won't show. Any help would be appreciated..

INSERT INTO [R&R_MonthlyMatrixScores] ( BNumber, [Year], [Month], DepartmentID, Department, LastName, FirstName, Salary, TypeID, IndPhoneMonitoringScore, DeptPhoneMonitoringScore, IndQAMScore, DeptQAMScore, ProductivityScore, HPDPoints, DisqID, DisqDesc, RunDate )
SELECT [R&R_EmployeeDepartmentLink].BNumber, 2006 AS [Year], 4 AS [Month], [R&R_EmployeeDepartmentLink].DepartmentID, [R&R_EmployeeDepartmentLink].Department, [R&R_EmployeeDepartmentLink].LastName, [R&R_EmployeeDepartmentLink].FirstName, [R&R_EmployeeDepartmentLink].Salary, [R&R_EmployeeDepartmentLink].TypeID, [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_IndPhoneMonitoringScore] AS IndPhoneMonitoringScore, [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_DeptPhoneMonitoringScore] AS DeptPhoneMonitoringScore, QA_CombinedIndTeamScores.QA_IndAccuracy AS IndQAMScore, QA_CombinedIndTeamScores.QA_DeptAccuracy AS DeptQAMScore, [R&R_MonthlyProductionScores].Score AS ProductivityScore, [R&R_HPDPoints].HPDPoints, QuarterlyDisqEmployees.DisqID, QuarterlyDisqEmployees.DisqDesc, Now() AS RunDate
FROM ((((([R&R_EmployeeDepartmentLink] LEFT JOIN [R&R_CombinedIndTeamPhoneMonitoringScores] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_CombinedIndTeamPhoneMonitoringScores].[R&R_BNumber]) LEFT JOIN QA_CombinedIndTeamScores ON [R&R_EmployeeDepartmentLink].BNumber = QA_CombinedIndTeamScores.BNumber) LEFT JOIN [R&R_HPDPoints] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_HPDPoints].BNumber) LEFT JOIN [R&R_MonthlyProductionScores] ON [R&R_EmployeeDepartmentLink].BNumber = [R&R_MonthlyProductionScores].USERNAME) LEFT JOIN QuarterlyDisqEmployees ON [R&R_HPDPoints].BNumber = QuarterlyDisqEmployees.BNumber) LEFT JOIN [R&R_MonthlyDisqEmployees] ON [R&R_HPDPoints].BNumber = [R&R_MonthlyDisqEmployees].BNumber
ORDER BY [R&R_EmployeeDepartmentLink].BNumber;


Mike

 

[PHV]

What are the data type and field size of IndPhoneMonitoringScore in [R&R_MonthlyMatrixScores] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ecugrad]

The IndPhoneMonitoringScore is Numeric with a scale of 3. Viewing the data before inserting, we see that the values are rounding up. What gets me is why it shows correctly in one query and not the other. Also, the DeptPhoneMonitoringScore field is the same data type, etc and displays correctly in both queries..

Thanks...

 

[PHV]

I asked for the field size: Long, Single, ... ?
Only Float, Single, Decimal and Currency hold a fractional part when stored.
Long, Integer and Byte are integral numbers.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886