Query count question

[knulisch]

I have two tables linked by an id field. I would like to create a query that updates a field in the first table when the count of items in the second table (for that id) is > 25. Can someone help me out with this syntax? There is a one to many relationship between the first table and second table.

Thanks

 

[Eradic8or]

Use an update query on the second table. You can use the count function by going to View|Totals and in the criteria, selct >25. You can then tell it what to update.

Hope this helps.
Eradic8or

 

[raskew]

Here's an example using Northwind tables. Create a query using tables Products and Order details. This SQL would return ProductName, Discontinued and Quantity where quantity >=25.

SELECT Products.Discontinued
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
WHERE ((([Order Details].Quantity)>=25));


Now, switching from a Select to an Update query, this SQL updates the Discontinued field when quantity >= 25 (doesn't actually make any change since I set it to set the field to its current value, but this should show you the technique).

UPDATE Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID SET Products.Discontinued = IIf([discontinued]=True,True,False)
WHERE ((([Order Details].Quantity)>=25));