combining data from multiple tables into a single field

[n2nuk]

Hi there,

I am after some guidance and assistance on how best to do this, i'll do my best to describe how data has been recorded.

We have a client management system that we use to track family records. each family is made up of the parent record, the siblings and any other relations.

there are separate tables to record the parent, the sibling and other relations (fam_prnt,fam_yp and fam_other) there is one table that contains the main family record called fam. The fam_id generated on this table is used to link to the 3 tables.

What I want to do is run a report that lists all the family members for each family. for example

[pre]fam_id - The Jones Family last_contact_date[/pre]

[pre]Family_parent_id 0013 Sam Jones 03/02/2014 11:45[/pre]
[pre]family_parent_id 0024 Wendy Hones 15/05/2014 16:00[/pre]
[pre]sibling_id 0123 Fred Jones[/pre]
[pre]sibling_id 7843 Amy Jones 08/05/2014 10:00[/pre]

however the data that is being returned looks like this:

fam_id - The Jones Family

[pre]Family_parent_id 0013 Sam Jones sibling_id 0123 Fred Jones[/pre]
[pre]family_parent_id 0024 Wendy Hones sibling_id 7843 Amy Jones[/pre]
[pre]Family_parent_id 0013 Sam Jones sibling_id 0123 Fred Jones[/pre]
[pre]family_parent_id 0024 Wendy Hones sibling_id 7843 Amy Jones[/pre]

How can I get this data to return data in the correct order?

Many thanks

 

[Charliy]

I think you can do it with a SQL command that does a Union. Not my area of expertise, so hopefully someone else will pick it up from here.