The table and graph below list the number of records in the database grouped by Document Topic.
<%
'Dim DataConnection1, cmdDC1, RecordSet1, DataConnection2, cmdDC2, RecordSet2
'Dim RecordToEdit, Updated, strUserName, strPassword
'Make a record set that lists the locations
Set conn2 = Server.CreateObject("ADODB.connection")
conn2.Open "DSN=MRCDC"
vsql2="select spatial_general_abbrev, spatial_general_order from qry_Count_byLocation_byType GROUP BY spatial_general_abbrev, spatial_general_order ORDER BY spatial_general_order"
'response.write vsql2
Set oRs2=conn2.execute(vsql2)
Response.Write "
"
'Write the table headers
headerArray = array("Ag.","Bio.","Cult.","Data","Env.","Hyd.","Infra.","N.R.","Soc.","Water","Clim.")
Response.Write "
Location
"
for each topic in headerArray
Response.Write "
" & topic & "
"
Next
Response.Write ""
While NOT oRs2.Eof
Response.Write "
"
Response.Write "
" & oRs2.Fields("spatial_general_abbrev") & "
"
'Create a recordset for the counts that varies by location and topics in the topic array
'Query the data and write it to the table
topicArray = array("Agricultural","Biological","Cultural","Data","Environmental","Hydrology","Infrastructure","Natrual_Resource","Socioeconomic","Water","Climate")
for each topic in topicArray
myField = "doctopics_" & topic
'response.write myfield & ","
Response.Write "
"
Set conn1 = Server.CreateObject("ADODB.connection")
conn1.Open "DSN=MRCDC"
vsql1="SELECT spatial_general_abbrev, " & myField & ", Count(" & myField & ") AS Count FROM qry_Documents GROUP BY spatial_general_abbrev, " & myField & " HAVING spatial_general_abbrev = '" & oRs2.Fields("spatial_general_abbrev") & "' AND " & myField & " = True"
'response.write vsql1
Set oRs1=conn1.execute(vsql1)
if not oRs1.EOF Then
Response.Write oRs1.Fields("Count")
else
Response.Write "0"
end if
Next
Response.Write "
"%>
<%'Graph the data
Response.Write ""%>
<%Response.Write "
"
%>
Information
The data on this page has been created dynamically from the information in the database. As additional information is added to the database, the table and graph on this page will automatically be updated to reflect the new data.
Copyright 2004 - DTM Consulting, Inc. - All Rights Reserved
