rework a table so that old columns are new values

[lschibley]

Hi all,

I am a access beginner. I am using it to organize some data and have a
question. I have a table (1700 records) with 6 fields to hold census data
for sites in my program. This format (I'm giving an example) comes from a
legacy system.

Sites Species Fall Winter Spring Summer
Riversite YellowBird 124 342 567 345
Treesite ShyRedbird 345 456 896 345
Treesite YellowBird 567 346 890 432
Pondsite Bigbluebird 321 654 876 987

I want to rework this table to a more logical (and I believe normalized)
format, namely:

SiteName Species Season Population
Treesite ShyRedbird Fall 345
Treesite Yellowbird Winter 346
Pondsite Bigbluebird Spring 876
..
..
..


But I don't know how to make the transformation. I know how to do a cross
tab query, but in a way, this seems to be a reverse cross tab query. If
anyone has any suggestions, I would appreciate it. Thanks!

Lisa

 

[KARL DEWEY]

Use a union query like this --
SELECT Sites AS SiteName, Species AS Species, "Fall" AS Season, Fall AS
Population
FROM YourTable
UNION ALL SELECT Sites AS SiteName, Species AS Species, "Winter" AS Season,
Winter AS Population
FROM YourTable
UNION ALL SELECT Sites AS SiteName, Species AS Species, "Spring" AS Season,
Spring AS Population
FROM YourTable
UNION ALL SELECT Sites AS SiteName, Species AS Species, "Summer" AS Season,
Summer AS Population
FROM YourTable;




Riversite YellowBird 124 342 567 345

SiteName Species Season Population
Treesite ShyRedbird Fall 345

 

[lschibley]

It worked! Thank you so much!

Use a union query like this --
SELECT Sites AS SiteName, Species AS Species, "Fall" AS Season, Fall AS
Population
FROM YourTable
UNION ALL SELECT Sites AS SiteName, Species AS Species, "Winter" AS Season,
Winter AS Population
FROM YourTable
UNION ALL SELECT Sites AS SiteName, Species AS Species, "Spring" AS Season,
Spring AS Population
FROM YourTable
UNION ALL SELECT Sites AS SiteName, Species AS Species, "Summer" AS Season,
Summer AS Population
FROM YourTable;




Riversite YellowBird 124 342 567 345

SiteName Species Season Population
Treesite ShyRedbird Fall 345