select query of parent and child in one row

[zionsaal]

I have a parent table and a child table
one parent has between 1 and 6 children in the child table I wont a
query to have the parent and the child in one "row"

how do I create that?
thanks for helping me

 

[Guest]

Your table structure is not correct for a relational database but this will
get you there.
This has three but you get the idea and expand on it. Place the table in
the design grid as many times as you have field to combine.
SELECT MultipleFieldData.x, MultipleFieldData.a & IIf(MultipleFieldData_1.b
Is Null,Null," " & MultipleFieldData_1.b) & IIf(MultipleFieldData_2.c Is
Null,Null," " & MultipleFieldData_2.c) AS Combined
FROM (MultipleFieldData INNER JOIN MultipleFieldData AS MultipleFieldData_1
ON MultipleFieldData.x = MultipleFieldData_1.x) INNER JOIN MultipleFieldData
AS MultipleFieldData_2 ON MultipleFieldData.x = MultipleFieldData_2.x;

 

[John W. Vinson]

I have a parent table and a child table
one parent has between 1 and 6 children in the child table I wont a
query to have the parent and the child in one "row"

how do I create that?
thanks for helping me

Which child? Any arbitrary child? All six children as separate fields? All six
children as a comma-separated string? What will you do with this query?

John W. Vinson [MVP]

 

[zionsaal]

Which child? Any arbitrary child? All six children as separate fields? All six
children as a comma-separated string? What will you do with this query?

John W. Vinson [MVP]

thank you john
All children as separate records in a table wich includes a parent id

 

[John W. Vinson]

thank you john
All children as separate records in a table wich includes a parent id

But you say in your first question:

I wont a query to have the parent and the child in one "row"

So you want separate records, and you want one row.

These are contradictory requirements. Separate, or one??

Please post an example of the type of data that you have in your two tables,
and how you would want that data to appear in your report.

John W. Vinson [MVP]

 

[zionsaal]

But you say in your first question:


So you want separate records, and you want one row.

These are contradictory requirements. Separate, or one??

Please post an example of the type of data that you have in your two tables,
and how you would want that data to appear in your report.

John W. Vinson [MVP]

thanks john

I have one table called "ParentList" like this:

ParentID, FirstName, LastName, Address,
1001 John Duo 123 Main St.
1002 Cris Taylor 456 Maple St.


then I have a table called "Children" Like this:

ChildID, ParentID, ChildFirst,
1001 1001 Ab
1002 1001 Bryan
1003 1001 Dave
1004 1002 Jack
1005 1002 Bill

So I want the sql results Like this:

ParentID, FirstName, LastName, Address, Child(1), Child(2),
Child(3),
1001 John Duo 123 Main St. Ab
Bryan Dave
1002 Cris Taylor 456 Maple St. Jack
Bill

thanks in advance!!!!!

 

[John W. Vinson]

then I have a table called "Children" Like this:

ChildID, ParentID, ChildFirst,
1001 1001 Ab
1002 1001 Bryan
1003 1001 Dave
1004 1002 Jack
1005 1002 Bill

So I want the sql results Like this:

ParentID, FirstName, LastName, Address, Child(1), Child(2),
Child(3),
1001 John Duo 123 Main St. Ab
Bryan Dave
1002 Cris Taylor 456 Maple St. Jack
Bill

thanks in advance!!!!!

answered in microsoft.public.access

John W. Vinson [MVP]