[chuckaitch]

Please help - I have been searching the web for hours and have come up woith nothing

I am working in FrontPage 2003 and creating ASP pages which calls from and writes to an Access database.

I need to create a nested sql query and can't work it out at all. FrontPage's Database Results Wizrd is greyed out if I try to create a second database result inside another one.

Essentially I want to present a list in 2 levels - for each item in the main list I want to display one (or more or less) sub items which are uniquely assciated with that main list item.

I would have thought this would be simple enough and there must have been many people who have done this already?

Many thanks in advance for your help.

[Amadeus]

It is indeed simple enough...just not with FrontPage

One of our members can likley write the SQL statement you require, but we'll need a little more information. Can you provide the schema of the tables and a description of what you want to get out?

[chuckaitch]

I have attached an image of the table structure.

The table is called Members. Essentially I want to list all members and and for each member list the other members where JAID is equal to MemberID.

Many thanks

Attached File(s)

•  DB_Table_Members.psd (126.1K)
  Number of downloads: 60

[chuckaitch]

Sorry - my bad - attached is the .JPG image rather....

Attached image(s)

• 

[chuckaitch]

I would really appreciate whatever help i can get on this - if you need more info please just advise....

[chuckaitch]

OK - Clearly not much help forthcoming from this quarter....

[sigmazero13]

Do you need it as a nested query, or can you do it using a Join?

Maybe something like this:

SELECT *ABC* FROM MEMBERS member1 LEFT JOIN MEMBERS member2 ON member1.MemberID = member2.JAID WHERE *XYZ* ORDER BY member1.MemberID;"

where
- ABC is the list of fields you want to return
- XYZ is the other criteria you want to filter on
- QRS is the ordering stuff (if applicable)

Then you just need the one loop to iterate, and maybe keep track of the "previous" member ID; when it changes, you know you are at the next person. IE, you could do a loop like this (pseudocode)

Dim memberRS, thisID, prevID
**open recordset into memberRS**
prevID = 0
do until memberRS.EOF
 thisID = memberRS("MemberID")
 if thisID = prevID then
  **print the new header info for a new member**
 end if
 **print the info for the right half of the join (the JAID info)**
 prevID = thisID
 memberRS.MoveNext
loop
memberRS.Close

I hope this helps.

This post has been edited by sigmazero13: 15 May 2006 - 10:15 AM

[chuckaitch]

Thanks a mill - i'll try it and get back to ya.