update query with aggregate functions

[bob_kelly]

I can run an aggregate function to count the number of things in a group.
I want to update each of the members of the group if say the group has


more than say 5 members. I can get the aggregate function to work. The update
query works. How do I combine the two?

[orange]

Suggest you show us the sql of the queries.
Welcome to the forum.

[bob_kelly]

SELECT Count(finalsalestable.productcode) AS CountOfproductcode, finalsalestable.productcode, finalsalestable.instock, finalinventorytable.styleName
FROM finalinventorytable INNER JOIN finalsalestable ON finalinventorytable.productcode = finalsalestable.productcode
GROUP BY finalsalestable.productcode, finalsalestable.instock, finalinventorytable.styleName
HAVING (((Count(finalsalestable.productcode))>4) AND ((finalsalestable.instock)=Yes))
ORDER BY Count(finalsalestable.productcode) DESC;

UPDATE finalsalestable SET finalsalestable.lastchecked = #12/13/2016#
WHERE (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="ww847gy2")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="122404")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="d5723f1020")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="mx624wn2")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="ww577wt")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="122401")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="ww847wt2")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="d3852l6202")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="d6627l1001")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="mw847wt2")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="0001")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="122406")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="804-4900")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="d6698l1001")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="14996001")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="229911")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="c5706l1250")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="celina_brown")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="d6491l2400")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="e0595l1200")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="e0665l1001")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="e3052f3022")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="mw1300br")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="mw577bk")) OR (((finalsalestable.instock)=Yes) AND ((finalsalestable.productcode)="mw847gy2"));

[John_G]

A query that contains aggregate functions is not updatable.

However, a quick test showed that if you use a dlookup() to the query with the aggregate function, then the query is updatable.

For example, I created a query with a calculated field that looks like this:

Test: DLookUp("Countofmosid","Q1","MOSID = " & [MOS_ID])

where Q1 is a simple totals query using Count, and [MOS_ID] is a field in the select query to use as criteria.

When I run the Select query, it is updatable, because it does not contain an aggregate function.

[bob_kelly]

Thank you I will try that probably tomorrow. You can see by looking at my update
query why I would be interested in being able to do this- too much work
typing all that in-

[orange]

The SQL IN operator may be of interest to you.
Also, you can put your desired productcode into a separate table, and use that table with your UPDATE query.
See this link for an example --it excludes records but you can adjust accordingly.