Update Query to count the number of records

[monkey64]

I have a stock record database which I have 'inherited' from someone far cleverer than me!
I'm fairly used to basic SQL, but I'm teating by hair out over this particular problem.

I'm writing an Update Query to count the number of [NewModelCode] records WHERE Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes and update a field called CountNew on a table called InstStockReference..
So far I have the following SQL:

UPDATE InstrumentStock INNER JOIN InstStockReference ON InstrumentStock.NewModelCode = InstStockReference.NewModelCode 
SET InstStockReference.CountNew = DCount("NewModelCode","InstrumentStock","Status='Available'")
WHERE (((InstrumentStock.Status)="Available") AND ((InstrumentStock.StockType)="New") AND ((InstStockReference.OnlineShop)=Yes));

It partly works, but the result I get is a column count of 939 in every field where the Status ='Available' AND StockType = 'New' AND InstStockReference.OnlineShop = Yes.

I want to do a count the number of [NewModelCode] records.

Something's gone wrong? Any ideas?

 

[Kiwiman]

Your parameter is counting where the status is "Available", I think the where parameter needs to be NewModelCode.

Something like

Dcount("NewModelCode","InstrumentCode","[NewModelCode]=" & [InstStockReference]![NewModelCode])

Take it Easy
Man with one chopstick go hungry