Need some help with counting (ASP + Access)

Z

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:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'tblViziteDoctori.strDoctor=tblDoctori.ID Left JOIN tblSpitale ON tblDoctori.strLM=tblSpitale.ID'.
Click to expand...
Any suggestion to the SQL or another way to achieve this would be appreciated.
 
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
 
You must log in or register to reply here.
Back
Top