|
| |
|
|
jwpenuel
Posts: 23
From: Fort Myers FL USA
Status: offline
 |
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.
_____________________________
James Penuel
jpenuel@digestivehealth.com
|
|
|
|
Doug G
Posts: 1189
Joined: 12/29/2001
From: SoCal
Status: offline
|
RE: Update query using two separate tables - 6/4/2003 1:32:09
You can do an update from sql executed on the connection without a recordset.
myConn.Execute " UPDATE mytable SET col1 = ' Testing stuff' , col2 = 15" )
_____________________________
======
Doug G
======
| |
|
|
|
jwpenuel
Posts: 23
From: Fort Myers FL USA
Status: offline
|
RE: Update query using two separate tables - 6/4/2003 7:47:03
I am not sure I understand how this command would work in this situation.
I have to either loop through the Jan03 table and use SQL to update the Jan03 field in the doctors table, or somehow convert the recordset created by the SQL join query to another table. That query has a one to one relationship with the records in the doctors table.
I' m still stuck....
_____________________________
James Penuel
jpenuel@digestivehealth.com
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
