Keeping a running total of parts listed in different fields

[Shannan Casteel via AccessMonster.com]

I have a form for inputting part numbers. The form has 25 fields for listing
up to 25 parts. It also has a quantity field associated with each part
number. If on one record I enterend part number 1234 and a quatity of 2
along with part number 1235 and a quantity of 7, and on another record I
entered part number 1234 and a quantity of 11 and part number 1236 and a
quantity of 1, how would I get a report of how many times part number 1234,
1235, and 1236, and any other part numbers that have been entered, have
occurred?

I need it to sum the occurences of these part numbers. In other words it
needs to spit something out like this:

Part Number Number of Occurences
1234 13
1235 7
1236 1

Any help would be greatly appreciated.

 

[Rick B]

Your structure is badly flawed. Putting a part number in any of 25 fields
is not proper database design. You are trying to build a spreadsheet using
a database application.

Instead, you should have a one-to-many relationship. This would involve
adding a new table for the details (part number and quantity) and including
that data as a subform in your main form.

For example, in the Northwind database you can sell one or many products on
an invoice.

Fix your structure.