Syntax in query involving stdev and avg (1 Viewer)

[Margarita]

Hello,
I want to get payments per employee which are over 2 standard deviations over/under the average. For this I have two subqueries below and the main query which picks out the individual payments. Both subqueries run successfully. The nested subquery in the FROM clause of the main query also works when I run it on its own. Yet I keep getting a syntax error at the main query with the nested subquery highlighted. I've been at this for over an hour. Would someone please take a look and where I messed up the syntax. Thank you!

1. SalariesCleaning_Avg_forLargePayments:

SELECT LastName, FirstName, avg(payment) AS PayAvg
FROM monthlysalaries_REG
WHERE PAY_TYPE Like '100' And PAYMENT>0
GROUP BY lastname, firstname;

2. SalariesCleaning_StDev_forLargePayments

SELECT LastName, FirstName, STDEV(payment) AS PayStDev
FROM monthlysalaries_REG
WHERE PAY_TYPE Like '100' And PAYMENT>0
GROUP BY lastname, firstname;

Main:

SELECT A.lastname, A.firstname, 
PayAvg as [Average Code 100 Payment per Employee],
PayStDev as [Standard Deviatoon of Payment],
A.earn_DATE, A.pay_type, A.OTtype, A.Payment, A.paystatus
from
SalariesCleaning_StDev_forLargePayments
inner join
(select monthlysalaries_REG.lastname, monthlysalaries_REG.firstname, 
PayAvg as [Average Code 100 Payment per Employee],
monthlysalaries_REG.earn_DATE, monthlysalaries_REG.pay_type, monthlysalaries_REG.OTtype, Monthlysalaries_REG.Payment, monthlysalaries_REG.paystatus
from monthlysalaries_REG inner join  SalariesCleaning_Avg_forLargePayments
on
monthlysalaries_REG.lastname=SalariesCleaning_Avg_forLargePayments.lastname and
monthlysalaries_REG.firstname=SalariesCleaning_Avg_forLargePayments.firstname) as A
on 
A.lastname= SalariesCleaning_Avg_forLargePayments.lastname and A.firstname= SalariesCleaning_Avg_forLargePayments.firstname
where 
A.earn_date between [start date] and [end date]
and 
abs(payment- PayAvg)> 2*PayStDev;

;

 

[jdraw]

I only found this sample when looking for info from M$oft.

http://office.microsoft.com/en-us/access-help/HV080761093.aspx