Need some help with counting (ASP + Access)

Discussion in 'Database Support' started by zaboss, Apr 24, 2008.

  1. zaboss

    zaboss Perch

    Hi,

    i have 3 tables:
    tblViziteDoctori - holds details about visits to doctors
    tblDoctori - holds the details about the doctor
    tblSpitale - holds the details about the hospital the doctor works in.
    In tblViziteDoctori I have the ID of the doctor and in tblDoctori I have the ID of the Hospital. tblSpitale is the only one that holds the information about the City. What I need is to count the visits made within a City during a month, for each of the last 12 months. Here it is the code I have (I save the statistics into an Excel file, not page):

    Code:
    'Visits to doctors per county
    act.WriteLine "<tr>"
    'I grab the list of the cities and parse it, city by city
    	Set rs2 = MyConn.Execute ("select * FROM Liste_Judete ORDER BY Judete ASC") 
    		Do While Not rs2.eof
    ' I grab the month and parse the last 12 months, including the curent one
    For f = 0 to 11
    theDate = DateAdd("m", -f, Now)
    			set rs=MyConn.Execute ("SELECT COUNT (*) as NumarVizite, tblViziteDoctori.strDoctor, tblDoctori.ID, tblDoctori.strLM ,tblSpitale.ID, tblSpitale.strjudet FROM tblViziteDoctori LEFT JOIN tblDoctori ON tblViziteDoctori.strDoctor=tblDoctori.ID Left JOIN tblSpitale ON tblDoctori.strLM=tblSpitale.ID WHERE tblSpitale.strJudet ='"& RS2("Judete") &"' AND Month(StrData) ="& Month(theDate) &" AND Year(strData) ="& Year(theDate) &" GROUP BY tblSpitale.strJudet" )
    			If theDate = Now then	
    				act.WriteLine "<td>" & rs2("Judete") & "</td><td><font color =""#990000"">" & rs("numarvizite") & "</font></td>"
    			Else
    			act.WriteLine "<td><font color =""#990000"">" & rs("numarvizite") & "</font></td>"
    			End if
    		Next
    act.WriteLine "</tr>"
    		rs2.MoveNext
    	loop 
    	rs2.close
    	rs.close
    	set rs = nothing
    	set rs2 = nothing
    act.WriteLine "<tr>"
    act.WriteLine "</tr>"
    
    
    This is wrong somehow as it throws the error:
    Any suggestion to the SQL or another way to achieve this would be appreciated.
  2. cdog

    cdog Perch

    Have you considered setting this up as a view within access and then connecting to the view to get what you require ? That way you could also design the query using the query designer in access when creating the view
  3. zaboss

    zaboss Perch

    Sorted out. It wasn't needed to make the Join, just a simple select with the right WHERE clause.

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services