Query not Updateable - using a subquery

[KGPinard]

The following won't execute as "Query Not Updateable"
What I'm trying to do is get the maximum Original Collection date [OriginalCollectionDate], for each Job from the raw data. The [Raw Data full set] and [Raw data Enhanced Full Set] are a 1 to 1 relationship (ID fields are the keys). The Raw data is limited by the AegisJobname for the row being updated.

UPDATE distinctrow AegisJobNames
SET AegisJobNames.LastImportDate =
(select max(rdefs.originalcollectiondate) as Odate from [Raw Data Enhanced full set] as RDEFs inner join [raw data full set] as rdfs on rdefs.id=[rdfs].id where (rdfs.job=[AegisJobNames].AegisJobName) );

Anyone knows a good description for using sub queries referring back to the parent query?

tia
Ken

 

[dhookom]

You might have to resort to a domain aggregate function in Access SQL. First create a query that joins [Raw Data Enhanced full set] and [raw data full set]. Then try this SQL:

UPDATE AegisJobNames 
SET LastImportDate = 
DMax("originalcollectiondate","[Your Query Name]","job=""" & AegisJobName & """" );

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya KGPinard . . .

Have a look here: Why is my query read-only?

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!

 

[KGPinard]

Thank you, I tried to forget about those things.

I'll give it a try.

The subquery should work though. I use them off and on, sometimes they just run other times I have to do a work around. This is great ins SQL Server.

Ken

 

[dhookom]

Ken,
I think any query in Access that contains a group by will render the query read-only. I wish we had all of the functionality from SQL Server queries in Access queries :-(

BTW: welcome to Tek-Tips

Duane
Hook'D on Access
MS Access MVP