Displaying the most frequently occuring value

[Guest]

I have a query that is grouping by a process ID. This ID has several
different names associated with it due to entry errors, etc. I would like to
group by the ID and keep the most frequently occuring name. For example:

Table:
ID Name
1 A
1 B
1 B
1 C

I would like the query to return an ID of 1 and Name of B. Can you help me
out?

 

[Allen Browne]

1. Create a query using this table.

2. Click the Totals icon on the toolbar (upper sigma icon.)
Access adds a Total row to the design grid.

3. Add the ID field to the grid.
Accept Group By in the Total row.

4. Add the Name field to the grid.
In the Total row, choose Count.
In the Sorting row, choose Descending.

5. Open the Properties box (View menu.)
Set the Top Values property to: 1

If you just want the "B" and not the count, uncheck the Show box under the
Name field.

BTW, if you really have a field named Name, it will cause you grief. For
example, when you refer the Name field on a form, Access is likely to
understand it as the Name of the form: since forms have a Name property, the
reference is ambiguous. For a list of the names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Guest]

This didn't work and maybe I need to be a little more descriptive. Here is a
better example of what my data looks like:

Operation ID Description Labor
A1 Cut 2
A1 Cut 3
A1 Roll 4
B1 Mix 1
B1 Inspect 2
B1 Mix 3

I want the query to disply the following:
Operation ID Description Labor
A1 Cut 9
B1 Mix 6

I want it to sum up all of the labor grouping by the operation ID. Also, I
want to only display the most frequently occuring descprition because the
others are incorrect descriptions. Is this even possible?

 

[Allen Browne]

Create a new table with just one record for each valid value of the
OperationID, and move the Description into that table. Create a relationship
between the OperationID in your existing table and the one in the new
table.

This guarantees the integrity of your data, i.e.:
a) invalid OperationID values cannot get entered into your existing table,
and
b) the Description does not have to be entered every time in your existing
table, and therefore
c) the Description cannot be wrong.

You can now create a Totals query, based on both tables. Group by the
OperationID and Description, and sum the Labor.

The previous reply gave the steps for creating a Totals query.