Query problem..

[Rex]

Hi I have a query as given below..

familyID individual Members MemberType DOB
-----------------------------------------------------------------------------------------------------
0 0 Sally Mother
0 1 Stuart Father
0 17 Cooper Twin 25/05/2003
0 18 Kasey Twin 25/05/2003
1 2 Michael Father
1 2 Rosemary Mother
1 19 Stephanie Twin 9/10/2003
1 20 Carla Twin 9/10/2003
3 1 Zachary Sibling
3 2 xyz Sibling
3 4 Mandy Mother
3 4 Tony Father
3 23 Ethan Twin
3 24 Caleb Twin
8 5 Ronin Sibling
8 6 Danielle Mother
8 6 Ricky Father

I want to create a new query which would look like this:

0 Sally Stuart Cooper kasey
1 Michael Rosemary Stephanie Carla
3 Zachary xyz Mandy Tony Ethan
Caleb
8 Ronin Danielle Ricky

I have tried to create a crosstab query for it, but I was
unsuccessful..

Any help would be greatly appreciated..

Rex

 

[Guest]

You can use a crosstab. The trick is to create an expression that can be used
as a derived column heading. This is generally done with DCount(). Your
expression for a column heading might look something like:
ColHead: "mem" & DCount("*","[AsGiven]","FamilyID =" & [FamilyID] & " AND
Individual<=" & [Individual] & " AND MemberType <=""" & [MemberType] & """")

The Row Heading would be FamilyID and the Value would be First of Members.