4

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

I have two tables:

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

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

I now need to update the field "count" in Table1 with the number of matching entries in table 2. these two tables need to be connected via "customer" 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:

Operation must be an updateable query.

I was searching this side and the web and it was proposed to use 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 give back all entries which are existing in Table2. So if I have 100 entries in Table2 the DCount value = 100 and not the amount of matching entries for a specific entry of Table1 (where customer and product are the same).

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

Newd
  • 2,174
  • 2
  • 17
  • 31
Thorsten
  • 43
  • 1
  • 4
  • possible duplicate of [Operation must use an updatable query. (Error 3073) Microsoft Access](http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access) – Bulat Jul 29 '15 at 11:47

1 Answers1

3

Create a temp table with counts:

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

Update join of new table with Table1:

UPDATE 
    Table1 t JOIN 
    CustomerCounts cc ON cc.customer = t.customer
       AND cc.product = t.product
SET t.count = cc.count
Bulat
  • 6,869
  • 1
  • 29
  • 52
  • Hello. Thanks for the answer. I have tried to do so and defined a table with a select query (temp table) and to use this table in the update query via join. The problem is that when the temp. table qry is executed I get the error message "You tried to execute a query that does not include the specified expression 'customer' as part of an aggregate function". If I leave everything out instead of the COUNT(..) statement this query works but as soon As I include the fields customer and product I get the error message. – Thorsten Jul 29 '15 at 10:49
  • Sorry, I forgot to include group by, updated the answer – Bulat Jul 29 '15 at 10:55
  • Thanks. Have noticed that myself during writing my comment. I now have the error that I can't use the temp table in the update query as access is giving the error message "Operation must be a updateable query". Any solution for that? – Thorsten Jul 29 '15 at 11:30
  • This occurs when there is not a UNIQUE MS-ACCESS key for the table(s) being updated. (Regardless of the SQL schema). see this - http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access – Bulat Jul 29 '15 at 11:49
  • At the end I used your code for a temp table but made a real table out of it, filled dynamically. After it is a real table there are no problems with the "Operation must be a updateable query" so I am now able to select the data and join it with a different table. – Thorsten Jul 30 '15 at 06:08