You can do this with a single recordset (see
http://www.aspfaq.com/2241 ).
I'll leave the HTML pretty-ifying up to you.
<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"
cOrg = "": cFund = "": cProg = "": cAcc = ""
set rs = conn.execute(sql)
do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)
if nOrg <> cOrg then ' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg
end if
if nFund <> cFund then ' new fund
response.write "<br>Fund:" & nFund
cFund = nFund
end if
if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if
response.write "<br>" & nAcc & ":" & nPd
rs.movenext
loop
%>
Are your tables really named by year? This is horrible design, you really
should have ONE table and have a column for year. You're really messing up
the whole idea of relational design and entity modeling...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Abby Lee" <abbylee26@hotmail.com> wrote in message
news:80504bef.0408040907.7b342284@posting.google.c om...[color=blue]
> 1st sorry about leangth...couldn't really cut anymore.
>
> I want the output to be
> Organization 320000
> Fund 100004
> Program 777777
> Account1 7234.55
> Account2 -347.99
> Account3 823.55
>
> Program 888888
> Account1 8745.99
> Account2 -9878.33
> ....on and on.
>
> <b>What I can get to work is</b>
> Organization 320000
> Fund 100004
> Program 777777
> Account1 7234.55
>
> Organization 320000
> Fund 100004
> Program 777777
> Account2 -347.99
> ........on and on.
>
> The code below looks right but the Organization, Fund, Program numbers
> never change when they should. I've been looking at this for two
> days...help!
>
> <table border="0">
> <%
> sSQL3 = "SELECT Distinct Organization" & _
> " FROM " & TheYear & _
> " WHERE MonthPD = '" & ThePD & "'"
> set rs3 = Connect.Execute(sSQL3)
> %>
> <tr>
> <td> <font color="#0000FF">Organization </font></td>
> <td colspan="10"><%=rs3("Organization")%></td>
> </tr>
> <% Do until rs3.eof %>
> <%
> sSQL2 = "SELECT Distinct Fund" & _
> " FROM " & TheYear & _
> " WHERE MonthPD = '" & ThePD & "'"
> set rs2 = Connect.Execute(sSQL2)
> %>
> <tr>
> <td> <font color="#0000FF">Fund </font></td>
> <td colspan="10"><%=rs2("Fund")%></td>
> </tr>
> <% Do until rs2.eof %>
> <%
> sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
> " FROM " & TheYear & _
> " WHERE MonthPD = '" & ThePD & "'" & _
> " ORDER BY Organization, Fund, Program"
> set rs4 = Connect.Execute(sSQL4)
> TheProg = rs4("Program")
> %>
> <tr>
> <td> <font color="#0000FF">Program</font> </td>
> <td colspan="10"><%=TheProg%></td>
> </tr>
> <% Do until rs4.eof %>
> <tr>
> <td>Account</div></td>
> <td><%=TheAct%> </td>
> <td><%=TotalAmt%></td>
> </tr>
> <%
> TheFund = rs4("Fund")
> TheOrg = rs4("Organization")
> TheProg = rs4("Program")
> SQL = "SELECT *" & _
> "FROM " & TheYear & _
> "WHERE MonthPD = '" & ThePD & "'" & _
> " and Organization = '" & TheOrg & "'" & _
> " and Fund = '" & TheFund & "'" & _
> " and Program = '" & TheProg & "'" & _
> " ORDER BY Account"
> set rs = Connect.Execute(sSQL)
> TotalAmt=0
> Do until rs.eof
> TheAct = rs("Account")
> TotalAmt = TotalAmt + rs("PdAmount")
>
> Response.Flush
> rs.MoveNext
> Loop
>
> Response.Flush
> rs4.MoveNext
> Loop
>
> Response.Flush
> rs2.MoveNext
> Loop
>
> Response.Flush
> rs3.MoveNext
> Loop %>
> </table>[/color]
