ado sql update table with result of group by query

Question

I am trying to update records in an .mdb table with the number of records containing the same value

The sql below does not work but I think gives an indication of what I am trying to achieve.

UPDATE table1 AS A
INNER JOIN (SELECT PH_BSP , Count(PH_BSP) AS PHCOUNT  FROM table1 GROUP BY PH_BSP)  AS B
ON A.PH_BSP=B.PH_BSP
SET A.PH_SORT = B.PHCOUNT;

any ideas?

Answer 1

Have you tried something alike?

update table1 as a
    set a.ph_sort = (
        select COUNT(b.ph_bsp) as phcount
            from table1 b
            where b.ph_bsp = a.ph_bsp)

I'm assuming SQL Server here.

But this or something alike should do it, I guess.

Answer 2

Untested, but setting out the statement thusly this should solve your issue

UPDATE A
SET A.PH_SORT = B.PHCOUNT
From table1 AS A
INNER JOIN (SELECT PH_BSP , Count(PH_BSP) AS PHCOUNT  FROM table1 GROUP BY PH_BSP)  AS B
ON A.PH_BSP=B.PH_BSP

Edit: Your problem might be from your sub query, I would try putting that part into a separate Access Query and see how it goes. From memory I used to have a lot of trouble with Access and subqueries, square brackets would also sometimes help, but unreliable from memory.

Answer 3

If you are doing this in Access, you need to use a domain aggregate function:

UPDATE table1 
SET PH_SORT = DCount("PH_BSP","Table1","PH_BSP='" & PH_BSP & "'")

The above assumes that PH_BSP is a text field, drop the single quotes if it is numeric.