Convert SQL Server Query To Access Query

[jo15765]

I am running this query in SQL Server so that only ONE employeeID shows the employee's salary, not each entry of the employeeID. This works as it should in SQL Server -> but for the life of me I can not discover how to re-write this in access.

What would be the access query syntax to achieve this same result?

;WITH data AS
(
  SELECT [empName], [empID], TR = SUM([empSal]) OVER (PARTITION BY [empName]),
    r = ROW_NUMBER() OVER (PARTITION BY [empName] ORDER BY [empName])
  FROM employee
  WHERE [empID] IS NOT NULL
),
All As
(
	Select
	[empName]
    ,[empID]
    ,[PayrollDate]
	,[DepositDate]
  FROM payroll  
  WHERE [empID] IS NOT NULL
)
SELECT 
[empName] = aj.[empName]
,[empID] = aj.[empID]
,[payrolldate] = aj.[PayrollDate]
,[empSal] = ROUND(COALESCE(CASE r WHEN 1 THEN TR END, 0),0)
FROM allempID aj
LEFT JOIN data d
ON aj.[empID] = d.[empID]
GROUP BY aj.[empName], aj.[empID], aj.[PayrollDate]

[ranman256]

instead of translating it word for word,
link the tables into access,
and build the query you want, adding items until you get whats needed.

[rpeare]

how about some example tables and the data in them and what the result set you want is. I am not great with interpreting SQL to MS Access SQL without the access to a SQL server.

[jo15765]

My issue is using the row_number() function. Since I only want to update the ONE row - and not every row.
I already have the tables linked into Access - I'll keep trying to build a query to get close.

Could I just use an Access Pass through query for this since I am only selecting data?