Group By in Update Query

Group By in Update Query - Error.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Venus - 11 Nov 2005 18:40 GMT

I have an update query as below:

UPDATE TimeSheets
Set InvoiceTallyFlag = 'Yes'
From TimeSheets as TS INNER JOIN (ImportedSFUS ON TS.WeekEnding =
ImportedSFUS.Weekending AND TS.EmployeeID = ImportedSFUS.EmpID)
Group By TS.WeekEnding, TS.EmployeeID
Having [TimeSheets]![HoursWorked] = Sum(Nz([ImportedSFUS].[Hours],0));

Its giving me this error:
Syntax error: Missing operator.

Please help. Can't figure out where the error is.

Reply to this Message

Duane Hookom - 11 Nov 2005 19:44 GMT

Don't waste your time with this query since you will never be able to use
GROUP BY like this in an update query. You might be able to use DLookup() or
something.

Signature

Duane Hookom
MS Access MVP

>I have an update query as below:
>
[quoted text clipped - 9 lines]
>
> Please help. Can't figure out where the error is.

Reply to this Message

Venus - 13 Nov 2005 19:06 GMT

I have the following query which doesn't error out and does update some of
the rows but not all ????
Can't understand why its not updating all the rows.

UPDATE TimeSheets INNER JOIN ImportedSFUS ON (TimeSheets.EmployeeID =
ImportedSFUS.EmpID) AND (TimeSheets.WeekEnding = ImportedSFUS.Weekending) SET
TimeSheets.InvoiceTallyflag = Yes
WHERE [TimeSheets]![HoursWorked] = (select Sum(Nz([ImportedSFUS].[Hours],0))
from ImportedSFUS where TimeSheets.EmployeeID = ImportedSFUS.EmpID) AND
(TimeSheets.WeekEnding = ImportedSFUS.Weekending);

> Don't waste your time with this query since you will never be able to use
> GROUP BY like this in an update query. You might be able to use DLookup() or
[quoted text clipped - 13 lines]
> >
> > Please help. Can't figure out where the error is.

Reply to this Message

John Spencer - 13 Nov 2005 19:28 GMT

Probably data related.

What type of field is HoursWorked and Hours? Double or single are both floating
decimal numbers and therefore can both be problematic on matching if they have a
decimal component.

I might try forcing them to currency type for the purpose of query

UPDATE TimeSheets INNER JOIN ImportedSFUS ON (TimeSheets.EmployeeID =
ImportedSFUS.EmpID) AND (TimeSheets.WeekEnding = ImportedSFUS.Weekending) SET
TimeSheets.InvoiceTallyflag = Yes
WHERE CCUR([TimeSheets]![HoursWorked]) = (SELECT
CCur(Sum(Nz([ImportedSFUS].[Hours],0)))
from ImportedSFUS where TimeSheets.EmployeeID = ImportedSFUS.EmpID) AND
(TimeSheets.WeekEnding = ImportedSFUS.Weekending);

Also you don't need the NZ function, since Sum ignores nulls in its calculations.

> I have the following query which doesn't error out and does update some of
> the rows but not all ????
[quoted text clipped - 28 lines]
> > >
> > > Please help. Can't figure out where the error is.

Reply to this Message

Venus - 14 Nov 2005 17:10 GMT

They are both double. I tried CCUR on both and still the same problem.
When I use the same query as SELECT rather than UPDATE, it matches all the
rows. Only in update it doesn't update all of them. Its frustrating.

> Probably data related.
>
[quoted text clipped - 46 lines]
> > > >
> > > > Please help. Can't figure out where the error is.

Reply to this Message

John Spencer - 14 Nov 2005 17:33 GMT

Anyone else?

I'm stuck. I've not seen this behavior.

What version of Access? Are you using some other database engine for the
data - Oracle, SQL, MySQL, etc?

> They are both double. I tried CCUR on both and still the same problem.
> When I use the same query as SELECT rather than UPDATE, it matches all the
[quoted text clipped - 60 lines]
>> > > >
>> > > > Please help. Can't figure out where the error is.

Reply to this Message

Venus - 14 Nov 2005 17:59 GMT

Access 2002. No other database engine.

I'm trying another approach.
Created a Select query which has the Sum. Then trying to create an Update
query using the previous query. It gives me an error: "Operation must use an
updateable query". Maybe you can help with this one. Below are my queries:

QUERY1:
SELECT Sum(Nz([ImportedSFUS].[Hours],0)) AS Expr1, timesheets.EmployeeID,
timesheets.WeekEnding, timesheets.HoursWorked, timesheets.InvoiceTallyflag
FROM ImportedSFUS INNER JOIN timesheets ON (ImportedSFUS.EmpID =
timesheets.EmployeeID) AND (ImportedSFUS.Weekending = timesheets.WeekEnding)
GROUP BY timesheets.EmployeeID, timesheets.WeekEnding,
timesheets.HoursWorked, timesheets.InvoiceTallyflag
HAVING (((timesheets.HoursWorked)=Sum(Nz([ImportedSFUS].[Hours],0))));

QUERY2:
UPDATE timesheets INNER JOIN SumOfInvoiceHours ON (timesheets.WeekEnding =
SumOfInvoiceHours.WeekEnding) AND (timesheets.EmployeeID =
SumOfInvoiceHours.EmployeeID)
SET timesheets.InvoiceTallyflag = Yes;

> Anyone else?
>
[quoted text clipped - 67 lines]
> >> > > >
> >> > > > Please help. Can't figure out where the error is.

Reply to this Message

John Spencer - 14 Nov 2005 20:58 GMT

You can never use an aggregate query in an UPDATE with Access.

If query one is giving you the correct information, I would suggest you
might use it to do an APPEND to a temporary work table. Then you can use
that temporary work table in the UPDATE query.

I still have a feeling that the problem has something to do with trying to
match the SUM of HOURs to HoursWorked. IF you hadn't said that the query
works as a SELECT query I would be concentrating on that aspect of the
situation.

> Access 2002. No other database engine.
>
[quoted text clipped - 102 lines]
>> >> > > >
>> >> > > > Please help. Can't figure out where the error is.

Reply to this Message