Two columns as one

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hotplate74@gmail.com - 11 Aug 2006 01:09 GMT

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

Reply to this Message

Allen Browne - 11 Aug 2006 05:16 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 15 lines]
>
> James

Reply to this Message