I have the following table:
Grade ResourceCode Name 2008 2009 20010
Engr1 Codex Jones $1.11 $2.22 $3.33
Engr4 Codey Smith $1.12 $2.23 $3.34
I'd like to get the data into the following format:
Grade Year Rate
Engr1 2008 $1.11
Engr1 2009 $2.22
etc.
I thought I could use a cross tab query, but I can't figure out how to do
it. Thanks.
Well, you need an "uncrosstab" since your current structure IS a crosstab (or
looks very like the result of one).
A "Normalizing Union Query" is what's needed here. Esentially you will create
three (or more, if there are more than three years) queries extracting each
year's data, and the UNION query operator will stitch them together.
I presume that the Name and ResourceCode values are to be discarded? They can
be kept if needed... but if they're not, create a new Query selecting just
Grade and [2008]. Select View... SQL from the menu, and edit the query to
something like
SELECT [mytable].[Grade], [mytable].[2008] AS Rate, (2008) AS RateYear
FROM mytable WHERE [2008] IS NOT NULL
UNION ALL
SELECT [mytable].[Grade], [mytable].[2009], (2009)
FROM mytable WHERE [2009] IS NOT NULL
UNION ALL
SELECT [mytable].[Grade], [mytable].[2010], (2010)
FROM mytable WHERE [2010] IS NOT NULL;
This will "unravel" the spreadsheet you now have into a more properly
normalized tall-thin table. The fieldnames used in the first SELECT will carry
through. Note that Year is a reserved word and should not be used as a
fieldname, hence my suggestion RateYear.
