unupdateable query

[Guest]

why is this not updateable -- designed in design view but sent sql view. if
i have to remove the group by -- what should i replace it with?

SELECT [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Service, [Service Record].Printed
FROM LU_ServicesII INNER JOIN [Service Record] ON LU_ServicesII.[Service
Name] = [Service Record].Service
WHERE (((LU_ServicesII.[Service Name])=[Enter Service Name]) AND (([Service
Record].Render)=No) AND (([Service Record].Cancel)=No))
GROUP BY [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Service, [Service Record].Printed
HAVING ((([Service Record].Printed)=No));

 

[Tom Ellison]

Dear Ian:

Whenever you GROUP BY, there can be a row of the query results that
originate from more than one source row. If you were to update something to
this query, it could not find just one of the potentially many rows to which
to update, and it will NOT update all of them.

As you do not have any aggregate functions, I'm not sure why you are using a
GROUPed query. Perhaps you do not mean to, and do not need to. What is
wrong with this:

SELECT [Service Record].PropertyID, [Service Record].ServiceID,
[Service Record].Service, [Service Record].Printed
FROM LU_ServicesII
INNER JOIN [Service Record]
ON LU_ServicesII.[Service Name] = [Service Record].Service
WHERE LU_ServicesII.[Service Name] = [Enter Service Name]
AND [Service Record].Render = No
AND [Service Record].Cancel = No
AND [Service Record].Printed = No;

Perhaps this will display duplicate rows. If you have such duplicates and
you wish to update one of them, do you then intend to update all of them?
It won't typically do this all in one operation, and that's why your
original query is not updatable.

Tom Ellison

 

[Guest]

I rewrote it to the following. it allows editing but doesn't limit the
records the way i wanted:

SELECT [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Printed, [Service Record].Service
FROM [Service Record]
WHERE ((([Service Record].Render)=No) AND (([Service Record].Cancel)=No));

but i want it to prompt for the [service record].service. in the previous
version it prompt [Enter service type] -- i'd type "aeration" (one of the
types) and it would only show the service types equal to that value. I get
some sort of error message about "having" statement without a group by
function. can you help me thru this last step. it's allowing me to edit it
now -- i just have to limit the records showing.

Dear Ian:

Whenever you GROUP BY, there can be a row of the query results that
originate from more than one source row. If you were to update something to
this query, it could not find just one of the potentially many rows to which
to update, and it will NOT update all of them.

As you do not have any aggregate functions, I'm not sure why you are using a
GROUPed query. Perhaps you do not mean to, and do not need to. What is
wrong with this:

SELECT [Service Record].PropertyID, [Service Record].ServiceID,
[Service Record].Service, [Service Record].Printed
FROM LU_ServicesII
INNER JOIN [Service Record]
ON LU_ServicesII.[Service Name] = [Service Record].Service
WHERE LU_ServicesII.[Service Name] = [Enter Service Name]
AND [Service Record].Render = No
AND [Service Record].Cancel = No
AND [Service Record].Printed = No;

Perhaps this will display duplicate rows. If you have such duplicates and
you wish to update one of them, do you then intend to update all of them?
It won't typically do this all in one operation, and that's why your
original query is not updatable.

Tom Ellison

why is this not updateable -- designed in design view but sent sql view.
if
i have to remove the group by -- what should i replace it with?

SELECT [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Service, [Service Record].Printed
FROM LU_ServicesII INNER JOIN [Service Record] ON LU_ServicesII.[Service
Name] = [Service Record].Service
WHERE (((LU_ServicesII.[Service Name])=[Enter Service Name]) AND
(([Service
Record].Render)=No) AND (([Service Record].Cancel)=No))
GROUP BY [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Service, [Service Record].Printed
HAVING ((([Service Record].Printed)=No));

 

[Guest]

problem solved! thank you very much

Dear Ian:

Whenever you GROUP BY, there can be a row of the query results that
originate from more than one source row. If you were to update something to
this query, it could not find just one of the potentially many rows to which
to update, and it will NOT update all of them.

As you do not have any aggregate functions, I'm not sure why you are using a
GROUPed query. Perhaps you do not mean to, and do not need to. What is
wrong with this:

SELECT [Service Record].PropertyID, [Service Record].ServiceID,
[Service Record].Service, [Service Record].Printed
FROM LU_ServicesII
INNER JOIN [Service Record]
ON LU_ServicesII.[Service Name] = [Service Record].Service
WHERE LU_ServicesII.[Service Name] = [Enter Service Name]
AND [Service Record].Render = No
AND [Service Record].Cancel = No
AND [Service Record].Printed = No;

Perhaps this will display duplicate rows. If you have such duplicates and
you wish to update one of them, do you then intend to update all of them?
It won't typically do this all in one operation, and that's why your
original query is not updatable.

Tom Ellison

why is this not updateable -- designed in design view but sent sql view.
if
i have to remove the group by -- what should i replace it with?

SELECT [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Service, [Service Record].Printed
FROM LU_ServicesII INNER JOIN [Service Record] ON LU_ServicesII.[Service
Name] = [Service Record].Service
WHERE (((LU_ServicesII.[Service Name])=[Enter Service Name]) AND
(([Service
Record].Render)=No) AND (([Service Record].Cancel)=No))
GROUP BY [Service Record].PropertyID, [Service Record].ServiceID, [Service
Record].Service, [Service Record].Printed
HAVING ((([Service Record].Printed)=No));