SELECT Records based on user input

[Mattrob]

Hi All



I have a set of data which contains fields 1 through 12 for months of the year and one field for the year with one row of data for each year and each customer. I am trying to produce a query to present 18 months worth of data per customer based on an input of current month and current year. EG Month 0 of the resulting data would be the current month, Month-1 would be last month and month+1 would be next from last year. I am thinking I just need to create 18 separate select queries and then a union query to bring the results together. Does anyone have any suggestions on an easier way?

Terr	CustName	Year	1	2	3	4	5	6	7	8	9	10	11	12
SIS	CUSTOMER 1	2011	0	0	55.8	0	0	0	0	0	0	0	55.8	0
SIS	CUSTOMER 1	2012	0	0	0	0	90	0	0	0	0	0	0	0
SIS	CUSTOMER 1	2013	0	0	0	0	0	1170	0	0	0	0	-1170	0
SIS	Customer 2	2011	0	0	300	0	0	0	0	0	0	0	0	0
SIS	Customer 2	2012	0	0	0	14	0	0	126	0	0	0	0	0
SIS	Customer 2	2013	0	0	0	0	0	0	0	0	0	0	450	0

[ssanfu]

You should normalize your table. The way you have it set up now is known as "Committing Spreadsheet".

Your table structure should look like:

Fields
--------------------------
Terr Text
CustName
DataYear (Year is a reserved word)
DataMonth
Reading

Terr	CustName	DataYear	DataMonth	Reading
SIS	CUSTOMER 1	2011	1	0
SIS	CUSTOMER 1	2011	2	0
SIS	CUSTOMER 1	2011	3	55.8
SIS	CUSTOMER 1	2011	4	0
SIS	CUSTOMER 1	2011	5	0
SIS	CUSTOMER 1	2011	6	0
SIS	CUSTOMER 1	2011	7	0
SIS	CUSTOMER 1	2011	8	0
SIS	CUSTOMER 1	2011	9	0
SIS	CUSTOMER 1	2011	10	0
SIS	CUSTOMER 1	2011	11	55.8
SIS	CUSTOMER 1	2011	12	0
SIS	CUSTOMER 1	2012	1	0
SIS	CUSTOMER 1	2012	2	0
SIS	CUSTOMER 1	2012	3	0
SIS	CUSTOMER 1	2012	4	0
SIS	CUSTOMER 1	2012	5	90
SIS	CUSTOMER 1	2012	6	0
SIS	CUSTOMER 1	2012	7	0
SIS	CUSTOMER 1	2012	8	0
SIS	CUSTOMER 1	2012	9	0
SIS	CUSTOMER 1	2012	10	0
SIS	CUSTOMER 1	2012	11	0
SIS	CUSTOMER 1	2012	12	0
SIS	CUSTOMER 2	2011	1	0
SIS	CUSTOMER 2	2011	2	0
SIS	CUSTOMER 2	2011	3	300
SIS	CUSTOMER 2	2011	4	0
SIS	CUSTOMER 2	2011	5	0
SIS	CUSTOMER 2	2011	6	0
SIS	CUSTOMER 2	2011	7	0
SIS	CUSTOMER 2	2011	8	0
SIS	CUSTOMER 2	2011	9	0
SIS	CUSTOMER 2	2011	10	0
SIS	CUSTOMER 2	2011	11	0
SIS	CUSTOMER 2	2011	12	0