"Horizontal" output from many-to-many tables

[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

 

[Dale Fye]

Tony,

Since your basic table is not normalized, that should be your first step. I
would create a table that is setup something like:

SpecID GroupID ValCol Value
1 1 1 a
1 1 2 b
1 1 3 c
....
2 4 4 t

If you cannot change your table structure for some reason, you can create a
Union query (qry_Normalize) to normalize the data, as shown below.

SELECT SpecID, GroupID, 1 as ValCol, Value1 as Value
From YourTable
UNION
SELECT SpecID, GroupID, 2, as ValCol, Value2 as Value
FROM yourTable
UNION
SELECT SpecID, GroupID, 3 as ValCol, Value3 as Value
FROM yourTable
UNION
SELECT SpecID, GroupID, 4 as ValCol, Value4 as Value
From yourTable

Once you have created and saved this table(or query), you can create a
crosstab query that is based on that first query to get what you want.

It will look something like(assuming you use the query method):

TRANSFORM First(qry_Normalize.Value) AS FirstOfValue
SELECT qry_Normalize.SpecID
FROM qry_Normalize
GROUP BY qry_Normalize.SpecID
PIVOT "G" & [GroupID] & Chr$(46) & "V" & [ValCol];

The only problem with this is that when I use chr$(46), it replaces the
period "." that I should get with an underscore to separate the G1_V1.

HTH
Dale

 

[Julia]

Since your basic table is not normalized, that should be your first step.

Thanks, Dale. I'm sure that will work. I thought I had kind of sorted out
normalisation but this table obviously slipped through the net. Thanks again

Tony