|
| |
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
 |
Is this possible? - 12/27/2006 3:19:35
I'm trying to create a report that draws info from three different tables within one database. Here's the outline:
We select a group from our members table
Do While NOT EOF
Save FirstName, LastName and ID.
Display the Name.
Then open another table to collect the sum of volunteer_hours for the same ID.
Display Total Hours
Close that table
Then open a third table to collect the sum of payments for that ID.
Display Total Payments
Close that table
Move to the next record in the original table
Loop
Here is a first attempt:
<!-- #include file="adovbs.inc" -->
<%
Dim conn, connstr, rs, rs2, rs3, strSQL, vID
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Members.ID, Main.LastName, Main.FirstName FROM Members INNER JOIN Main ON Members.ID=Main.ID ORDER BY Main.LastName"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
rs.MoveFirst
Do While NOT rs.EOF
vID=rs("ID")
Response.write rs("FirstName") & " " & rs("LastName")
strSQL2 = "SELECT SUM(HoursSubmitted) AS TotalHours FROM Volunteer_Hours WHERE ID = " & vID & ""
Set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
Response.write "Total Hours = " & rs2("TotalHours")
Rs2.Close
Set Rs2 = Nothing
strSQL3 = "SELECT SUM(PaymentAmount) AS TotalPayment FROM Payments WHERE ID = " & vID & ""
Set rs3 = Server.CreateObject("ADODB.Recordset")
rs3.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
Response.write "Total Payments = " & rs3("TotalPayments")
Rs3.Close
Set Rs3 = Nothing
rs.MoveNext
Loop
' Close the RecordSet
Rs.Close
Set Rs = Nothing
'close the connection
conn.Close
Set conn = nothing
%>
Possible?
Stephen
|
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/27/2006 10:20:44
I would think that 2 queries would work. Wouldn't one look something like this:
SELECT Members.ID, Members.LastName, Members.FirstName, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Members INNER JOIN Volunteer_Hours ON Members.ID = Volunteer_Hours.ID
GROUP BY Members.LastName, Members.FirstName, Members.ID
I personally don't like keeping recordsets open like that but if you do, I'd try to reduce the number of open connections to as few as possible. If you did a similar query using payments, that would reduce them down to 2 connections.
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/27/2006 12:38:22
Roger!
Thanks for your suggestion.
If I understand your query, it will group SumOfHoursSubmitted for each ID. So I'd get a report like this:
Name1 - SumOfHoursSubmitted
Name2 - SumOfHoursSubmitted
Name3 - SumOfHoursSubmitted
etc.
What we're after is:
Name1 - SumOfHoursSubmitted - TotalPayments
Name2 - SumOfHoursSubmitted - TotalPayments
Name3 - SumOfHoursSubmitted - TotalPayments
etc.
That's why I was trying:
1) Finding the Names/IDs (Query1)
2) Do While NOT EOF (in Query1)
3) Display Name (Query1 - leave open)
4) Totaling HoursSubmitted for that ID and Display (Query2 and close)
5) Totaling Payments for that ID and Display (Query3 and close)
6) MoveNext (in Query1)
7) Loop (back to 3)
Does that make sense?
When I try the code I posted above, it finds the first Name/ID from Query1. Then it balks at Query2 with this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/admin/New_Report_Test.asp, line 30
Line 30 is:
Response.write "Total Hours = " & rs2("TotalHours")
So for some reason it is not happy with rs2("TotalHours"). I made sure that all HoursSubmitted fields are not empty (at least 0).
I don't know what else to try...
Thanks for your help.
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/27/2006 12:45:25
quote:
strSQL2 = "SELECT SUM(HoursSubmitted) AS TotalHours FROM Volunteer_Hours WHERE ID = " & vID & ""
And that is Query2? I personally wouldn't keep those recordsets open but we should be able to work thru this anyways.
So what happens if you hardcode that query? For instance:
strSQL2 = "SELECT SUM(HoursSubmitted) AS TotalHours FROM Volunteer_Hours WHERE ID = 1"
or some other known good vID? Does that also error in the same spot?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/27/2006 13:03:41
Here's another option:
Find group of Names/IDs (Query1)
store rsCount to vCounter
store Name to vName
store ID to vID
close table
Do While vcounter > 0
Display Name
Sum HoursSubmitted to TotalHours Where ID = vID (Query2)
Display TotalHours
close table
SumPayments to TotalPayments Where ID = vID (Query3)
Display TotalPayments
close table
Find Next record
Query1 Where ID = vID
MoveNext
store Name to vName
store ID to vID
vCounter=vCounter-1
Loop
This logic makes sense (does it?), but it involves even more querying.
Stephen
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/27/2006 13:29:40
Roger!
Hard coding an ID doesn't seem to help. I got the same error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/admin/New_Report_Test.asp, line 30
Usually that means that the FieldName doesn't exist (or is spelled wrong). Since that "field" rs("TotalHours") is a computed one, perhaps that's the problem...
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/27/2006 13:50:19
strSQL2 = "SELECT SUM(HoursSubmitted) AS TotalHours FROM Volunteer_Hours"
Does that one work? Have you confirmed that 'HoursSubmitted' is spelled correctly as well as 'Volunteer_Hours'?
If so, what happens if yu setup a second conn as in:
Set conn2 = Server.CreateObject("ADODB.Connection")
conn2.Open connstr
strSQL2 = "SELECT SUM(HoursSubmitted) AS TotalHours FROM Volunteer_Hours WHERE ID = " & vID & ""
...
That help any??
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/27/2006 15:12:41
Roger!
I have been fiddling with the new roadmap (several messages above). My only concern is that it opens a closes the tables MANY times (in this case 160 each).
Here is the code that achieves what I want:
<!-- #include file="adovbs.inc" -->
<%
Dim conn, connstr, rs, rs2, rs3, strSQL, vID, vCounter, vName
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Members.ID, Main.LastName, Main.FirstName FROM Members INNER JOIN Main ON Members.ID=Main.ID ORDER BY Main.LastName"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
vCounter=rs.RecordCount
rs.MoveFirst
vID=rs("ID")
vName=rs("FirstName") & " " & rs("LastName")
Rs.Close
Set Rs = Nothing
Do While vCounter >= 1
Response.write vName & "<br>"
strSQL = "SELECT SUM(Volunteer_Hours.HoursSubmitted) AS TotalHours FROM Volunteer_Hours WHERE Volunteer_Hours.ID = " & vID & ""
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
Response.write "Total Hours = " & rs("TotalHours") & "<br>"
Rs.Close
Set Rs = Nothing
strSQL = "SELECT SUM(Payments.PaymentAmount) AS TotalPayments FROM Payments WHERE Payments.ID = " & vID & ""
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
Response.write "Total Payments = " & rs("TotalPayments") & "<br><br>"
Rs.Close
Set Rs = Nothing
If vCounter = 1 Then
Exit Do
Else
strSQL = "SELECT Members.ID, Main.LastName, Main.FirstName FROM Members INNER JOIN Main ON Members.ID=Main.ID ORDER BY Main.LastName"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
rs.Find "ID = " & vID
rs.MoveNext
vID=rs("ID")
vName=rs("FirstName") & " " & rs("LastName")
Rs.Close
Set Rs = Nothing
End If
vCounter = vCounter-1
Loop
'close the connection
conn.Close
Set conn = nothing
response.write "Done!"
%>
I did notice that the SQL interpreter didn't seem to like rs2("TotalHours"). So I just scrapped differentiating between the queries and called that all the same, as you ca see. I don't know way the rs2 code wouldn't work.
Is there a more efficient way to accomplish this?
Stephen
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/27/2006 17:40:41
Yes, I'll try the array suggestion.
At the moment we have the page working with the most recent code I posted (only slightly edited).
Say I create the two queries you suggest. By what mechanism do I get the results into an array?
Say Query 1 achieves the ID, Name, and TotalHours in either ID or Name order and Query 2 achieves the TotalPayments in the same order. I'd need to create an array with 4 elements and fill the first three from the Query 1 and the fourth from Query 2. Right?
Stephen
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/27/2006 19:33:08
In an effort to begin my query to array page, I've begun like this:
<%
Dim conn, connstr, rs, strSQL, vID, vName
Dim aResults()
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Members.ID, Sum(Volunteer_Hours.HoursSubmitted) AS TotalHours FROM Members INNER JOIN Volunteer_Hours ON Members.ID = Volunteer_Hours.ID GROUP BY Members.ID"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
aResults = rs.GetRows()
Rs.Close
Set Rs = Nothing
Dim iRowLoop, iColLoop
For iRowLoop = 0 to UBound(aResults, 2)
For iColLoop = 0 to UBound(aResults, 1)
Response.Write(aResults(iColLoop, iRowLoop) & "<br>")
Next 'iColLoop
Response.Write("<p>")
Next 'iRowLoop
response.write "<br><br> Done!"
I found some of this code at 4GuysfromRolla.com.
However, I get an Data Type Mismatch error on:
aResults = rs.GetRows()
I have no idea why...
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/28/2006 9:58:21
Are you comfortable with arrays? If so, let's start with this code and see if it runs OK. Obviously wwe want to do this on a test page and not the live one:
<%
Dim conn, connstr, rs, strSQL, vID, vName
Dim aResults()
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Members.ID, Members.LastName, Members.FirstName, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Members INNER JOIN Volunteer_Hours ON Members.ID = Volunteer_Hours.ID GROUP BY Members.LastName, Members.FirstName, Members.ID"
set rstemp=conn.execute(strSQL)
IF rstemp.eof THEN
response.write("No records found.")
ELSE
hoursdata=rstemp.getrows
FOR i = 0 to ubound(hoursdata,2)
for j = 0 to ubound(hoursdata,1)
Response.write(hoursdata(j,i)&" - ")
next
response.write("<br>")
next
END IF
rstemp.close
conn.close
set conn = nothing
%>
This should 'spit' out a list of members and sum of hours. Does it for you? It does for me. If this works, we can move to the next stage.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/28/2006 11:24:22
It turns out I had to alter the query to omit FirstName and LastName because they don't exist in the Members table - they are in a table called Main.
Removing reference to those fields allowed the script to work. A list on IDs and Volunteer Hours appeared on the screen.
Here's our problem. It seems when INNER JOINing two or more tables, no result is given if there isn't a corresponding record in each table for each ID. If someone has no Volunteer Hours entered, then that person's record won't appear on the list. It would be the same for Payments.
So we need to develop a query with an OUTER JOIN, so all records in the RIGHT table(s) show up regardless of whether the corresponding field is full or empty.
Does that make sense?
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/28/2006 11:32:40
quote:
Does that make sense?
Makes absolute sense. We can correct that with the appropriate query once we get the theory banged out.
Now, what does this do? Does this get you closer? (This code assumes that the field "ID" in all tables is the "MemberID" relationship.)
<%
Dim conn, connstr, rs, strSQL, vID, vName
Dim aResults()
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Members.ID, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Members INNER JOIN Volunteer_Hours ON Members.ID = Volunteer_Hours.ID GROUP BY Members.ID"
set rstemp=conn.execute(strSQL)
IF rstemp.eof THEN
response.write("No records found.")
ELSE
hoursdata=rstemp.getrows
END IF
strSQL = "SELECT Payments.ID, SUM(Payments.PaymentAmount) AS TotalPayments FROM Payments GROUP BY Payments.ID"
set rstemp2=conn.execute(strSQL)
IF rstemp2.eof THEN
response.write("No records found.")
ELSE
paymentdata=rstemp2.getrows
END IF
rstemp.close
rstemp2.close
conn.close
set conn = nothing
'now here is where the comparison is done
FOR i = 0 to ubound(hoursdata,2)
for j = 0 to ubound(paymentdata,2)
IF cint(hoursdata(0,h)) & cint(paymentdata(0,j)) THEN
Response.write("Member ID = " & hoursdata(0,h) & "<br>" & VbCrLf)
Response.write("Hours = " & hoursdata(1,h) & "<br>" & VbCrLf)
Response.write("Payments = " & paymentdata(0,h) & "<br><br>" & VbCrLf)
END IF
NEXT
NEXT
%>
This should give you a list of IDs, Hours, and Payments. Again, we can address the empty ones in a moment. Does this help?
Also, notice that now we open the DB only once, grab all of our data, and then close the DB before we ever do any comparisons.
< Message edited by rdouglass -- 12/28/2006 13:09:01 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 2:37:17
Thanks so much for your help and encouragement.
Two issues.
1) I forgot that we need to get the names (FirstName and LastName) from a table called Main, as well. Perhaps we can get that into the second query something like:
SELECT Main.LastName, Main.FirstName, Main.ID, Payments.ID, SUM(Payments.PaymentAmount) AS TotalPayments FROM Main INNER JOIN Payments ON Main.ID = Payments.ID GROUP BY Members.LastName"
Does that look vaguely correct?
2) Here is the result of the code you sent:
Member ID = 2
Hours = 4
Payments = 2
Member ID = 2
Hours = 4
Payments = 2
Member ID = 2
Hours = 4
Payments = 2
There were hundreds (maybe thousands) more of these - it took quite some time, too.
So the comparison of the Arrays isn't working quite as expected.
Hmmmm....
Stephen
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 2:45:03
I looked through the comparison (not that I understand it fully). I wondered why there was an ampersand here:
IF cint(hoursdata(0,h)) & cint(paymentdata(0,j)) THEN
Just our of curiosity, I changed it to an equal sign
IF cint(hoursdata(0,h)) = cint(paymentdata(0,j)) THEN
and ran it again.
This time I got the same result:
Member ID = 2
Hours = 4
Payments = 2
Member ID = 2
Hours = 4
Payments = 2
Member ID = 2
Hours = 4
Payments = 2
but only about 168 entries, which sounds more accurate. And it ran a lot faster!
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/29/2006 8:51:15
quote:
IF cint(hoursdata(0,h)) = cint(paymentdata(0,j)) THEN
That is the correct one, sorry.
However it is in an unexpected loop 'cause I used the wrong variables. I made a quick model of your DB and came up with this code:
<%
Dim conn, connstr, rs, strSQL, vID, vName
Dim aResults()
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Main.ID, Main.FirstName, Main.LastName, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Main INNER JOIN Volunteer_Hours ON Main.ID = Volunteer_Hours.ID GROUP BY Main.ID, Main.FirstName, Main.LastName"
set rstemp=conn.execute(strSQL)
IF rstemp.eof THEN
response.write("No records found.")
ELSE
hoursdata=rstemp.getrows
END IF
strSQL = "SELECT Payments.ID, SUM(Payments.PaymentAmount) AS TotalPayments FROM Payments GROUP BY Payments.ID"
set rstemp2=conn.execute(strSQL)
IF rstemp2.eof THEN
response.write("No records found.")
ELSE
paymentdata=rstemp2.getrows
END IF
'now here is where the comparison is done
FOR i = 0 to ubound(hoursdata,2)
for j = 0 to ubound(paymentdata,2)
IF cint(hoursdata(0,i)) = cint(paymentdata(0,j)) THEN
Response.write("Member = " & hoursdata(2,i) & ", " & hoursdata(1,i) & "<br>" & VbCrLf)
Response.write("Hours = " & hoursdata(3,i) & "<br>" & VbCrLf)
Response.write("Payments = " & paymentdata(1,j) & "<br><br>" & VbCrLf)
END IF
NEXT
NEXT
rstemp.close
rstemp2.close
conn.close
set conn = nothing
%>
This any better?
< Message edited by rdouglass -- 12/29/2006 9:24:10 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 11:14:14
Roger!
It looks like this works! I have to check it against the database to make sure it's correct.
Could you explain what your comparison is doing?
Also, is it possible to get the results into table format?
Thanks so much.
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/29/2006 11:37:55
What I'm doing is taking 2 queries and putting them into memory-based arrays (think table or spreadsheet). Then I start looping thru the first array. For each item in the first array, I loop thru the second array and compare ID's. When they match, I write the LastName, FirstName, and Hours from the first query and the Payments from the second.
You can make it write a table like this:
....
IF cint(hoursdata(0,i)) = cint(paymentdata(0,j)) THEN
Response write("<tr>" & VbCrLf)
Response.write("<td>" & hoursdata(2,i) & ", " & hoursdata(1,i) & "</td>" & VbCrLf)
Response.write("<td>" & hoursdata(3,i) & "</td>" & VbCrLf)
Response.write("<td>" & paymentdata(1,j) & "</td>" & VbCrLf)
Response write("</tr>" & VbCrLf)
END IF
....
Just make sure you put the open and close <table> tags at thye beginniong and end and of course any column headings. See what I'm doing?
It should also run far faster than the recordset method.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 12:49:00
I still haven't confirmed that the resulting recordset is correct (I expect it is).
However, I do need to get the final results into alphabetical order by LastName, FirstName.
Can I do that at the query level? Or is there a way to sort the records within the array?
To view the results so far, look at:
http://www.anmt.org/admin/New_Report_Test1.asp
You will see that it starts it alpha order with one or two out of place records, then after once through the alphabet it breaks down altogether.
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/29/2006 13:06:41
quote:
SELECT Main.ID, Main.FirstName, Main.LastName, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Main INNER JOIN Volunteer_Hours ON Main.ID = Volunteer_Hours.ID GROUP BY Main.ID, Main.FirstName, Main.LastName
Try this one:
SELECT Main.ID, Main.FirstName, Main.LastName, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Main INNER JOIN Volunteer_Hours ON Main.ID = Volunteer_Hours.ID GROUP BY Main.ID, Main.FirstName, Main.LastName ORDER BY Main.LastName, Main.FirstName
That work?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 13:07:51
It seems that there are differences in the results between the original code (accessing the database many times) and the array based code. The results of the original code (text only):
Jake Anthony $223.76 4
Terrence Atkins $175.00 4
Carmen Balas $150.00 4
Janet Barnet 4
Jordan Beck $223.75 4
Harriet Belkin 2
William Berry $842.50 4
Kellen Blair 2
Maureen Borillo 4
Joseph Cardinale $150.00 4
Cheryl Ceprati 4
Susie Chodakiewitz 4
Sally Conerton 4
Beverly Crain 3
Dan David 4
Elise Dewsberry 1
Douglas Dispiri $200.00 2
Barbara Dixon 4
Noelle Donfeld 4
Marylou Dunn $150.00 4
Brian Dyer 4
Myrna Emata 2
Virginia Emrick $100.00 4
Peder Fedde $200.00 4
Richard Fowkes 4
Kenneth Gardner 4
Mitch Glaser 4
James Goins $300.00 4
Darin Goulet $197.50 4
Susan Grace 2
Charles Gran 4
Debra M. Gussin 4
Scott Guy 4
Evelyn Halus 4
Joyce Harris 4
David Holmes 4
Bonnie Janofsky 4
Bill Johnson 4
Allison Johnson 4
Larry Johnson 4
Neville Johnson 4
Kincaid Jones $200.00 4
Marvin Kaplan 4
Madley Katarungan $330.00 4
Jason Kuller $300.00 4
Eric H. F. Law 4
Brian Leader 4
Adam LeBow 4
Bebe McGarry $150.00 4
Cindy O'Connor 4
Marian Partee 2
Greg Pasqua 4
Andrea Press 4
Tara Redepenning $197.50 4
Hillary Rollins 4
Jeffrey Scharf 2
Jeffery Segal $150.00 4
Sandy Shanin 4
Robin Share 4
Chris Smith $200.00 4
Anna Stone 4
Stephen Vendette 4
Peter Welkin 4
Alex Wexler 4
Brian Woodbury 4
Patricia Zehentmayr 4
If you compare that to what you see on the page I linked you to, you'll see anomalies - names on one report missing from the other...
Stephen
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 13:27:26
Thanks, Roger. The ORDER By in the query put everything in its place. Now for the anomalies...
Here is the code for the page that works (it's password protected on our site).
<!-- #include virtual="/spooky_protection_level3.inc" -->
<!-- #include file="adovbs.inc" -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>ANMT Members Good Standing Report</title>
</head>
<body stylesrc="../about_us.asp">
<table border="0" cellspacing="1" style="border-collapse: collapse" bordercolor="#111111" width="95%" height="7"><tr>
<td width="25%" height="1">
<a href="../index.htm" style="text-decoration: none"><img border="0" src="../images/ANMT_logo_transparent.gif"></a></td>
<td width="50%" height="1"><p align="center">
<font face="Arial"><b><font size="5" color="#000063">
PAYMENTS - <br>
</font><font size="5" color="#FF0000">
Good Standing Report -<br>
1st Quarter<br></font><i><a href="admin9.asp">
<font color="#000063">Back to Member Payment Menu</font></a></i></b></font></td>
<td width="25%" height="1"><a href="../arc" style="text-decoration: none"><img border="0" src="../images/ARC_logo_transparent.gif"></a></td>
</tr>
</table>
<div align="center">
<center>
<tr><p style="margin-top:25; margin-bottom:25" align="center"><b>
<font face="Arial">The following members are <font color="#FF0000">NOT IN GOOD STANDING</font><br>
for the First Quarter of 2006-07</font></p></tr>
<table width="426" bordercolor="#111111" cellpadding="4" cellspacing="0" border="1" style="border-collapse: collapse">
<thead>
<tr>
<td align="left" width="152" bgcolor="#000063">
<p style="margin-top: 3; margin-bottom: 3">
<font face="Arial" color="#FFFFFF"><b>Member Name</b></font></td>
<td align="right" width="121" bgcolor="#000063">
<p style="margin-top: 3; margin-bottom: 3">
<font face="Arial" color="#FFFFFF"><b>$ Outstanding</b></font></td>
<td align="center" width="142" bgcolor="#000063">
<p style="margin-top: 3; margin-bottom: 3">
<font face="Arial" color="#FFFFFF"><b>Hours Due</b></font></td>
</tr>
</thead>
<tbody>
<%
Dim conn, connstr, rs, rs2, rs3, strSQL, vID, vCounter, vName
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Members.ID, Members.ANMTLevel, Members.ARCLevel, Main.LastName, Main.FirstName FROM Members INNER JOIN Main ON Members.ID=Main.ID WHERE (Members.ANMTLevel = 'Associate' OR Members.ANMTLevel = 'Intermediate' OR Members.ANMTLevel = 'Introductory' OR Members.ARCLevel = 'ARC Member') ORDER BY Main.LastName"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
vCounter=rs.RecordCount
rs.MoveFirst
vID=rs("ID")
vName=rs("FirstName") & " " & rs("LastName")
Rs.Close
Set Rs = Nothing
Do While vCounter >= 1
strSQL = "SELECT SUM(Payments.PaymentAmount) AS TotalPayments FROM Payments WHERE Payments.ID = " & vID & " AND Payments.PaymentDate >= #8/1/2006#"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
vTotalPayments = rs("TotalPayments")
Rs.Close
Set Rs = Nothing
strSQL = "SELECT SUM(Volunteer_Hours.HoursSubmitted) AS TotalHours FROM Volunteer_Hours WHERE Volunteer_Hours.ID = " & vID & " AND Volunteer_Hours.TaskDate >= #08/01/2006# AND Volunteer_Hours.TaskDate < #03/01/2007#"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
vTotalHours = rs("TotalHours")
Rs.Close
Set Rs = Nothing
IF vTotalPayments <0 OR vTotalHours <4 THEN %>
<tr><td width="152" align="left"><font face="Arial"><%=vName%> </td>
<%IF vTotalPayments <0 THEN %>
<td width="121" align="right"><font face="Arial" color="#FF0000"><%=FormatCurrency(vTotalPayments-vTotalPayments-vTotalPayments,2,-1,-1)%> </td>
<%ELSE%>
<td width="121">
<%END IF%>
<%IF vTotalHours <4 THEN
vHoursDue = vTotalHours-4 %>
<td width="121" align="center"><font face="Arial" color="#FF0000"><%=vHoursDue-vHoursDue-vHoursDue%> </td>
<%ELSE%>
<td width="142">
<%END IF%>
<%
END IF
If vCounter = 1 Then
Exit Do
Else
strSQL = "SELECT Members.ID, Members.ANMTLevel, Members.ARCLevel, Main.LastName, Main.FirstName FROM Members INNER JOIN Main ON Members.ID=Main.ID WHERE (Members.ANMTLevel = 'Associate' OR Members.ANMTLevel = 'Intermediate' OR Members.ANMTLevel = 'Introductory' OR Members.ARCLevel = 'ARC Member') ORDER BY Main.LastName"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
rs.Find "ID = " & vID
rs.MoveNext
vID=rs("ID")
vName=rs("FirstName") & " " & rs("LastName")
Rs.Close
Set Rs = Nothing
End If
vCounter = vCounter-1
Loop
'close the connection
conn.Close
Set conn = nothing
%>
</tbody>
</table>
</center>
</div>
</body>
</html>
I noticed one other weird result. Only one record is wildly off - Terence Atkins. Our new Payment result is WAY higher than it should be, I think.
Stephen
| |
|
|
|
rdouglass
Posts: 9228
From: Biddeford, ME USA
Status: offline
|
RE: Is this possible? - 12/29/2006 13:36:51
quote:
Our new Payment result is WAY higher than it should be, I think.
Can you confirm that?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Is this possible? - 12/29/2006 13:45:01
Sorry!
The code I posted IS our original code that produces what we believe are correct results - which I posted separately above. I posted for comparison purposes...
OUR (array-based) code currently looks like this:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>TEST TEST TEST ANMT Members Good Standing Report</title>
</head>
<body stylesrc="../about_us.asp">
<%
Dim conn, connstr, rs, strSQL, vID, vName
Dim aResults()
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
& Server.MapPath("/fpdb/academy.mdb")
conn.Open connstr
strSQL = "SELECT Main.ID, Main.FirstName, Main.LastName, Sum(Volunteer_Hours.HoursSubmitted) AS SumOfHoursSubmitted FROM Main INNER JOIN Volunteer_Hours ON Main.ID = Volunteer_Hours.ID GROUP BY Main.ID, Main.FirstName, Main.LastName ORDER BY Main.LastName, Main.FirstName"
set rstemp=conn.execute(strSQL)
IF rstemp.eof THEN
response.write("No records found.")
ELSE
hoursdata=rstemp.getrows
END IF
strSQL = "SELECT Payments.ID, SUM(Payments.PaymentAmount) AS TotalPayments FROM Payments GROUP BY Payments.ID"
set rstemp2=conn.execute(strSQL)
IF rstemp2.eof THEN
response.write("No records found.")
ELSE
paymentdata=rstemp2.getrows
END IF
'close tables and connection
rstemp.close
rstemp2.close
conn.close
set conn = nothing
%>
<table border="0" cellspacing="1" style="border-collapse: collapse" bordercolor="#111111" width="95%" height="7"><tr>
<td width="25%" height="1">
<a href="../index.htm" style="text-decoration: none"><img border="0" src="../images/ANMT_logo_transparent.gif"></a></td>
<td width="50%" height="1"><p align="center">
<font face="Arial"><b><font size="5" color="#000063">
PAYMENTS - <br>
</font><font size="5" color="#FF0000">
Good Standing Report -<br>
1st Quarter<br></font><i><a href="admin9.asp">
<font color="#000063">Back to Member Payment Menu</font></a></i></b></font></td>
<td width="25%" height="1"><a href="../arc" style="text-decoration: none"><img border="0" src="../images/ARC_logo_transparent.gif"></a></td>
</tr>
</table>
<div align="center">
<center>
<tr><p style="margin-top:25; margin-bottom:25" align="center"><b>
<font face="Arial">The following members are <font color="#FF0000">NOT IN GOOD STANDING</font><br>
for the First Quarter of 2006-07</font> </b></p></tr>
<table width="426" bordercolor="#111111" cellpadding="4" cellspacing="0" border="1" style="border-collapse: collapse">
<tr>
<td align="left" width="152" bgcolor="#000063">
<p style="margin-top: 3; margin-bottom: 3">
<font face="Arial" color="#FFFFFF"><b>Member Name</b></font></td>
<td align="right" width="121" bgcolor="#000063">
<p style="margin-top: 3; margin-bottom: 3">
<font face="Arial" color="#FFFFFF"><b>$ Outstanding</b></font></td>
<td align="center" width="142" bgcolor="#000063">
<p style="margin-top: 3; margin-bottom: 3">
<font face="Arial" color="#FFFFFF"><b>Hours Due</b></font></td>
</tr>
<%
'now here is where the comparison is done
FOR i = 0 to ubound(hoursdata,2)
for j = 0 to ubound(paymentdata,2)
IF cint(hoursdata(0,i)) = cint(paymentdata(0,j)) THEN
If paymentdata(1,j) < 0 OR hoursdata(3,i) < 4 THEN
Response.write("<tr>" & VbCrLf)
'Name (Last, First)
Response.write("<td align='left'><font face='Arial'>" & hoursdata(2,i) & ", " & hoursdata(1,i) & "</td>" & VbCrLf)
'Payment Due
If paymentdata(1,j) < 0 Then
Response.write("<td align='right'><font face='Arial' color='#FF0000'>" & FormatCurrency((paymentdata(1,j)-paymentdata(1,j)-paymentdata(1,j)),2,-1,-1) & "</td>" & VbCrLf)
Else
Response.write("<td align='center'></td>" & VbCrLf)
End If
'Volunteer Hours Due
If hoursdata(3,i) < 4 THEN
Response.write("<td align='center'><font face='Arial' color='#FF0000'>" & ((hoursdata(3,i)-4)-(hoursdata(3,i)-4)-(hoursdata(3,i)-4)) & "</td>" & VbCrLf)
Else
Response.write("<td align='center'></td>" & VbCrLf)
End If
Response.write("</tr>" & VbCrLf)
END IF
END IF
NEXT
NEXT
Response.write "</table>"
%>
</center>
</div>
</body>
</html
How's that?
Stephen
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
