Select Query With Parents And Child's In one row

Z

zionsaal

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!!!!!
 
J

John W. Vinson

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!!!!!

This isn't at all easy. It has a result query with a variable number of fields
- and queries (like tables) must be "homogenous", with all records having the
same "shape".

The closest you'll be able to get is to use a Crosstab query, using ParentID,
FirstName, LastName, and Address as Row Headers, and ChildID (or a sequential
number derived from it) as the ColumnHeader. Use First as the totals operator
for the ChildFirst field.

Could you explain what you will DO with this query? Will it be printed,
displayed on a form, or what? There may be a different solution which won't
require this type of query.

John W. Vinson [MVP]
 
Z

zionsaal

Here you go!

Modules: Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm

or

How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/kb/210163

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________











- Show quoted text -

thank you tom

I receive this error:

compile error. in query expression
fConcatChild("TStudentList","ID","StudentFirstName","Long",[ID])
 
T

Tom Wickerath

Do you have a reference set to the Microsoft DAO 3.6 Object Library? This
reference is required, since the code is DAO code. In the Visual Basic
Editor, click on Tools > References. You should see a reference checked with
this name (use version 3.51 for Access 97).

After dismissing the references dialog, click on Debug > Compile
ProjectName, where ProjectName is the name of your VBA project (likely the
same as the name of your database). Does your code compile properly now? If
not, the offending line of code will likely be highlighted. You should always
be in the habit of compiling code after editing it.

This code could stand a slight improvement. On the line that reads:

Dim rs As Recordset

Change it to read:

Dim rs As DAO.Recordset

This way, you will avoid the possibility of a run-time error 13: Type
Mismatch, which is not caught by the compiler when compiling code. This can
happen as a result of reference priority, if you happen to have a reference
included to the ADO library, and it is higher in priority. More details on
this run-time priority related error here:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html


Finally, I agree with John Vinson that this will be very difficult to
achieve if your intent was to display each child in separate fields, in the
query. That was not the way that I initially interpreted your request.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Z

zionsaal

Here you go!
Modules: Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm

How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/kb/210163
Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt...
__________________________________________
"(e-mail address removed)" wrote:
- Show quoted text -

thank you tom

I receive this error:

compile error. in query expression
fConcatChild("TStudentList","ID","StudentFirstName","Long",[ID])- Hide quoted text -

- Show quoted text -

thank you tom

the problem was with the code
dim db as database

I do it with the linked database and it worked

but the values in the field SubFormValues are separated only with a ;
sign
like this:

Ruchy;Leah;Chana

how can I change it to a space and a comma , sign?
like this
Ruchy, Leah, Chana,
 
T

Tom Wickerath

Without having taken the time to test, I think this will do it for you:

Change this line of code:
varConcat = varConcat & rs(strFldConcat) & ";"

to this:
varConcat = varConcat & rs(strFldConcat) & ", "

and change this comment and line of code:
'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

to this:
'That's it... you should have a concatenated string now
'Just Trim the trailing comma space
fConcatChild = Left(varConcat, Len(varConcat) - 2)


Better yet, you might try the following modifications to this procedure, to
make it easier to change the separator in the future:

1.) Add this line of code in the declarations section

Const conSEP = ", "

2.) Change this line of code:
varConcat = varConcat & rs(strFldConcat) & ";"

to
varConcat = varConcat & rs(strFldConcat) & conSEP


3.) Change this line of code:
fConcatChild = Left(varConcat, Len(varConcat) - 1)
to
fConcatChild = Left(varConcat, Len(varConcat) - Len(conSEP))


Now, if you want to change the separator, you should only need to do it in
one place: the line of code that declares the constant conSEP.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Z

zionsaal

On May 20, 3:53 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
thank you tom
I receive this error:
compile error. in query expression
fConcatChild("TStudentList","ID","StudentFirstName","Long",[ID])- Hide quoted text -
- Show quoted text -

thank you tom

the problem was with the code
dim db as database

I do it with the linked database and it worked

but the values in the field SubFormValues are separated only with a ;
sign
like this:

Ruchy;Leah;Chana

how can I change it to a space and a comma , sign?
like this
Ruchy, Leah, Chana,- Hide quoted text -

- Show quoted text -

just I want to say thank you
I put doa.db
and
doa.rs
and I changed to
fConcatChild = Left(varConcat, Len(varConcat) - 2)
varConcat = varConcat & rs(strFldConcat) & ", "

and what you and john wrote:

"Finally, I agree with John Vinson that this will be very difficult
to
achieve if your intent was to display each child in separate fields,
in the
query. That was not the way that I initially interpreted your request.
"

answer
1 Its better for me in 1 field
2 this is the only way to exported to excel to make mailing labels
like:

father name
children
address
zip

and even in access is the best way to make the kind labels


thanks in advance
 
T

Tom Wickerath

I'm glad I could be of help to you.
I put doa.db and doa.rs

I hope that was just a typo. You wanted DAO, not DOA. (By the way, DOA is an
abbreviation for Dead on Arrival. I hope this does not describe your
database! <smile>

Did you try the modification involving the constant, which I named conSEP?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

One more suggested change:

Change this:

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function


to this:

Exit_fConcatChild:
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function


Reason:
Setting a recordset to Nothing, without closing it first, may lead to
database bloat:

http://support.microsoft.com/kb/209847


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
 
Z

zionsaal

One more suggested change:

Change this:

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function

to this:

Exit_fConcatChild:
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

Reason:
Setting a recordset to Nothing, without closing it first, may lead to
database bloat:

http://support.microsoft.com/kb/209847

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html

thanks again
BTW I tried the Const conSEP statement and it works fine

and it was just a typo sorry
"Dim db As DAO.Database
Dim rs As DAO.Recordset"
thanks!!!!!
 
Top