two identical items show once with a qty of 2

[Chummly66]

Was trying to see if I could do this and if it would be very dificult or not.

I have a form which allows me to enter up to 6 parts for one customer record. on the form itself, they are partdesc1, partdesc2, pqartdesc3... partdesc6. Occasionally, some of the partdesc fields may have the identical part. (each partdesc field is a drop list where you select a part, and the drop list is identical for all 6 partdesc fields).

currently, on a report (which is set up like a work order), I have it printing each partdesc field, so even if two of the fields are the same identical part, it lists each one separate.

I was wondering if by comparing the 6 partdesc fields, the program can determine if any are identical, and if so, count the number and print the report showing:

Qty Part Desc Part #
2 Antenna 123-443
1 Lens 123-321

instead of:

Qty Part Desc Part #
1 Antenna 123-443
1 Antenna 123-443
1 Lens 123-321

Thanks in advance for the help!

Oh, and just an fyi, the QTY of 1 that currently shows, is just a label I placed onto the report.
Richard

 

[dhookom]

If you truly have fields like partdesc1,.... you should consider normalizing your tables prior to going any further. If you can't change your table structure, use a union query to normalize it for your report:

SELECT ID, PartDesc1 as PartDesc, 1 as Qty
FROM tblSpreadsheet
UNION ALL
SELECT ID, PartDesc2, 1
FROM tblSpreadsheet
WHERE PartDesc2 is not Null
UNION ALL
SELECT ID, PartDesc3, 1
FROM tblSpreadsheet
WHERE PartDesc3 is not Null
UNION ALL
SELECT ID, PartDesc4, 1
FROM tblSpreadsheet
WHERE PartDesc4 is not Null
... etc ...;
You can then create a totals query based on your union query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Chummly66]

Thanks fo the reply and suggestions!!
Richard