|
jwpenuel -> Update query using two separate tables (6/3/2003 23:17:35)
|
Update query using two separate tables
Here is my problem. I have a monthly report that lists the number of referrals we receive from each physician. If the number of referrals is zero, no entry is made for that physician. I have a master table of about 1500 physicians in our database (doctors table). I want to create a new field for each month so I can track referrals. I have a separate table for each monthly report, for example Jan03.
Here are the fields (and data types) in each table:
Doctors: IDMedic (number), Last(text), First(text), Jan03(number), Feb03, etc
Jan03: IDMedic(Number), Name(text), Jan03(number)
I created a table join query that displays the output that I want, but I really need to be able to write this information into a table, so I can update the table with each month' s reports. (I left out the print loop for the sake of brevity)
<%
' Table_Update.asp
Set conn = server.createobject(" ADODB.Connection" )
Set objRec = server.createobject(" ADODB.Recordset" )
Conn.Open Application(" RefDocList_ConnectionString" )
objRec.ActiveConnection = conn
objRec.open " SELECT Doctors.IDMedic, Doctors.Last, Doctors.First, Jan03.Jan03 FROM Doctors LEFT OUTER JOIN Jan03 ON Jan03.IDMedic = Doctors.IDMedic"
%>
I tried using an UPDATE command to loop through the recordset and write the Jan03 field data to the doctors table. I did not receive any error message, but the doctors table was not altered by running the page.
<%
' Table_Update.asp
Set conn = server.createobject(" ADODB.Connection" )
Set objRec = server.createobject(" ADODB.Recordset" )
Conn.Open Application(" RefDocList_ConnectionString" )
objRec.ActiveConnection = conn
objRec.open " SELECT Doctors.IDMedic, Doctors.Last, Doctors.First, Jan03.Jan03 FROM Doctors LEFT OUTER JOIN Jan03 ON
Jan03.IDMedic = Doctors.IDMedic"
do while not objRec.EOF
units=objRec(" Jan03" )
IDM=objRec(" IDMedic" )
sql = " Update Doctors SET Jan03 = units WHERE IDMedic = IDM"
ObjRec.MoveNext
loop
%>
I also used code to loop through the Jan03 table to update the doctors table
<%
' Table_Update.asp
Set conn = server.createobject(" ADODB.Connection" )
Set objRec = server.createobject(" ADODB.Recordset" )
Conn.Open Application(" RefDocList_ConnectionString" )
objRec.ActiveConnection = conn
objRec.open " SELECT * from Jan03"
do while not objRec.EOF
units=objRec(" Jan03" )
IDM=objRec(" IDMedic" )
sql = " Update Doctors SET Jan03 = units WHERE IDMedic = IDM"
ObjRec.MoveNext
loop
%>
I think the issue may be the need to create two recordsets, but I am not sure as to how to proceed.
|
|
|
|
