Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looping problems again

    I have so much trouble with loops and SQL strings they drive me nuts

    I need to loop through my table getting info based upon the users names, so get the first entry count & display how many times they attended and dates then move onto the next user, and do the same, this is what I have so far but its failing and Im unsure why
    <%
    ' Health table for policy id
    Set con = Server.CreateObject("ADODB.Connection")
    con.open ="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath(level&"../../db/wic.mdb") & ";"
    ' Open the NMContacts table
    set rs = server.createobject("ADODB.Recordset")
    'get max records
    SQL = "Select count(*) AS ID From NMContact"
    rs.open SQL,con
    max = rs("ID")
    rs.close
    ' reopen table
    SQL = "Select * from NMContact"
    rs.open SQL,con
    dim Names(1000)
    Names(0) = rs("Name")
    rs.close
    i = 1
    x = 0
    SQL = "Select * From NMContact WHERE Name ='"&Names(x)&"'"
    rs.open SQL,con
    Do while Not rs.EOF
    Do while i <= max
    if rs("attended") = 1 THEN
    attended = "Attended"
    ' total count for attended sessions
    acount = acount + 1
    ELSE
    attended ="Un-attended"
    ucount = ucount + 1
    END IF
    %>
    <tr>
    <td><div align="center"><%=i%></div></td>
    <td><%=rs("Name")%></td>
    <td><div align="center"><%=rs("conDate")%></div></td>
    <td><div align="center"><%=rs("ADate")%></div></td>
    <td><div align="center"><%=attended%></div></td>
    <td><div align="center"><%=acount%></div></td>
    </tr>
    <%
    i = i + 1
    rs.movenext
    loop
    SQL = "Select * From NMContact WHERE Name ='"&Names(x)&"'"
    rs.open SQL,con
    Names(x+1)
    Loop
    %>

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    if you can give us some more info on the structure of this NMcontact table, together with the desired output, i think it can all be done in one query. that would speed up things drastically because you are making a lot of trips to the database
    with table structure i mean like
    Code:
    fieldname1  fieldname2 fieldname2 etc
    ------------------------------------------------
    value1         value1       value1        etc
    value2         value2       value2        etc
    and then what you like to have in your output

    i think all can be done with some simple grouping and counting

    Roelf
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More info

    the database is access
    also need to filter by month

    Name = text
    Condate = date Contact Date
    Adate = date Session date
    attendance = number Atended session 1 or 0
    Month = Month of contact


    info required

    Client (Name)
    Date of Contact
    Date of session
    number of session not attended
    Total Number of sessions per client

    I want to gather info about every contact and some contacts are the same client(name) over the same month repeated
    hope this makes sense

  • #4
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    just a little advice... you should have a space between SQL and con... like
    Code:
    SQL, con
    it might sound like a silly post but it will make your code clearer. Also if you have multiple recordsets, maybe you should think of naming them so you would have a clearer reading of your code

    <edit>
    I hope I wasn't sounding harsh... I wasn't at all . It's just that when you read and re-read your code trying to understand where the problem comes from you can get confused and alter the code where you didn't want... It used to happen to me all the time
    </edit>
    Last edited by jeskel; 11-12-2003 at 11:11 AM.

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    Re: More info

    Originally posted by dawilis
    hope this makes sense
    not yet, but thats probably my mind having a hard time at this moment. Can you export that table and mail it to me, together with an example (in text file) how the output should look like, based on some existing data in the table?

    I will have a look at it then
    Last edited by Roelf; 11-12-2003 at 12:08 PM.
    I am the luckiest man in the world

  • #6
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    more data

    here is some more data the actual database I cant get small enough to place on this forum
    its jpeg
    you will see from the pic the format, all I need is the totals for each name and the last date.
    Attached Thumbnails Attached Thumbnails Looping problems again-client1.jpg  

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    i think this should give what you want:

    select name, count(name) as totalname, max(condate) as lastdate, sum(attended) as totalattended, (count(name) - sum(attended)) as totalnotattended
    from NMcontacts
    group by name

    the group by name makes sure for every name, there is one record in the returned recordset
    the recordset contains the:
    • name - speaks for itself
    • count(name) - the number of times the name appears in the table
    • max(condate) - the last condate found in the table
    • sum(attended) - lucky for you, you store the attended/unattended as a 1/0, so the total amount of attended sessions is the sum of these values for this name
    • (count(name) - sum(attended)) - this gives the total not attended sessions (= total records for this name - attended sessions)


    if this is not what you expect, let me know
    Last edited by Roelf; 11-13-2003 at 08:28 AM.
    I am the luckiest man in the world

  • #8
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort by date

    roelf that work very well, Im suitably impressed,
    I had a play trying to get it to only pick up on dates that i ask but had some trouble if you could assist Id appreciate

    I have a field name called 'month' that has the month name (ie July or Novemver) of the contact, and need to sort on that

    eg
    SQL = "select name, count(name) as totalname, max(conDate) as contdate, max(Adate) as lastdate, sum(attended) as totalattended, (count(name) - sum(attended)) as totalnotattended from NMcontact group by name Where Month = 'November' "

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    having

    having month = 'november'

    i believe having is the right keyword here
    I am the luckiest man in the world

  • #10
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    not sure I follow

    are you saing that

    SQL = "select name, count(name) as totalname, max(conDate) as contdate, max(Adate) as lastdate, sum(attended) as totalattended, (count(name) - sum(attended)) as totalnotattended from NMcontact group by name having moth ='november'"

    will probably work???

  • #11
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    Re: not sure I follow

    try:
    SQL = "select name, count(name) as totalname, max(conDate) as contdate, max(Adate) as lastdate, sum(attended) as totalattended, (count(name) - sum(attended)) as totalnotattended from NMcontact group by name, month having month ='november'"

    i dont have sql-server installed at the moment (i am sick at home) so no guarantees it will work, but i think it will
    I am the luckiest man in the world

  • #12
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vunderbar

    danke reolf
    your the man!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •