Create query with table columns as rows

[Nan Hauser]

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.

 

[akphidelt]

Look up Union Queries

It would be something like

Select Grade, ResourceCode, Name, 2008 As [Year]

From tblYourTable

Union All Select

Grade, ResourceCode, Name, 2009

From tblYourTable

Union All Select

Grade, ResourceCode, Name 2010

From tblYourTable;

 

[John W. Vinson]

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.

 

[Nan Hauser]

Thanks so much for the help. This works great.

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.

 

[ANDY-N via AccessMonster.com]

Hi John,

I'm trying to do the same thing but I could not get the "RATE YEAR" to carry
through.

I have the following table:
Grade ResourceCode Name 2008 2009 20010

[quoted text clipped - 9 lines]

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.

 

[ANDY-N via AccessMonster.com]

Changing (2008) AS RATEYEAR into "2008" AS RATEYEAR solved my problem. =]

Hi John,

I'm trying to do the same thing but I could not get the "RATE YEAR" to carry
through.
[quoted text clipped - 27 lines]

through. Note that Year is a reserved word and should not be used as a
fieldname, hence my suggestion RateYear.