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. Advertising

  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...

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




    "Abby Lee" <> wrote in message
    news:...
    > 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>
     
    Aaron [SQL Server MVP], Aug 4, 2004
    #2
    1. Advertising

  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
    %>

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
     
    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

    "Aaron [SQL Server MVP]" <> wrote in message news:<>...
    > 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
    > %>


    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:
    > 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


    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.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
     
    Aaron [SQL Server MVP], Aug 6, 2004
    #6
  7. Abby Lee

    Abby Lee Guest

    Re: so many queries within queries I'm confused - got it to work...kinda

    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

    Re: so many queries within queries I'm confused - got it to work...kinda

    Remove the ticks from around your column names.

    Bob Lehmann

    "Abby Lee" <> wrote in message
    news:...
    > 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
    > %>
     
    Bob Lehmann, Aug 6, 2004
    #8
  9. Re: so many queries within queries I'm confused - got it to work...kinda

    Why did you change SELECT Organization, ... to SELECT 'Organization', ...

    ???

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




    "Abby Lee" <> wrote in message
    news:...
    > 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
    > %>
     
    Aaron [SQL Server MVP], Aug 6, 2004
    #9
  10. Abby Lee

    Abby Lee Guest

    Re: so many queries within queries I'm confused - got it to work...kinda

    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.

    "Aaron [SQL Server MVP]" <> wrote in message news:<>...
    > Why did you change SELECT Organization, ... to SELECT 'Organization', ...
    >
    > ???
    >
    > --
    > http://www.aspfaq.com/
    > (Reverse address to reply.)
    >
     
    Abby Lee, Aug 6, 2004
    #10
  11. Abby Lee

    Bob Lehmann Guest

    Re: so many queries within queries I'm confused - got it to work...kinda

    You need a space in between Organization and FROM.

    Bob Lehmann

    "Abby Lee" <> wrote in message
    news:...
    > 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.
    >
    > "Aaron [SQL Server MVP]" <> wrote in message

    news:<>...
    > > Why did you change SELECT Organization, ... to SELECT 'Organization',

    ....
    > >
    > > ???
    > >
    > > --
    > > http://www.aspfaq.com/
    > > (Reverse address to reply.)
    > >
     
    Bob Lehmann, Aug 6, 2004
    #11
  12. Re: so many queries within queries I'm confused - got it to work...kinda

    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.)




    "Abby Lee" <> wrote in message
    news:...
    > 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.
    >
    > "Aaron [SQL Server MVP]" <> wrote in message

    news:<>...
    > > Why did you change SELECT Organization, ... to SELECT 'Organization',

    ....
    > >
    > > ???
    > >
    > > --
    > > http://www.aspfaq.com/
    > > (Reverse address to reply.)
    > >
     
    Aaron [SQL Server MVP], Aug 6, 2004
    #12
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marco Ippolito
    Replies:
    0
    Views:
    2,602
    Marco Ippolito
    Oct 11, 2004
  2. =?Utf-8?B?U3R1?=

    session vars how many is to many ?

    =?Utf-8?B?U3R1?=, Mar 5, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    345
  3. dee
    Replies:
    2
    Views:
    406
  4. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,530
    Crutcher
    Feb 26, 2006
  5. Kyle Schmitt
    Replies:
    9
    Views:
    124
    Sean Mehan
    Nov 14, 2007
Loading...

Share This Page