Is this possible? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

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



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



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



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



rdouglass -> RE: Is this possible? (12/27/2006 15:43:13)

quote:

Is there a more efficient way to accomplish this?


Probably so. Does the example you posted work? [;)]

As to the more efficient way, how I would approach it is with 2 queries; one to get total hours and one to get total payments both by ID's. Read them both into arrays by opening and closing the DB just once. Then loop thru the 'first' array and match ID's in the second array.

Does that make any sense?

If so, do you have some time for this? This would be a good project IMO to get 'your feet wet' regarding arrays. By using arrays, you could do all of this in just 1 DB open and close and only 2 queries. At least the way I see it.

You game for it?



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



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



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



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



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



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



rdouglass -> RE: Is this possible? (12/29/2006 13:39:10)

You sure that's the right code? That's not what I posted. (It looks like your original.[8|][;)])



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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.15625