A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Horizontal" output from many-to-many tables



 
 
Thread Tools Display Modes
  #1  
Old July 16th, 2004, 12:51 AM
Tony Maddox
external usenet poster
 
Posts: n/a
Default "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  
Old July 16th, 2004, 02:13 AM
Dale Fye
external usenet poster
 
Posts: n/a
Default "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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 08:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.