Update Query Problem

  • Thread starter Thread starter Annelie
  • Start date Start date
A

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
 
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
 
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
Click to expand...

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...
 
Can I do it with unique values?

John Vinson said:
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...
Click to expand...
 
That does not work either.
Click to expand...

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.
 
Your query must join on primary and foreign keys and the queries can't be
group by.
 
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
 
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.
Click to expand...

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.
Click to expand...

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
Click to expand...

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()) & "#");
 
Back
Top