Historical Data By Month

[LadyDi]

I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months.

Is there a better way to build this database? I thought about just having four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.

Any suggestions you can provide would be greatly appreciated.

 

[Fran Lombard]

Your thought about building the table with four columns was the correct one.
Change the table design and your query will be easy.

Generally, you want data in tables to grow vertically not horizontally.

As an aside, where is this summary data coming from? If you have a table with the order details that includes order date you don't even need this summary table.