Subtract last record from current record in query

[HalfMoon2]

I am trying to calculate the taxes due this week in a query. Here is the scenario - I have a table named "Payroll Data Entry" with field including "PayID" (primary key), "PayDate", "TaxToDate", etc. I have created a query named Payroll with simple calculation to get "TaxToDate" totals. Based on the last pay date for an employee, I want to subtract the previous 'TaxToDate' record for an employee from the current 'TaxToDate' record for that employee and return that value in a field name "TaxThisWeek". This is what I have in my "TaxThisWeek" field in query:

TaxThisWeek:
(SELECT PayID, PayDate,TaxToDate,
(SELECT TOP 1 Dupe.TaxToDate
FROM Payroll Data Entry AS Dupe
WHERE Dupe.WeekNum = MeterReading.WeekNum
AND Dupe.PayDate < MeterReading.PayDate
ORDER BY Dupe.PayDate DESC, Dupe.PayID) AS PriorValue
FROM Payroll Data Entry; )

However, I continue to get a Run-time (3075) Error code, what am I missing or not doing?

N.B. An employee might not get paid on some pay date, so pay date may not be in number sequence. I have created WeekNum to get the week number the PayDate falls in.

Thanks in advance.
Mario

 

[pbaldy]

I'm guessing MeterReading is a copy/paste error from a sample you got? Also, because of the inadvisable spaces, this would have to be bracketed:

[Payroll Data Entry]

Fix those and let's see where we're at.

Paul
MS Access MVP 2007/2008