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

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

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

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

just a little advice... you should have a space between SQL and con... like 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 :p
</edit>

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

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.

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

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' "

having month = 'november'

i believe having is the right keyword here

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

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

danke reolf
your the man!!