Restructuring imported data

[skigal]

I have data that I imported into a table from our main accounting system.
The file gives me line data as follows

location, item, year

and column data of Qty Month 1, Qty Month 2, Qty Month 3......

I want to move the year designation up with the month so that I have line
dat for location and item and column data for QtyMonth1YearX,
QtyMonth2YearX,... QtyMonth11YearZ,QtyMonth12YearZ.....

I created a Union Query to try to move the Month and year data points
together but now I have quantity in one column and month/year in the next so
I still don't have my month year as a column heading.

Can anyone assist in writing a query that will fix the data so the format is
one I can work with? I would like the MonthYear field to be in a format I
can do math on - like enter current MonthYear and pull data for that month
plus the previous 24 month.

 

[KARL DEWEY]

Your narrative is not clear to me as to how the raw data looks like.
Is this how it looks?
New York 23 1 43 2 56 3 62 4 75 5 43 6 .... 61 12
Squash
2007
Baltimore 31 1 36 2 69 3 28 4 55 5 39 6 .... 57 12
Spinach
2008

 

[skigal]

Sorry about that.
March April May June.....
squash NY 2007 5 4 7 10
squash MD 2007 1 3 9 15
spinach MD 2007 2 5 6 1
spinach NY 2007 10 15 21 0
squash NY 2008 4 6 2 9
squash MD 2008 8 1 0 8

and what I would like is:

March 2007 April2007 May2007 ...March 2008 April
2008
squash NY 5 4 7 4
6
squash MD 1 3 9 8
1
spinach MD 2 5 6
spinach NY 10 15 21

 

[KARL DEWEY]

Use this union query ---
SELECT skigal.location, skigal.item, dateserial(skigal.year, 3, 1) AS
TransDate, skigal.March AS QTY
FROM skigal
UNION ALL SELECT skigal.location, skigal.item, dateserial(skigal.year, 4, 1)
AS TransDate, skigal.april AS QTY
FROM skigal
UNION ALL SELECT skigal.location, skigal.item, dateserial(skigal.year, 5, 1)
AS TransDate,skigal.may AS QTY
FROM skigal
UNION ALL SELECT skigal.location, skigal.item, dateserial(skigal.year, 6, 1)
AS TransDate, skigal.june AS QTY
FROM skigal
UNION ALL SELECT skigal.location, skigal.item, dateserial(skigal.year, 7, 1)
AS TransDate, skigal.july AS QTY
FROM skigal;

Then use this crosstab --
TRANSFORM Sum(skigal_1.QTY) AS SumOfQTY
SELECT skigal_1.location, skigal_1.item
FROM skigal_1
GROUP BY skigal_1.location, skigal_1.item
PIVOT Format([TransDate],"mmmm yyyy") IN("March 2007", "April 2007", "May
2007", "June 2007", "Ju;y 2007");