[AccessD] Update table with update query

Thanks, Ken, I think I had encountered this before, but like most bad
memories, I chose to ignore it. <G>  What threw me is exactly what you
wrote, "even the source query that you explicitly aren't updating".  So,
I've gone with Door #2, create a temp table.  And the table I'm updating is
a temp table (sheesh).  Thanks for the replies everyone.  Have a nice
weekend.

-----Original Message-----
From: Ken Ismert [mailto:KIsmert at TexasSystems.com] 
Sent: Friday, February 06, 2004 2:38 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Update table with update query


Randall,

Welcome to the unnecessarily restricted world of Jet SQL ;) Summary queries
aren't updatable. Jet requires ALL parts of the query to be updatable, even
the source query that you explicitly aren't updating. <Sigh>

This limitation chaps my hide regularly. Three ways around it:

1. Open the source query in code. Loop through the query, construct and
issue individual UPDATEs for each target record affected.

2. Append the source to a temp table, then update the target using the temp

3. Move to SQL Server or some other REAL data server that's smart enough to
do this in the obvious way

-Ken

-----Original Message-----
From: Randall Anthony [mailto:ranthony at wrsystems.com]
Sent: Friday, February 06, 2004 11:57 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Update table with update query


Hey group,
I have a problem that seems a no-brainer.  I use a select groupby query to
get a recordset.  I then use a second query with the first query and the
target table in order to update the target table.  I thought this should
work no problem, however I get an "Operation must use an updatable query"
error.  Is this one of those you can't get there from here things?  Using
A97.

Here's the sqls.
Get the dates.
SELECT tblInstall_7B_Navcert_Mams.Hull, Max(dbo_tblNavcert.NavcertDate) AS
NDate
FROM tblInstall_7B_Navcert_Mams INNER JOIN (dbo_tblLocation INNER JOIN
dbo_tblNavcert ON dbo_tblLocation.LocID = dbo_tblNavcert.LocID) ON
tblInstall_7B_Navcert_Mams.Hull = dbo_tblLocation.Hull
WHERE (((dbo_tblNavcert.EquipTypeName)="rlg"))
GROUP BY tblInstall_7B_Navcert_Mams.Hull;

Try to put the dates in the table
SELECT tblInstall_7B_Navcert_Mams.Hull, Max(dbo_tblNavcert.NavcertDate) AS
NDate
FROM tblInstall_7B_Navcert_Mams INNER JOIN (dbo_tblLocation INNER JOIN
dbo_tblNavcert ON dbo_tblLocation.LocID = dbo_tblNavcert.LocID) ON
tblInstall_7B_Navcert_Mams.Hull = dbo_tblLocation.Hull
WHERE (((dbo_tblNavcert.EquipTypeName)="rlg"))
GROUP BY tblInstall_7B_Navcert_Mams.Hull;

Randy @ ext. 473


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com