T
Tony Maddox
Hi
I have a table which has 6 fields:SpecID, GroupID, then Value 1, Value 2,
Value 3 and Value 4. Each specimen can have one or more associated groups
and each group has the same four variables (Value) which are numbers in this
case. So, the table looks like this:
SpecID GroupID Value1 Value2 Value3 Value4
1 Group1 a b c d
1 Group3 e f g h
2 Group1 i j k l
2 Group2 m n o p
2 Group4 q r s t
etc.
The PK is the first two fields.
I want to write a query which produces this:
SpecID G1.V1 G1.V2 G1.V3 G1.V4 G2.V1 G2.V2 G2.V3
G2.V4 G3.V1 etc
1 a b c d
e etc
2 i j k l m
n o p etc
There are 10 possible groups. Some of the values are nulls but I could
convert them to zeroes without any major problem.
If there was only one value column, I could see how to do a crosstab, but I
don't know how to do it for this sort of case.
Thanks in advance.
Tony
I have a table which has 6 fields:SpecID, GroupID, then Value 1, Value 2,
Value 3 and Value 4. Each specimen can have one or more associated groups
and each group has the same four variables (Value) which are numbers in this
case. So, the table looks like this:
SpecID GroupID Value1 Value2 Value3 Value4
1 Group1 a b c d
1 Group3 e f g h
2 Group1 i j k l
2 Group2 m n o p
2 Group4 q r s t
etc.
The PK is the first two fields.
I want to write a query which produces this:
SpecID G1.V1 G1.V2 G1.V3 G1.V4 G2.V1 G2.V2 G2.V3
G2.V4 G3.V1 etc
1 a b c d
e etc
2 i j k l m
n o p etc
There are 10 possible groups. Some of the values are nulls but I could
convert them to zeroes without any major problem.
If there was only one value column, I could see how to do a crosstab, but I
don't know how to do it for this sort of case.
Thanks in advance.
Tony