Access: update query with calculation in subquery - error or all results

Question

I know that you should not store the calculated values ​​in the database, but in this case the structure is given, and I have to deal with it.

I have two tables:

Table1 with fields (i.e. customer, product, price, count )

Table2 with fields (i.e. customer, product, description )

Now I need to update the "count" field in table 1 with the number of corresponding entries in table 2. These two tables must be connected through the "client" and "product".

My idea was something like this:

 UPDATE Table1 SET Table1.count = (SELECT COUNT(Table2.customer) FROM Table2 WHERE Table2.customer = Table1.customer AND Table2.product = Table1.product) WHERE Table1.count IS NULL 

But this gives an error:

The operation must be an updatable request.

I was looking for this side and network, and it was suggested to use the DCount function, so I rewrote my code to do this:

 UPDATE Table1 SET Tabl1.count = DCount( "*", "Table2", "Table2.product = "& Table1.product AND "Table2.customer = "& Table1.customer) WHERE Table1.count IS NULL 

This, unfortunately, always returns all records existing in Table2 . Therefore, if I have 100 records in Table2 value is DCount = 100, and not the number of matching records for a particular record of Table1 (where the client and product are the same).

Can someone point out what I am missing in this statement so that I can update the "count" column with the number of matching records from Table2 .

Answer 1

Create a temporary table with accounts:

 SELECT customer, product, COUNT(customer) as count INTO CustomerCounts FROM Table2 GROUP BY customer, product 

Refresh the connection of the new table with table 1:

 UPDATE Table1 t JOIN CustomerCounts cc ON cc.customer = t.customer AND cc.product = t.product SET t.count = cc.count