Updating field with date from another table

[Jarekb]

Hello all,

I've been trying to figure out the logic for this, thought I had it, then couldn't write up the SQL correctly, so I'll ask here.
I'm trying to send a letter to a list of employees notifying them of changes to the way they get paid.

Details
EMPL_INFO table contains employee ID and paygroup
PROCESS table has a date field that will be added to the letter. Field is called DT_PP_1.
PAY_CALENDAR has dates for when each paygroup gets their checks. It has a field called Check_DT.

I want to write an update query that will add the next pay date to the process table under DT_PP_1. I wrote this query to find the next date and it works fine.

SELECT Min(PERIOD_CALENDAR.CHECK_DT) AS Expr1
FROM PERIOD_CALENDAR
WHERE (((PERIOD_CALENDAR.CHECK_DT)>Date()) AND ((PERIOD_CALENDAR.PAYGROUP)='00E'));

I want to replace the 00E part with a sub query that will take an employee id from PROCESS table and look up that employee's paygroup in the EMPL_INFO table. Then I'd like to stick all that in an update query.

This is what I came up with, but getting syntax errors that I couldn't find/fix.

UPDATE PROCESS SET PROCESS.DT_PP_1 = ( SELECT MIN(PERIOD_CALENDAR.CHECK_DT ) FROM PERIOD_CALENDAR WHERE (((PERIOD_CALENDAR.CHECK_DT)>DATE()) AND ((PERIOD_CALENDAR.PAYGROUP)='DLookup("Paygroup", "EMPL_INFO", "EMPLID=[PROCESS.EMPLID])'))

I'm hoping someone can help.

Thanks

 

[Remou]

You need something like this:

[tt]UPDATE PROCESS SET PROCESS.DT_PP_1 = ( SELECT MIN(PERIOD_CALENDAR.CHECK_DT ) FROM PERIOD_CALENDAR WHERE PERIOD_CALENDAR.CHECK_DT>DATE() AND PERIOD_CALENDAR.PAYGROUP=DLookup("Paygroup", "EMPL_INFO", "EMPLID=" & [PROCESS.EMPLID]))[/tt]

However, you will need to make some changes if you are running this in code rather than the query window.

 

[Jarekb]

I get an error saying "Operation must use an updateable query."

I'm not sure if my query is constructed correctly. It should update multiple employee records in the process table. Also the pay calendar table has multiple entries for each group, one for each paydate, but I'm not sure if the returning value is a single date.

I've tried just creating a query to display just the employee id, paygroup, and next pay date. Then using another query to update the process table with the date value from the first query. I ran into some errors that I think were similar to the one I stated above.

Any ideas?

 

[Jarekb]

Does anyone have an idea? I've been stuck on this for three days and really want it to be resolved.