Columns to rows in a query

[Mark Y via AccessMonster.com]

Hello all,

I have a query as below:

-------------------------------------------------

SELECT SLEEP2.FamID, SLEEP2.ResID, SLEEP2.Salutation, SLEEP2.Add1, SLEEP2.
Add2, SLEEP2.Add3, SLEEP2.Add4, SLEEP2.Add5, SLEEP2.Country, SLEEP2.PostCode,
DetailChild.ChildNum, DetailChild.ChildDoB, DetailChild.ChildGender,
DetailChild.Prem, DetailChild.PremGest
FROM SLEEP2 LEFT JOIN DetailChild ON SLEEP2.ResID = DetailChild.ResID
GROUP BY SLEEP2.FamID, SLEEP2.ResID, SLEEP2.Salutation, SLEEP2.Add1, SLEEP2.
Add2, SLEEP2.Add3, SLEEP2.Add4, SLEEP2.Add5, SLEEP2.Country, SLEEP2.PostCode,
DetailChild.ChildNum, DetailChild.ChildDoB, DetailChild.ChildGender,
DetailChild.Prem, DetailChild.PremGest
ORDER BY SLEEP2.FamID;

--------------------------------------------------

There is also a host of other ChildDetail data that I need to pull in but
didn't want to post as there are over 100 fields.
This returns my data as:

FamID ResID Salutation Add1 Add2 Add3 Add4 Add5 Country Postcode ChildNum
ChildDoB
12345 9876 Mr Smith 1, Acacia Ave London England SE1 5DP 123 13/12/88
67890 9512 Mr Jones 2, Jones St York England YO1 3EF
94548 9632 Mr Ali 6, Sunny Rd Acomb Nr York York England YO9 4DB 456
29/09/85
94548 9632 Mr Ali 6, Sunny Rd Acomb Nr York York England YO9 4DB 457
15/07/99

FamID, and ResID are unique in the table SLEEP2 (actually SLEEP 2 is a query).
ChildNUM is unique in the table DetailChild. FamID can have up to 4 ChildID’s

What I would like is for where the FamID has more than 1 ChildNum for me to
be able to display these on one record as follows:

FamID|ResID|Salutation|Add1 |Add2| Add3| Add4|Add5|
Country| Postcode| 1ChildNum |1ChildDoB| 2ChildNum| 2ChildDoB
94548 9632 Mr Ali 6, Sunny Rd Acomb Nr York York
England YO9 4DB 456 29/09/85 457
15/07/99

I would like to do this for each record, even where there are no child
details for a FamID. I hope this makes sense

Is this possible? Could anyone give me a pointer in the right direction as I
am by no means an Access expert

Many thanks in advance

Mark

Familyfund dot org dot UK

 

[John Nurick]

Hi Mark,

One way is to INNER JOIN the table with the child data to itself three
times ON FamID to get a set of fields for up to four children.

If there are four children in a family, do they have ChildNum 1, 2, 3,
4? If so it's fairly easy: in the first appearance of the table just
select records where ChildNum =1, in the second ChildNum = 2 and so on.
If there isn't a convenient field like that, you'll have to generate a
sequential number in the query.

Here's an example of the sort of thing involved. It works in the
Northwind sample database and returns the first two products for each
order (I got bored after one join).

SELECT T1.OrderID, Prod1, Prod2 FROM
(SELECT A.OrderID, A.ProductID AS Prod1
FROM [Order Details] AS A
WHERE (
SELECT Count(ProductID)
FROM [Order Details]
WHERE A.OrderID = OrderID
AND A.ProductID <= ProductID)=1
) AS T1
INNER JOIN
(SELECT A.OrderID, A.ProductID AS Prod2
FROM [Order Details] AS A
WHERE (
SELECT Count(ProductID)
FROM [Order Details]
WHERE A.OrderID = OrderID
AND A.ProductID <= ProductID)=1
) AS T2
ON T1.OrderID = T2.OrderID
;

 

[Mark Y via AccessMonster.com]

Thanks John will give this a try and post back!

Thanks again

Mark

Hi Mark,

One way is to INNER JOIN the table with the child data to itself three
times ON FamID to get a set of fields for up to four children.

If there are four children in a family, do they have ChildNum 1, 2, 3,
4? If so it's fairly easy: in the first appearance of the table just
select records where ChildNum =1, in the second ChildNum = 2 and so on.
If there isn't a convenient field like that, you'll have to generate a
sequential number in the query.

Here's an example of the sort of thing involved. It works in the
Northwind sample database and returns the first two products for each
order (I got bored after one join).

SELECT T1.OrderID, Prod1, Prod2 FROM
(SELECT A.OrderID, A.ProductID AS Prod1
FROM [Order Details] AS A
WHERE (
SELECT Count(ProductID)
FROM [Order Details]
WHERE A.OrderID = OrderID
AND A.ProductID <= ProductID)=1
) AS T1
INNER JOIN
(SELECT A.OrderID, A.ProductID AS Prod2
FROM [Order Details] AS A
WHERE (
SELECT Count(ProductID)
FROM [Order Details]
WHERE A.OrderID = OrderID
AND A.ProductID <= ProductID)=1
) AS T2
ON T1.OrderID = T2.OrderID
;

Hello all,

[quoted text clipped - 51 lines]

Familyfund dot org dot UK