Rearrange information in a table

[Cecilia]

Hi,

Hopefully someone knows this: I have a table in Access that looks like this:



species A
species B species C .. species X
ID1 Y-coordinate1 X-coordinate 1 1 0
1 ID2 Y-coordinate2 X-coordinate 2 1
1 0
ID3 Y-coordinate3 X-coordinate 3 0 0
1

and I want it to look like this:

ID1 Y-coordinate1 X-coordinate1 species A 1
ID1 Y-coordinate1 X-coordinate1 species C 1
ID2 Y-coordinate2 X-coordinate2 species A 1
ID2 Y-coordinate2 X-coordinate2 species B 1
ID3 Y-coordinate3 X-coordinate3 species C 1

So what I want is to rearrange the information in the first table so it
looks like the second example. There will be several rows with information
for ID1 (as many row as species with value=1, value=0 should be excluded). I
want to have all species information in one column not in several as in the
first example.

Thanks!

 

[Cecilia]

The first table looked odd, I try again.

Table 1
Sp 1
Sp 2 Sp 3... Sp X
ID1 Y-coordinate1 X-coordinate1 1 0 1
ID2 Y-coordinate2 X-coordinate2 1 1 0
ID3 Y-coordinate3 X-coordinate3 0 0 1

Table 2

ID1 Y-coordinate1 X-coordinate1 species A 1
ID1 Y-coordinate1 X-coordinate1 species C 1
ID2 Y-coordinate2 X-coordinate2 species A 1
ID2 Y-coordinate2 X-coordinate2 species B 1
ID3 Y-coordinate3 X-coordinate3 species C 1

 

[John Spencer]

You can use a UNION query to normalize the data as long as there are not
TOO many columns involved. If you have too many species columns you
will need to write multiple INSERT queries.

Union query would look like:

SELECT ID, [Y-Coordinate], [X-Coordinate], "A", [Sp 1]
FROM YourTable
WHERE [Sp 1] <> 0
UNION ALL
SELECT ID, [Y-Coordinate], [X-Coordinate], "B", [Sp 2]
FROM YourTable
WHERE [Sp 2] <> 0
UNION ALL
SELECT ID, [Y-Coordinate], [X-Coordinate], "C", [Sp 3]
FROM YourTable
WHERE [Sp 3] <> 0
....
UNION ALL
SELECT ID, [Y-Coordinate], [X-Coordinate], "X", [Sp X]
FROM YourTable
WHERE [Sp X] <> 0

Your insert query would look like the following. This assumes you have
built the destination table (for example named SpeciesFixed)

INSERT INTO SpeciesFixed (ID, X, Y, Species, TheValue)
SELECT ID, [Y-Coordinate], [X-Coordinate], "A", [Sp 1]
FROM Table1
WHERE [Sp 1] <> 0

You would need to repeat the query for each set of species from SP 1 to Sp X

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================