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):
This is wrong somehow as it throws the error:
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>"
Any suggestion to the SQL or another way to achieve this would be appreciated.Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'tblViziteDoctori.strDoctor=tblDoctori.ID Left JOIN tblSpitale ON tblDoctori.strLM=tblSpitale.ID'.