Update fields to max count value 1

[jw45]

I need to count the number of records in a sub-group then update all the records to the value of the max count.

In the example data below I need to change all of the values of group ‘4138’ to ‘LVS’ , since the count is LVS – 3 and LVL – 1.

Now for the tie breaker – If there is a tie then I want the max count of ‘Item’ that ends in ‘90’ Example below for 4141, HK – 1 and HJ – 1 then change to both to HK. If there is still a tie then I don’t care which one it uses.

Cor_Num Cor_Job Item CAT$GROUP
4138 28649 97190 LVS
4138 29751 97191 LVS
4138 29752 60630 LVL
4138 29753 80038 LVS
4139 29242 99090 RADIAL
4139 29612 93030 RADIAL
4139 29613 60031 RADIAL
4140 28440 12590 CB500
4140 29599 12530 CB500
4140 29600 12531 CB500
4141 24563 20090 HK
4141 29605 20041 HJ


Thanks,
JW

 

[lespaul]

Are you updating to this table? what field do you want to update? You list 4 fields all with data?

What does

since the count is LVS - 3 and LVL - 1

mean?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jw45]

Sorry..I want to change the values of CAT$GROUP to the value of the max count.... so I would want to make all 4 of the values for CAT$GROUP for '4138' be 'LVS' since it has the higher count.

Thanks!

 

[PHV]

Create a query named qryCountValue:
SELECT Cor_Num, [CAT$GROUP], Count(*)-Sum([Item] Like '*90') AS CountValue
FROM tblGroup
GROUP BY Cor_Num, [CAT$GROUP]
ORDER BY 1, 3 DESC;

And now your update query:
UPDATE tblGroup
SET [CAT$GROUP] = DLookUp("[CAT$GROUP]","qryCountValue","Cor_Num='" & [Cor_Num] & "'")

If Cor_Num is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw45]

Thanks!