Update Query Problem

[Annelie]

In my employee table where the social security number is the primary field.
I would like to be able to run an update query, which inserts the last time
the employee got paid into the releasedate field

However, it does not want to update. I get the error message: operation must
use an updatable query.
Annelie

 

[Duane Hookom]

Post your SQL.

 

[Annelie]

UPDATE QryFinal2forTerminationDate, TblEmployeeList SET
QryFinal2forTerminationDate.LastOfWeDate = tblEmployeeList!ReleaseDate;
From Reading Prior Posts, I think the problems is with my prior filtering
for the last date. I have to group the year to date data, to find the last
date.
Annelie

 

[John Vinson]

In my employee table where the social security number is the primary field.
I would like to be able to run an update query, which inserts the last time
the employee got paid into the releasedate field

However, it does not want to update. I get the error message: operation must
use an updatable query.
Annelie

Any Totals query, or any query containing a Totals query, is
non-updateable (even if logically it ought to be, as in this case).
You may need to use DMax() rather than a GROUP BY... MAX to identify
the last date.

Since I have no idea how your tables are structured or where one might
find the last pay date I can't be more specific than that, of
course...

 

[Annelie]

Can I do it with unique values?

Any Totals query, or any query containing a Totals query, is
non-updateable (even if logically it ought to be, as in this case).
You may need to use DMax() rather than a GROUP BY... MAX to identify
the last date.

Since I have no idea how your tables are structured or where one might
find the last pay date I can't be more specific than that, of
course...

 

[Annelie]

That does not work either.

Can I do it with unique values?

 

[John Vinson]

That does not work either.

Then don't do "that".

Since you don't give us any clue what you're doing, or how it's not
working, I'm a bit baffled at being able to help.

If you could, please, post the SQL of the query and an indication of
the manner in which it is failing, I'll be glad to try to help.

 

[Duane Hookom]

Your query must join on primary and foreign keys and the queries can't be
group by.

 

[Annelie]

I have a table (TblEmployees), with the Social Security Number as primary
key.
The 2nd Table is (tblYearToDate) that has many columns, containing the year
to date payroll transactions for the employee.

The 3rd table (tblHoursbyDay), which is the current week table. This table
has a line for every day the employee worked and for each different job. In
a query (QryAddWeDate), I add the week ending date and another field which
subtracts 7 days from the WEDate, for a PriorWeekDate.

In each table an employee can have several rows of data per week, because
there is a line for every job that he worked on in one week.

For the comparison purpose, I created 2 new queries. From QryAddweDate I
show only the PriorWeekDate and SocialSecurityNumber, and from the
TblYearToDate the WEDate and Social. I now I want to compare the
PriorWeekDate to the Last WeDate on the tblYearToDate.
Which works fine - I am able to compare that with grouping.

Now I would like to create an update query where the last WeDate the
employee worked and if he did not work this week, I would like to update the
TerminationDate field in the employee Table with the last WEDate from the
TblYearToDate.
Hope you haven't lost patience with me.
Annelie

First I built a query

 

[John Vinson]

I have a table (TblEmployees), with the Social Security Number as primary
key.
ok...

The 2nd Table is (tblYearToDate) that has many columns, containing the year
to date payroll transactions for the employee.

What are these "many columns"? Not repeating data I presume!

The 3rd table (tblHoursbyDay), which is the current week table. This table
has a line for every day the employee worked and for each different job. In
a query (QryAddWeDate), I add the week ending date and another field which
subtracts 7 days from the WEDate, for a PriorWeekDate.

In each table an employee can have several rows of data per week, because
there is a line for every job that he worked on in one week.

ok... sounds like a decent design.

For the comparison purpose, I created 2 new queries. From QryAddweDate I
show only the PriorWeekDate and SocialSecurityNumber, and from the
TblYearToDate the WEDate and Social. I now I want to compare the
PriorWeekDate to the Last WeDate on the tblYearToDate.
Which works fine - I am able to compare that with grouping.

Now I would like to create an update query where the last WeDate the
employee worked and if he did not work this week, I would like to update the
TerminationDate field in the employee Table with the last WEDate from the
TblYearToDate.
Hope you haven't lost patience with me.
Annelie

I'd be uncomfortable about storing this derived data in a table, but
if you want to do that...

UPDATE tblEmployees
SET TerminationDate =
DMax("[WEDate]", "tblYearToDate", "[Social] = '" & [SSN] & "' AND
PriorWeekDate < #" & DateAdd("d", -7, Date()) & "#");