multiple rows in one line 1

[tomvdduin]

I'm having a problem and I don't know where to start to solve it. Perhaps you guys can give me a start:

I want to make a report that lists families that are bounded to our organisation. The layout is as following:

lastname, firstname_man + firstname_women street zipcode city phone
name of kids, separated with comma's

And now with real names:

Duin van der, Tom + mariah 34 Blackstreet 1430AD Amsterdam 020-5372545
Yudith, Anna, David

The first line is not the problem, that's already programmed. But how do I get the names of the childen on one line?

I have a table tbl_person that holds a p_id, first_name, and a family_id and some other stuff about a person.
I also have a table tbl_family that holds the family_id, familyname, etc

in a normal query, I do: select firstname from tbl_person where family_id = [family_id]
in the example above, I get 3 rows of names:

Yudith
Anna
David

how can I get it displayed in one row? like:

Yudith, Anna, David??

 

[PaulBricker]

Is there anything that logically differenciates the children from the parents in the records.

Paul

 

[randysmid]

Hi,
Yes, there is a solution to this.
First, remove the tbl_person from your query, add a textbox (such as txtNames) to the area underneath the family name line, then include the following code in the "Format" event for the detail line:

' STRATEGY - read the database and get all childrens
' names into a recordset (rs). Then, loop through
' this recordset and append them to a
' string (txtNames) that appears on the report.
' Remove the last comma & space from txtNames

Dim rs As Recordset

' next statement assumes family_id is string
Set rs = CurrentDb.OpenRecordset("select firstname from tbl_person where family_id = '" & [family_id] & "'&quot

While rs.EOF = False
txtNames = txtNames & rs!firstName & ", "
rs.MoveNext
Wend

'remove last comma and space from txtNames
If txtNames <> &quot;&quot; Then
txtNames = Left(txtNames, Len(txtNames) -2)
End if



HTH,
Randy Smith
California Teachers Association

 

[tomvdduin]

Thanks, Randy! I'll try that in the weekend. When I find difficulties, i'll post it!!

 

[MichaelRed]

I would think a cross tab piviot on family_id would do much the same and be somewhat faster. I do not have a suitable recordset to illustrate it, so some details need to be considered in implementation.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Michael,
I don't think that a crosstab report is appropriate for what Tom wants. This would be true if he wanted columns dependent on values within a table (e.g., January, February, March, etc.), but he just needs to have the childrens names within one text box. I believe that my solution is the best, but I would love to hear your explanation of why you think the crosstab is better at providing this functionality. By the way Michael, I have learned many things by reading your posts!!!
HTH,
Randy Smith
California Teachers Association

 

[MichaelRed]

Hmmmmmmm ... mmmm

W/o an example (requires a sample data set ... ) I am not sure. It also depends on the structure of the original recordset, as it would be necessary to have appropiate piviot criteria.

That being said (note MULTIPLE caveats), it is difficult to impossible to achieve the effieciency of a stored query with code which loops through a recordset. A Crosstab QUERY would, it it 'aggregates' the child records in the record, there is no reason for the loop, just make the asignment directly from the record.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[tomvdduin]

OK, michael, here's some more information.
I use this tables:

tbl_family:
f_id (autonumber, primary key)
middlename (string)
lastname (string)
address (string)
zip (string)
place (string)
telephone (string)
headperson (number, references to tbl_person)
partner (number, references to tbl_person)

tbl_person:
p_id (autonumber, primary key)
family (number, references to tbl_family)
firstname (string)
child (boolean)

of course there are more columns in these tables, but they aren't used in this query

This is how the address-line is build-up:
at the first line, the middle name and last name of the family are printed. on the same line, the first name of the headperson (mostly the man) of the family is printed, and the first name of the partner (mostly the woman). after that, the address and the phoneno. are printed.
on the line beneath that, the names of the children are printed (if any). Children have the child column true, the headperson and his partner have the child column false, so they can be the only adults in a family.

now the query for the first line:

SELECT f_id, lastname, middlename, address, zip, place, telephone, headperson.firstname, partner.firstname
FROM (tbl_family left join tbl_person AS headperson on tbl_family.headperson = tbl_person.p_id) left join tbl_person as partner on tbl_family.partner = tbl_person.p_id

and the query for the children:
SELECT firstname from tbl_person
WHERE tbl_person.family = [f_id] AND tbl_person.child = true

now the secon dificulty: when there are no children in a family, the second line mustn't simply be blank, but must be skipped! for example:

Duin van der, Tom + mariah 34 Blackstreet 1430AD Amsterdam 020-5372545
Yudith, Anna, David
Fortuin, Pim + johan 34 Blackstreet 1430AD Amsterdam 020-5372545
Ratelband, Emille + Janet 34 Blackstreet 1430AD Amsterdam 020-5372545
Yudith, Anna, David

Hope this info can help you help me!!!!! I realy appreciate!

greetz,

Tom

 

[MichaelRed]

If you are just interested n suppressing the line, it is only necessary to set the &quot;CanGrow&quot; and &quot;CanShrink&quot; properties for ALL of the fields on the 'child' line / row to true, and to NOT populate them with ANYTHING (not even spaces or commas).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[tomvdduin]

Michael,

I will try the things you suggested. When I find difficulties, I'll let you know.

greetz,

Tom

 

[tomvdduin]

And Yes, it works!! perfectly!

But now one more question, I want to print only the families with a headperson or partner that has status 1.
So I added the following to the SQL-query:
where (headperson.status = 1 or partner.status = 1)

Now only the records with BOTH the headperson and partner that have status 1 are printed, where also the families where only the headperson or the partner that have status 1 must be printed. What's wrong here?

 

[MichaelRed]

I would try it in a simpler query until I got the criteria working (perhaps just the two tables and the join (where I suspect the issue to be)m abd build the complete query from the working prototype.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places