Updating a field based on a count from another table

[marculos]

Hi, I have been struggling with the problem all day ..

I have
table a


field - patient a
field - patient ref
field - number of heath issues

table b
field - patient ref
field - heath issue X

So I want to get the count of health issues for patient a from table b to produce something like below.

patient a, refa12345, 5
patient b, refb12345, 2
patient c, refc12345, 8


what is the best approach to take with ms access to do this ? I have tried an UPDATE query but doesnt seem to want to work as can't use joins.
Appreciate any help here.

[ranman256]

Easiest way, make 2 queries
Q1 make table that counts the health issues for each patient. write to a table T1.
Q2 is an update query that uses the table made from Q1 and TableA together....
UPDATE tableA INNER JOIN T1 ON tableA.ClientID = T1.ClientID SET tableA.CountOfIssues= [T1].[CountOfIssues];

(you have to make table because you cant sum and update in the same query)

[June7]

Usually a bad idea to save aggregate data. Calculate when needed.

[marculos]

Thanks for all the help peeps !!