Updateable query

[Guest]

Having the old "Operation must use an updateable query" problem and not sure
why.

Query #1 (called DailyHeaderLookup)

SELECT FishCountsImport.ProjectID, FishCountsImport.ShiftDate
FROM FishCountsImport
GROUP BY FishCountsImport.ProjectID, FishCountsImport.ShiftDate;

I also tried:

SELECT DISTINCT FishCountsImport.ProjectID, FishCountsImport.ShiftDate
FROM FishCountsImport;

These both give me the same results: a single record for each
ProjectID/ShiftDate combination in FishCountsImport table.

Now, Query #2 (the one that is failing):

UPDATE DailyHeaderLookup INNER JOIN DailyHeader ON
(DailyHeaderLookup.ShiftDate = DailyHeader.ShiftDate) AND
(DailyHeaderLookup.ProjectID = DailyHeader.ProjectID) SET
DailyHeader.Status = "Updated";

Because the two field involved are together the primary key of the
DailyHeader table, and the DailyHeaderLookup is a SELECT DISTINCT or GROUP BY
on the same two fields, you can see that the relationship is one-to-one
between DailyHeaderLookup (query #1) & DailyHeader (the table I am attempting
to update)

Why is this not non-updateable? I know I can instead use DCount in a single
query, like this:

UPDATE DailyHeader SET DailyHeader.Status = "Updated"
WHERE (((DCount("*","[FishCountsImport]","[ProjectID] = " & [ProjectID] & "
and [ShiftDate] = #" & [ShiftDate] & "#"))>0));

I'm just trying to avoid introducting the domain aggregate into a query, but
is it the only way to get what I want?

 

[Guest]

Hmmm...read a little further in the forum and see that the DISTINCT or GROUP
BY in any query involved in the process makes the final query non-updateable,
even though the actual update is intended for the table, not the DISTINCT
query.

This said, is there an alternative to using the domain aggregate in the
query to get the desired result, or is that just the best option?

UPDATE DailyHeader SET DailyHeader.Status = "Updated"
WHERE (((DCount("*","[FishCountsImport]","[ProjectID] = " & [ProjectID] & "
and [ShiftDate] = #" & [ShiftDate] & "#"))>0));

 

[Rick Brandt]

Having the old "Operation must use an updateable query" problem and
not sure why.

Query #1 (called DailyHeaderLookup)

SELECT FishCountsImport.ProjectID, FishCountsImport.ShiftDate
FROM FishCountsImport
GROUP BY FishCountsImport.ProjectID, FishCountsImport.ShiftDate;

I also tried:

SELECT DISTINCT FishCountsImport.ProjectID, FishCountsImport.ShiftDate
FROM FishCountsImport;

These both give me the same results: a single record for each
ProjectID/ShiftDate combination in FishCountsImport table.

Now, Query #2 (the one that is failing):

UPDATE DailyHeaderLookup INNER JOIN DailyHeader ON
(DailyHeaderLookup.ShiftDate = DailyHeader.ShiftDate) AND
(DailyHeaderLookup.ProjectID = DailyHeader.ProjectID) SET
DailyHeader.Status = "Updated";

Because the two field involved are together the primary key of the
DailyHeader table, and the DailyHeaderLookup is a SELECT DISTINCT or
GROUP BY on the same two fields, you can see that the relationship is
one-to-one between DailyHeaderLookup (query #1) & DailyHeader (the
table I am attempting to update)

Why is this not non-updateable? I know I can instead use DCount in a
single query, like this:

UPDATE DailyHeader SET DailyHeader.Status = "Updated"
WHERE (((DCount("*","[FishCountsImport]","[ProjectID] = " &
[ProjectID] & " and [ShiftDate] = #" & [ShiftDate] & "#"))>0));

I'm just trying to avoid introducting the domain aggregate into a
query, but is it the only way to get what I want?

Access insists that ALL of the tables/queries in an Action query be
updateable even if the non-updateable ones are not being updated by the
query. Both GroupBy and DISTINCT cause a query to be read only so that is
your problem.

You might be able to use an IN() clause instead of a join to work around the
issue.

 

[Allen Browne]

Query #1 will not be updatable, since it uses GROUP BY or DISTINCT.
As a result, Query #2 will not be updatable if you INNER JOIN Query #1.

You could probably use a subquery in the WHERE clause. It should be much
more efficient than DCount().

This kind of thing:
UPDATE DailyHeader SET DailyHeader.Status = "Updated"
WHERE EXISTS (SELECT ProjectID FROM FishCountsImport
WHERE FishCountsImport.ProjectID = DailyHeader.ProjectID
AND FishCountsImport.ShiftDate = DailyHeader.ShiftDate);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

(Of course, one might argue that this Status is an unnormalized field, and
should be calculated rather than stored.)