Two columns as one

[hotplate74]

I have some records where one column is a part, the other is the
problem with the part. One part can have different problems, but all
the parts are defective. I need to do a query of the top ten defects.
Could I have some advice on how to query the top ten defects?
Examples would be--

Part: Bracket Problem: scratched
Part: Bracket Problem: dented
Part: Cover Problem: light Paint
Part: Tire Problem: Flat
Part: Bracket Problem: scratched

The query would come back:
2 Brackets scratched
1 Bracket dented
1 Cover light paint
1 Tire flat

James

 

[Allen Browne]

1. Create a query using this table.

2. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.

3. Drag the Part and Problem fields into the grid.
Accept "Group By" in the Total row under these fields.

4. Drag the primary key field into the grid (or one of the others again.)
In the Total row under this field, choose Count.

The query will give you 3 columns with the data you wanted.

If you want the 3 in one, try typing this into the Field row:
Combined: Trim([CountOfID] & " " & [Part] & " " & [Problem])
In the Total row, choose Expression.

If you counted the Part field instead of a field named ID, you will need to
use CountOfPart instead of CountOfID.