Count multiple categories for Reports

[nlsmith326]

Greetings,

I have a table set up w/ the following fields (excerpt):
PartType1
PartType2
PartType3
PartType4
PartQuantity1
PartQuantity2
PartQuantity3
PartQuantity4

For every specific part that is entered, there must be a quantity.

On my report I would like to total the quantities of parts entered by the individual part name.

I'm having huge difficulty figuring this one out. I'm debating whether nor not I should change my table & the entry form...

Any ideas would be greatly appreciated....

Thanks,

Natalie

[Scott Price]

I think your idea to change the table design is correct :-)

Generally speaking you should have a Parts table (lets call it tblParts using Hungarian notation), it will have PartsID, PartName, PartNumber, PartQuantity, PartTypeID, etc... fields. Then you will establish a Many to One link between it (on the many side) and your PartType table (tblPartType). This will have fields: PartTypeID, PartTypeName, etc...

Now when you enter a part, it will be related to it's type by the Foreign key field PartTypeID contained in the tblParts.

Understanding?

Also have a look at Normalisation and Table structures for further information on the design of database tables.

Regards,
Scott