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
|
|||
|
|||
"Horizontal" output from many-to-many tables
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 |
#2
|
|||
|
|||
"Horizontal" output from many-to-many tables
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 "Tony Maddox" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot Tables Rows | hans | General Discussion | 2 | June 22nd, 2004 11:26 AM |
Draw horizontal line every fifth row on output list? | Too_Much_Coffee ® | Worksheet Functions | 2 | June 17th, 2004 12:54 PM |
Use a make-table query to combine two existing tables | Matt | Running & Setting Up Queries | 1 | June 16th, 2004 01:53 AM |
searching for "join tables" and "join word tables" | Uncle Bill | Tables | 1 | June 11th, 2004 09:33 PM |
How do I design a database based on the information that will be stored? - Copy of Tables and hirearchies.zip (0/1) | Jim | Database Design | 1 | June 1st, 2004 01:44 PM |