Aggregate function max in update query in access

[Sreedevi]

Hi ALL,

I have 2 tables, trying to update one table filed by using max(onefiled) from other table.

my qyery is as follows:

my tables are tblSubFlowForecast and tblPickCalendar

I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.

UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delivery SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");

I am getting error saying that "Operation must be an updateable query"


I have also tried in other way:

UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delivery AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar

This is giving me syntax error (missing operator)


Can any body tell me why it is giving error?
Thanks in advance

 

[ajetrumpet]

Instead of this:

UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delivery SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");

I am getting error saying that "Operation must be an updateable query"

Try this:

UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON 
   tblSubFlowForecast.Delivery = tblPickCalendar.Delivery SET 

      tblSubFlowForecast.[Latest Replen Date] = tblPickCalendar.[Pick]

[COLOR="Red"][U]WHERE[/U][/COLOR] tblPickCalendar.[Pick] [COLOR="Red"][U]IN[/U][/COLOR] (SELECT Max(tblPickCalendar.[Pick]) 
   FROM tblPickCalendar [COLOR="Red"][U]WHERE[/U][/COLOR] Type = "Replen");

This is just an extension of what you can find here.

 

[Sreedevi]

Thanks a lot ajetrumpet..

UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");

It is working but it is updating only those rows depending on the criteria in where clause, so it is updating only one row..

But I want to update all the rows with type as Replen with max(pick)

Sample data is as follows:

tblSubFlowForecast:

Delivery LatestReplenDate
12/01/2007
12/02/2006
11/01/2005


tblPIckCalendar:

Delivery Type Pick
12/01/2007 Replen 2/2/2008
12/02/2006 Replen 1/8/2007
11/01/2005 Replen 1/9/2007
10/9/2004 NF Bleedoff 1/6/2007

I want to update tblSubFlowForecast by matching the delivery date in tblpickcalendar where Type is Replen, with max(pick)

So for the data as shown above, all the 3 rows in tblSubFlowforecast should get update LatestReplenDate as 2/2/2008(max of pick).

 

[ajetrumpet]

tblSubFlowForecast:

Delivery LatestReplenDate
12/01/2007
12/02/2006
11/01/2005


tblPIckCalendar:

Delivery Type Pick
12/01/2007 Replen 2/2/2008
12/02/2006 Replen 1/8/2007
11/01/2005 Replen 1/9/2007
10/9/2004 NF Bleedoff 1/6/2007

So for the data as shown above, all the 3 rows in tblSubFlowforecast should get update LatestReplenDate as 2/2/2008(max of pick).

I am getting it, but I'm not sure if it's possible, because you have the tables joined on one of the fields.

What you might try is querying the max date with type = "replen" from the pick table, and storing it in a query, just to get it out of the way. Do that with this:

SELECT Max([pick]) AS [Max Replen Date] 

   FROM tblPickCalendar

      WHERE [type] = "Replen";

After you do this, I would assume you could reference that value in an UPDATE query, like this:

UPDATE tblSubFlowForecast SET 
   tblSubFlowForecast.LatestReplenDate = DateQuery.[Max Replen Date]

WHERE tblSubFlowForecast.delivery IN 
   (SELECT tblPickCalendar.delivery FROM tblPickCalendar);

Although I'm not 100% sure about this, it does make sense to do it this way. Honestly, I don't think you can do it with one query, because you are mixing an aggregate function with a subquery. I have written a FAQ on using aggregates, but it does not highlight what you are trying to do here. The only thing I am not certain of here is weather or not you can reference an aggregate value in a SELECT query. I know you can do it with other values; I guess you'll just have to try it.

If you can't, you might have to pull the MAX date out another way, maybe even with some code, and store it in on a form, and do the updating that way. Hopefully you're not doing this stuff within the datasheets anyway, as that's not very practical!

Personally, I would use some code to do something like this, because I do not really like the query functions that Access provides. But, the aggregate query method is still worth a try I guess...

(I apologize for the link I gave you. The SQL there will only work if you have two unrelated tables.)