Group By in Update Query - Error.

G

Guest

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.
 
D

Duane Hookom

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.
 
G

Guest

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);

Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP


Venus said:
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.
Click to expand...
Click to expand...
 
J

John Spencer

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 ????
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);

Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP


Venus said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.


John Spencer said:
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 ????
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);

Duane Hookom said:
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.

--
Duane Hookom
MS Access MVP


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

John Spencer

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?

Venus said:
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.


John Spencer said:
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 ????
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
something.

--
Duane Hookom
MS Access MVP


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

Guest

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;

John Spencer said:
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?

Venus said:
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.


John Spencer said:
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.

Venus wrote:

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
something.

--
Duane Hookom
MS Access MVP


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

John Spencer

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.


Venus said:
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;

John Spencer said:
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?

Venus said:
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.

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.

Venus wrote:

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
something.

--
Duane Hookom
MS Access MVP


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query on 2 other queries 1
perform INNER JOIN on query results 2
Convert Null to Zero in query 1
Please help with update/join/sum query 1
Problem with Access Update Statement 7
Error: Operation must use an updatable query 2
Update Query 2
Help with Query 2

Top