showing only columns that meet criteria

[mjnov85]

Hello,
I am making a database where ppl can input customer orders and other's can then commit to that order, the order number is my primary key for both of the following tables. So i have a table with OPN1, OPN2,...... OPN12. Each one of those fields has a corrosponding quantity1, quantity2,.....quantity12. This table has a 1 to 1 relationship with another table that contains the customers name for each individual order #. What i want to do is this:

Once these are entered into the table via a form ( I have already done this), I want to be able to get a sum of the quantity based on a certain OPN (from all order #'s). So if I choose OPN= X then it should look through that table and find all the quantities that match that OPN1-12 and return the sumed values. I have tried a few things but hte problem is that it tries to match all 12 OPN to X. It doesnt only pick the ones with that OPN. I know there has to be an easy way to do this, i just need a little help. I then want the summed quantities to be displayed according to Customer Name. I just dont know how to do this?

Any help will be greatly appretiated.

Thanks
Mustansir Jeevanjee

 

[Ziggy1]

I think your table structure is probably wrong, you should have a single column for the OPN type and one for OPT qty...this will create more records, but it can be summed using grouping in a query

For what you want to do you need a query for every OPN, you won't be able to SUM OPN1 because OPN2-12 won't allow it.

 

[georgedwilkinson]

Agreed, you're doing more work than you should have to do using a database. Look up normalization (normalisation) and fix your data format first. Then you'll find things like this are easy.