ASP, How to get users information from a family tree?

[yakovw]

Hallo
Please help on this asp programming.
I want to display family members details from access db.
I have many records with details like:
Name; Parent's name; Age; Phone; Email; Address; Occupation; Son No
I want to display each Family once, the Parent's name at first (Parent's name is Unique) and bellow
All family members (order by "Son No").
How can I gather all the children's of "Maria & Dan Griffin" display this way:

Maria & Dan Griffin

Drew Griffin
18, student, living in London
Email…Phone….

Mike Griffin
24, student, living in Oxford
Email…Phone….

Son No 3…

Thanks! :confused:

[Whatever Jr.]

Hi,

Maybe this will help


Dim tmp, sql, oRS

sql = "SELECT * FROM family ORDER BY Son No"
tmp = ""


Set oRS = oConn.execute(SQL)
If Not oRS.eof Then
Do Until oRS.eof

If tmp <> oRS("Parent's name") Then
Response.Write oRS("Parent's name") & "<br>"
End If
Response.Write oRS("Son's name") & "<br>"
Response.Write oRS("age") & ", " & oRS("occupation") & ", living in " & oRS("Address") & "<br>"


tmp = oRS("Parent's name")
oRS.Movenext
Loop
End If
Set oRS = Nothing


I haven't tested this, but this'll push you in the right direction.

And have a look at your column names. Don't use any spaces or apostrophes.

HTH, Tom

[yakovw]

Hi Tom,

Thanks allot it's working fine!

yakov

[yakovw]

Ops,
there is a problem.
I now getting all the children's of the all the families under: Maria & Dan Griffin

[Whatever Jr.]

Edit you SQL statement

sql = "SELECT * FROM family WHERE lastname = 'Griffin' ORDER BY Son No"


HTH, Tom

[Roelf]

No, in that case you dont get all families.

You have to join the table to itself to get all the information about the parents and the children

SELECT *
FROM family p
LEFT JOIN family c
ON p.lastname = c.parents_name
ORDER BY c.son_no
this way you have records where the parent information will be repeated in every record of a child. The left outer join also takes care of people without children
then you have to loop through the recordset like before, only add the table alias in front of the field you want to show, like:
If tmp <> oRS("p.parents_name") Then
Response.Write oRS("p.parents_name") & "<br>"
tmp = oRS("p.parents_name")
End If
Response.Write oRS("c.name") & "<br>"
Response.Write oRS("c.age") & ", " & oRS("c.occupation") & ", living in " & oRS("c.Address") & "<br>"

[Whatever Jr.]

Where does it say that that's the desired result?
The OP only mentions one family name.

Tom

[Roelf]

These postings gave me that hint, but sorry if i am wrong here. Did not want to offend you.
I want to display each Family once, the Parent's name at first (Parent's name is Unique) and bellow
All family members (order by "Son No").

Ops,
there is a problem.
I now getting all the children's of the all the families under: Maria & Dan Griffin

[yakovw]

To: The luckiest man in the world...

Yor are right,

I have a Page called: All.asp
There I list All the "Pname - Fname" - each is a link to: ShowFamily.asp
(Pname = Parent's name, Fname = last name)
Now, I am expecting to see what I meant at the beginning.
But on: ShowFamily.asp?Pname=Maria_Dan
I get an empty list.
What is the correct way to do?
How do I get the right children's to the Chosen Family from the other page: (All.asp)

Thanks

Tom,
I sorry If I was'nt clear at the first time