If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
rework a table so that old columns are new values
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 |
#2
|
|||
|
|||
rework a table so that old columns are new values
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 -- KARL DEWEY Build a little - Test a little "lschibley" wrote: 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 |
#3
|
|||
|
|||
rework a table so that old columns are new values
It worked! Thank you so much!
"KARL DEWEY" wrote: 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 -- KARL DEWEY Build a little - Test a little "lschibley" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|