Rolling 12 Month Query

[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. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?

Is there a better way to build this database? I thought about just have 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.

 

[maw230]

Is there a better way to build this database?

After reading your first paragraph I think the answer to this is yes. It sounds like you're in need of a total redesign, but it ought not to be too complicated in your case. I'm no expert on database normalization, but there are plenty here who are.

I would post over in the "Theory and practice of database design " forum and explain your current structure and ask for help. This will end up making things like a 12 month rolling query much, much easier.

 

[Frothingslosh]

Your original, 4-field table was the way to go, although I'd add a primary key of some kind as a fifth column (something like OrderID - which is not necessarily the same as an order number).

The rule with Access is you fill DOWN, not ACROSS. Setting it up to work like Excel just makes life more and more difficult.

Once you've changed everything to the part number/order month/order year/order amount setup, it's pretty easy to set up a crosstab query that pulls up the orders for the previous twelve months.