0

I'm building a database for parents and students and am trying to find a way to show the full family relationship in a single query. I have the following tables:

adults (ID=pk, fam_ID=fk, name, phone, head of house(yes/no))

family (fam_ID=pk)

kids (ch_ID=pk, fam_ID=fk, name, ...)

I'd like to have a table or query that shows the whole family together, something like this:

fam_ID, name of head of house, name of adult2, name of child1, name of child2, ..., phone, etc.

I've tried looking at ideas for linking two foreign keys to on primary key, or creating multiple relationships between two tables. But haven't found anything that describes my issue.

I also manually built a test table with fam_ID, adult1_ID, adult2_ID, kid1_id, kid2_id etc., but this isnt viable for the full database and if it were I couldn't get the names linked to the ID's to appear (using query criteria). Is there any way to do this?

Thanks, Josh

Josh
  • 3
  • 2
  • Look at ConcatRelated: http://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value or a crosstab query. – Andre Feb 26 '16 at 10:58
  • Thanks Andre, the ConcatRelated seems to have worked! – Josh Feb 26 '16 at 13:54

0 Answers0