Count then Update recordset

[Wes1961]

I created a table that has the data similar to below. I need to count the field “group” and replace/update the group to that value of the one that has the highest count.

Table
Num Desc Group
1 xxxx A
1 yyyy A
1 zzzz B
1 xxxx A
1 yyyy B
2 abcd D
2 bcde D
2 zzzz A
etc…..

In the first group I would like to set all Num = 1 to A, Num = 2 to D.

The source data has about 50 different groups, so each time I build the table the number of groups (A,B,D) can vary and change. Bottom line is I want all values of group in a number subset to be the same and equal to the highest count of the subset. If they are equal I do not care which one it picks.

Help?

 

[ByteMyzer]

Assuming a table named Table1, with the fields and data as outlined above, create a query with the following SQL statement:

[tt]
SELECT DISTINCT T2.Num, T2.Group
FROM Table1 AS T2
WHERE T2.Group=(SELECT TOP 1 T3.Group FROM Table1 AS T3 WHERE T3.Num=T2.Num GROUP BY T3.Group ORDER BY Count(T3.Group) DESC;
[/tt]

Save this query as Query1. Now, to perform the update you describe, execute the following SQL statement:

[tt]
UPDATE Table1 SET Table1.[Group] = DLookUp("[Group]","Query1","[Num]=" & [Num]);
[/tt]