"Not part of aggregate function" update query problem

[darrask]

Hi guys,
I'm sorry I could not find the solution to my problem on this forum, though I searched, so I post here. But it should be pretty obvious, because what I'm trying to do sounds basic...

I have a table, named "ES_Detail", in which I want to update its field [Date created] (if empty) with the oldest date from the child field [Entry Date] in its linked table named "ES".

Such a thing should be easy to do but I managed only to do syntax errors with my limited SQL knowledge.

The SQL query I have so far, that is not working, is:

UPDATE ES_Detail INNER JOIN ES ON ES_Detail.[ES ID] = ES.[ES ID] SET ES_Detail.[Date created] = First([ES].[Entry Date])
WHERE (((ES_Detail.[Date created]) Is Null));

That tells me that I tried to execute a query that does not include [Date created] as part of an aggregate function.

Anyone having an idea of the simple change I should do to carry out this simple update query? Thanks in advance.

 

[jzwp22]

You could try something like this:

UPDATE ES_Detail SET ES_Detail.[Date created] = DMin("[Entry Date]","ES","[ES ID]=" & ES_Detail.[ES ID])
WHERE (((ES_Detail.[Date created]) Is Null));

 

[darrask]

Thanks a lot, that really helped, and I get the logic now.

 

[jzwp22]

You're welcome