so many queries within queries I'm confused

Discussion in 'ASP General' started by Abby Lee, Aug 4, 2004.

  1. Abby Lee

    Abby Lee Guest

    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>
     
    Abby Lee, Aug 4, 2004
    #1
    1. Advertisements

  2. 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...
     
    Aaron [SQL Server MVP], Aug 4, 2004
    #2
    1. Advertisements

  3. Well, there's a little bit of logic to fit here, e.g. if you have two orgs
    with the same program or two programs with the same account, etc. You can
    solve that by setting children to "" whenever you hit a new tier, e.g.

    <%
    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: cFund="": cProg=""
    end if

    if nFund <> cFund then ' new fund
    response.write "<br>Fund:" & nFund
    cFund = nFund: cProg=""
    end if

    if nProg <> cProg then
    response.write "<br>Program:" & nProg
    cProg = nProg
    end if

    response.write "<br>" & nAcc & ":" & nPd

    rs.movenext
    loop
    %>
     
    Aaron [SQL Server MVP], Aug 4, 2004
    #3
  4. Abby Lee

    Abby Lee Guest

    Aaron,

    You are the sweetest thing...I could just kiss you.

    Abby
     
    Abby Lee, Aug 5, 2004
    #4
  5. Abby Lee

    Abby Lee Guest

    I get an Object Required error when I reach the line:
    set rs = conn.execute(sql)

    Do I needt to make a change to my script that connects to my db?
    Set connect = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateOBject("ADODB.Recordset")
    connect.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    connect.Open
     
    Abby Lee, Aug 6, 2004
    #5
  6. I get an Object Required error when I reach the line:
    Well, do you want to use the name conn, or the name connect? Pick one.

    set conn = CreateObject("ADODB.Connection")
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    conn.open connStr
    ....
    set rs = conn.execute(sql)

    or

    set connect = CreateObject("ADODB.Connection")
    connectStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sDataSource
    connect.open connStr
    ....
    set rs = connect.execute(sql)

    I think you'll find that the most common convention is to use the word conn
    (or oConn) as opposed to connect, but you could call it baker or airplane or
    bobizumi if you wanted to... just stick to one name.
     
    Aaron [SQL Server MVP], Aug 6, 2004
    #6
  7. Abby Lee

    Abby Lee Guest

    I used this code...which worked...but gave me strange output.
    Instead of a list of Accounts with totals I got pages of this...
    Organization:Organization
    Fund:Fund
    Program:program
    Account:MonthPD
    Account:MonthPD
    Account:MonthPD
    Account:MonthPD
    Account:MonthPD

    <%
    sql = "SELECT 'Organization','Fund','Program','Account','MonthPD'" & _
    "FROM AllExpenses2004 " & _
    " WHERE MonthPD = '0604'" & _
    " ORDER BY 'Organization', 'Fund', 'Program', 'Account'"

    cOrg = "": cFund = "": cProg = "": cAcc = ""

    set rs = connect.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: cFund="": cProg=""
    end if

    if nFund <> cFund then ' new fund
    response.write "<br>Fund:" & nFund
    cFund = nFund: cProg=""
    end if

    if nProg <> cProg then
    response.write "<br>Program:" & nProg
    cProg = nProg
    end if

    response.write "<br>" & nAcc & ":" & nPd

    rs.movenext
    loop
    %>
     
    Abby Lee, Aug 6, 2004
    #7
  8. Abby Lee

    Bob Lehmann Guest

    Remove the ticks from around your column names.

    Bob Lehmann
     
    Bob Lehmann, Aug 6, 2004
    #8
  9. Why did you change SELECT Organization, ... to SELECT 'Organization', ...

    ???
     
    Aaron [SQL Server MVP], Aug 6, 2004
    #9
  10. Abby Lee

    Abby Lee Guest

    When I use SELECT Organization,
    I get the error message:
    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression 'OrganizationFROM
    AllExpenses2004 WHERE MonthPD = '0604' ORDER BY 'Organization''.

    But the SELECT 'Organization', has its own problems.

    I finally did this:
    SELECT *
    and changed this...to fit my db:
    nOrg = rs(3): nFund = rs(2): nProg = rs(5)
    nAcc = rs(4): nPd = rs(19)

    I hate having to grabb all the extra information but this works.
    Thanks.
     
    Abby Lee, Aug 6, 2004
    #10
  11. Abby Lee

    Bob Lehmann Guest

    You need a space in between Organization and FROM.

    Bob Lehmann

     
    Bob Lehmann, Aug 6, 2004
    #11
  12. Yep, if you response.write(sql) -- pretty common and trivial debugging
    technique -- you'll see that you're missing a space between the column and
    the keyword FROM.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




     
    Aaron [SQL Server MVP], Aug 6, 2004
    #12
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.