Linking Table "Headers" to "Columns"?

[SpikeyA]

Good morning

I am unsure if what I am about to ask is even feasible however it is currently the only way I can think to populate information moving forwards without having to manually update formulas each month to correct the values.

In essence what I am trying to get to is displaying quarterly average over a period of 2 Years historical data.

The SQL Table / Code I am trying to manipulate is detailed as follows:

SELECT [Stock No], [Sales Qty 1 ], [Sales Qty 2 ], [Sales Qty 3 ], [Sales Qty 4 ], [Sales Qty 6 ], [Sales Qty 5 ], [Sales Qty 7 ], [Sales Qty 8 ], [Sales Qty 10 ], [Sales Qty 9 ],
[Sales Qty 11 ], [Sales Qty 12 ], [Sales Qty 13 ], [Sales Qty 14 ], [Sales Qty 15 ], [Sales Qty 16 ], [Sales Qty 17 ], [Sales Qty 18 ], [Sales Qty 19 ], [Sales Qty 20 ],
[Sales Qty 22 ], [Sales Qty 21 ], [Sales Qty 23 ], [Sales Qty 24 ]
FROM dbo.KPIREPORT

I have then generated an excel sheet that auto generates the correct period of dates automatically based on "todays date" as follows. (This has also been dumped into SQL (table name : dbo.'Field Names$'

Field Name Month Quarter
Sales Qty 1 1 Q1
Sales Qty 2 12 Q4
Sales Qty 3 11 Q4
Sales Qty 4 10 Q4
Sales Qty 5 9 Q3
Sales Qty 6 8 Q3
Sales Qty 7 7 Q3
Sales Qty 8 6 Q2
Sales Qty 9 5 Q2
Sales Qty 10 4 Q2
Sales Qty 11 3 Q1
Sales Qty 12 2 Q1
Sales Qty 13 1 Q1
Sales Qty 14 12 Q4
Sales Qty 15 11 Q4
Sales Qty 16 10 Q4
Sales Qty 17 9 Q3
Sales Qty 18 8 Q3
Sales Qty 19 7 Q3
Sales Qty 20 6 Q2
Sales Qty 21 5 Q2
Sales Qty 22 4 Q2
Sales Qty 23 3 Q1
Sales Qty 24 2 Q1

Is there any feasible way to link the headings in dbo.KPIREPORT to the this table so that I can then sum the historical quarters and work out a historical average of those periods?


If you require any further information please let me know.

I am in the process of trying to understand SQL so please go easy on me!

Thanks for your time.

 

[Privateer]

SpikeyA, I don't want to sound like a jerk, but data is not usually stored that way. You are building a table in a database the way you would build a spreadsheet, horizontally, rather than vertically. Your table, dbo.KPIREPORT should look like this: [Stock No], [Sales], [SoldOn], the last one being the all important date field. In a database, you want to throw everything into one column but give yourself a way to find it; you never separate it into columns. In your case the way to find it is dates. You should also consider adding in a SalesPersonID column and a ClientID column so you can do a total by person or client and see who your best and worst are. The bottom line is you want the data in its most basic, rawest form in the table, then the queries become easy and the results accurate. And you almost never put a number in a table without a date next to it. In a spreadsheet you label 12 columns Jan-Dec, in a DB you put the actual date the sale was transacted in a column right next to the amount and you go vertical. If you reorganize your table, try the code below.

Dim strSQL as string
Dim Criteria as string

Criteria = "([KPIREPORT].[SoldOn] Between #" & date & "# AND #" & dateadd("m",-24,date) & "#)"

strSQL = "Select Avg([KPIREPORT].[Sales]) AS Two Year Sales Average from [KPIREPORT] where (" & criteria & ");"

"date" is a reserved work and it is whatever the date is on the computer and the dateadd function is subtracting 24 months from today's date, so that should cover it.
Hope that helps,
Privateer