I need help with a Query Output...

[tlhawkins]

I need help with a Query Output...

I have a very complicated query that collects data accross 4 tables all linked by key ids . What I want to do is be able to output this query like this:

tbl1.field1
tbl2.field1
tbl3.field1
tbl3.field2
tbl3.field3
tbl3.field4
tbl2.field2
tbl3.field1
tbl3.field2
tbl2.field3
tbl3.field1
tbl1.field2

...

and so on.

I could do this easily with a long series of Queryies inside loops but I dont want to. Does anyone have some tips on looping through a query like this?

Thanks

 

[RushiShroff]

Bacsically you are trying to set a tree view sort of results
where one parent id has some child Ids and those child have some other childs..

if you are sure if there is only upto 3 levels this happens,you can easily set parent child relationship.And concatenate query into one string

So you can easily avoid execution of SQL statements again and again.Just create -DECIDE relationship first and start from bottommost rung,try to find parents and from those results finf uppermost rung results.

Think on it..
For simplification create Query(Access) or View(SQL server) based on
relationship. Rushi Shroff Rushi@emqube.com
"Life is beautiful."

 

[link9]

I control this type of thing with two placeholders and a sub to control spacing the output, which you send a series of spaces for output. If it's all in one recordset, and your three fields where field1, 2, & 3, then:

dim field1, field2
while not rs.eof
if rs(&quot;field1&quot <> field1 then
writeField &quot;&quot;,rs(&quot;field1&quot
field1 = rs(&quot;field1&quot
elseif rs(&quot;field2&quot <> field2 then
writeField &quot; &quot;,rs(&quot;field2&quot
field2 = rs(&quot;field2&quot
else
writeField &quot; &quot;,rs(&quot;field3&quot
end if
rs.movenext
wend

sub writeField(placeholder,txt)
response.write(placeholder & txt)
end sub

replace the series of spaces there with the html non-breaking space & nbsp ; <--- this form would turn that into a space, which is why I didn't put it in there, and put spaces there to show you.

hope that helps!
paul