Another Union Query Question 2

[ecugrad]

In the below Union query, I'm trying to set the number of decimal places to 2 on Score field. What do I need to add to the query to do this?

Thanks again..

Mike

I'm using Acess 2003

SELECT [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department, Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]) AS Score
FROM ([R&R_PhoneAssessment] INNER JOIN [R&R_Employees] ON [R&R_PhoneAssessment].Agent = [R&R_Employees].EmployeeID) INNER JOIN [R&R_Departments] ON [R&R_Employees].Department = [R&R_Departments].Department
WHERE ((([R&R_PhoneAssessment].CallDate)>=#4/1/2006# And ([R&R_PhoneAssessment].CallDate)<=#4/30/2006#) AND (([R&R_PhoneAssessment].Auditor)=1))
GROUP BY [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department
ORDER BY [R&R_Employees].EmployeeID;
UNION SELECT [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department, Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]) AS Score
FROM ([R&R_OutBoundPhoneAssessment] INNER JOIN [R&R_Employees] ON [R&R_OutBoundPhoneAssessment].Agent = [R&R_Employees].EmployeeID) INNER JOIN [R&R_Departments] ON [R&R_Employees].Department = [R&R_Departments].Department
WHERE ((([R&R_OutBoundPhoneAssessment].CallDate)>=#4/1/2006# And ([R&R_OutBoundPhoneAssessment].CallDate)<=#4/30/2006#) AND (([R&R_OutBoundPhoneAssessment].Auditor)=1))
GROUP BY [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department
ORDER BY [R&R_Employees].EmployeeID;

 

[Golom]

Wrap your AVG calculation in

Val(Format(AVG(...),"0.00"))

or

Round(AVG(...),2)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ecugrad]

Golum, what I'm I doing wroung on the below? I'm getting an error message..

Thanks

SELECT [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department, Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]) AS Round(AVG(Score),2)



FROM ([R&R_PhoneAssessment] INNER JOIN [R&R_Employees] ON [R&R_PhoneAssessment].Agent = [R&R_Employees].EmployeeID) INNER JOIN [R&R_Departments] ON [R&R_Employees].Department = [R&R_Departments].Department
WHERE ((([R&R_PhoneAssessment].CallDate)>=#4/1/2006# And ([R&R_PhoneAssessment].CallDate)<=#4/30/2006#) AND (([R&R_PhoneAssessment].Auditor)="1"))
GROUP BY [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department
ORDER BY [R&R_Employees].EmployeeID;
UNION SELECT [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department, Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]) AS Score
FROM ([R&R_OutBoundPhoneAssessment] INNER JOIN [R&R_Employees] ON [R&R_OutBoundPhoneAssessment].Agent = [R&R_Employees].EmployeeID) INNER JOIN [R&R_Departments] ON [R&R_Employees].Department = [R&R_Departments].Department
WHERE ((([R&R_OutBoundPhoneAssessment].CallDate)>=#4/1/2006# And ([R&R_OutBoundPhoneAssessment].CallDate)<=#4/30/2006#) AND (([R&R_OutBoundPhoneAssessment].Auditor)="1"))
GROUP BY [R&R_Employees].EmployeeID, [R&R_Employees].BNumber, [R&R_Departments].DepartmentID, [R&R_Departments].Department
ORDER BY [R&R_Employees].EmployeeID;

 

[lespaul]

what's the error message?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ecugrad]

Select statement includes a reserved word or an argument that is misspelled or missing, or the punctuation uis incorrect..

 

[Golom]

This

Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]) AS Round(AVG(Score),2)

should be

Round(Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]), 2) AS [Score]

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

this line is incorrect:

Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]) AS Round(AVG(Score),2)

Access thinks you are trying to use Round as an alias for this calculation.

I think you want:

ROUND(Avg([GreetScore]+([ClientScore]*10)+([AccruacyScore]*8)+[ClosingScore]), 2) As Score

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design