Complex Multitable INSERT help needed!!

Results 1 to 2 of 2

Thread: Complex Multitable INSERT help needed!!

  1. #1
    Join Date
    Dec 1969

    Default Complex Multitable INSERT help needed!!

    I have tried to design the code here to do what I WANT it to do.<BR>Can anyone help get me oriented?? So I can get off on the right foot. I have added detailed comments. Thanks, Daniel<BR><BR>&#060;%<BR>dim DbConn, rs<BR>dim sSql<BR>dim field1<BR>dim field2<BR>dim field3<BR><BR>&#039 open database<BR><BR> Set DbConn = Server.CreateObject("ADODB.Connection") <BR> DbConn.Open "private"<BR> Set rs = Server.CreateObject("ADODB.Recordset")<BR> rs.Open "SELECT InstitutionCode, MemberID FROM tblInstitutionMembers", DbConn<BR><BR>&#039 access first table unfortunately table tblInstitutionMembers does not have a common field with table tblUsers<BR>&#039 there is however 2 fields in tblInstitutionMembers that equals 1 field in tblUsers<BR>&#039 so I have to join these 2 fields first<BR><BR>DO WHILE NOT rs.EOF<BR><BR>field1 = rs("InstitutionCode")<BR>field2 = rs("MemberID")<BR>field3 = field1 & "-" & field2<BR><BR>&#039 I am going to need to add this value to my TemporaryTable<BR>&#039 I also need to grab fileds fldFName, fldLName, and fldAddress from tblInstitutionMembers<BR>&#039 TemporaryTable will be used later to do a Merge Mail<BR><BR>INSERT field3 INTO TemporaryTable<BR><BR>rs.MoveNext<BR>Loop<BR>rs.Cl ose<BR><BR>&#039 Now that I have that value in TemporaryTable I want to compare it to tblUsers<BR>&#039 As I get the matches, grab each records Password field and place it into TemporaryTable<BR>&#039 them all and need to mail them to the client<BR><BR> rs.Open "SELECT * fldPassword FROM tblUsers WHERE fldUserName = &#039" & field3 & "&#039", DbCon<BR> <BR>INSERT fldPassword INTO TemporaryTable<BR><BR>DO WHILE NOT rs.EOF<BR><BR><BR>rs.MoveNext<BR>Loop<BR>rs.Close< BR><BR> Set rs = Nothing<BR> DbConn.Close<BR> Set DbConn = Nothing<BR>%&#062;

  2. #2
    Join Date
    Dec 1969

    Default I don't think what you are trying to do here..

    is diffucult, but I am totally unsure of what you are trying to do. What result would you like to have...<BR><BR>I would grab the fields - all of the ones you need from the first table.<BR><BR>Then do the INSERT into the tmpTable as you are...<BR><BR>I think you could do an update for the second insert...<BR><BR>UPDATE tmpTable t1 SET fldpassword = (SELECT fldPassword FROM tblUsers t2 WHERE t2.fldUserName = t1.fldUserName)<BR><BR>Then you should be able to send those results to the client.<BR><BR>I think you could handle this all in one recordset though. You know, selecting out the records you want, including the password, and then writing them to a file...<BR><BR>SELECT t1.field1, t1.field2, t1.field3, t2.fldusername, t2.fldPassword FROM tblInstitutionMembers t1, tblUsers t2 WHERE<BR>t2.fldusername = t1.institutioncode + &#039-&#039 + t1.memberid <BR><BR><BR>HTH<BR><BR>Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts