Sum from multiple columns

[ScribeGuy]

I have a table of Cabinets.
Each cabinet has 20 fields for Hardware (HW1, HW2 etc)
and 20 for Hardware Quantity (HW1Qty, HW2Qty etc)
Not all fields are populated.

Hardware is entered as an ID number
eg "Hinge" = 620 and "Handle" = 750

So say there are 3 Cabinets in a Job Order

Cab1: [HW1] = 620, [HW1Qty] = 2
Cab2: [HW4] = 620, [HW4Qty] = 4
Cab3: [HW1] =750, [HW1Qty] = 2 AND [HW3]=750, [HW3Qty]=1

Each Job Order (list of cabs) is different.

I need to total all the different hardware items, no matter which column (HW1, HW2 etc) they might be in.

So I would end up with totals like :

620 (Hinges) = 7
750 (Handles) = 1
for each Job Order

Any help appreciated, cheers

 

[Galaxiom]

The problem has arisen due to the inappropriate structure of your data. Search for information about "Normalization". Once you reconstruct your tables using relational principles you won't have the problem.

 

[plog]

Here's the correct help: structure your tables properly. You need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and fix the errors with your tables. Once you do that, this issue resolves itself and the sum you want becomes trivial to calculate.

Numerated field names (HW1, HW2, HW3...) are nearly always a sign of improper structure. Data should not be contained in field or table names. By adding numbers to your fields you've done that. If that number in the field name is relevant, you should instead create a field to store that information with each record.

Again, check out the link I provided and read up on normalization. You're tables are structured incorrectly.

 

[ScribeGuy]

Thanks for the response. Not sure how I could structure it any differently, perhaps you can help.

Cabinets are in one Table
Hardware in another table

Each cabinet uses different combinations of the items in the Hardware table but no two cabinets use the same combination. Some cabinets might have 5 items, some 10 and some might have none.

What then is the best way to assign the correct hardware to each cabinet so that when I have a list of cabinets I can get a list of hardware?

 

[plog]

Can you provide some sample data from each table? Be sure to include table and field names. Use this format for posting your data:


Table1NameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 12, 4/3/2008
Steve, 8, 5/16/2010
Amy, 2, 6/6/2007

 

[ScribeGuy]

I have attached a sample database. There are 3 tables

Cabinets
Hardware
Orders

This is how I have it structured at the moment. If this is incorrect please let me know.

That said, I need to write a query on the Orders table that will list for me the various hardware items and their quantities for that particular order.

This is a simple example. In reality there are hundreds of cabinets and thousands of possible hardware items.

 

[ScribeGuy]

I see now what you mean about structural problem. I think I need a many-many relationship in my tables. Not too experienced with that but I'll look into it.

 

[Cronk]

To give you a steer, you need another table
tblCabinetContents with the fields
CabinetID, HardwareID, Quantity. I'd suggest you also add a unique Autonumber field, say CabHWareID

That way you can easily sum any one HardwareID item across all Cabinets, or list the contents of any Cabinet, or ....

 

[ScribeGuy]

thanks Cronk, I'm starting to get it now.

 

[ScribeGuy]

Solved. Of course you were all correct. The HW1 etc fields had no business being in that table. As soon as Cronk mentioned the third table it all made sense. I used to know this, in fact I’ve done it before on this same database.

Problem is, like many, I only get ten minutes at a time to work on this because it’s not my actual job, I’m just the guy that “knows computers”. So by the time I get back to it I have to remember how it all works again.

For others with this problem I also searched on many-to-many relationships which was equally helpful.