<DIV>I have four tables.</DIV>
<DIV> </DIV>
<DIV>Table1 is where all the records end up. It starts out empty.</DIV>
<DIV> </DIV>
<DIV>1. I take all transactions from Table2 and append them into Table1.</DIV>
<DIV> </DIV>
<DIV>2. I then check each record in Table3 and see if there is a corresponding record in Table1 based on two fields. If so, then I edit the existing record in Table1. If not, I add it.</DIV>
<DIV> </DIV>
<DIV>3. I the do the same as in step 2 for Table4.</DIV>
<DIV> </DIV>
<DIV>I am using DAO recordset processing and use the RS.FindFirst method to see if records from Table3 or 4 are in Table1. </DIV>
<DIV> </DIV>
<DIV>It appears that this FindFirst method is greatly slowing my process. Is there a faster way. I pasted my code below if anyone wants to see it.</DIV>
<DIV> </DIV>
<DIV>Option Compare Database</DIV>
<DIV> Dim dbCurr As DAO.Database<BR> <BR> Dim rsD As DAO.Recordset<BR> Dim rsF As DAO.Recordset<BR> Dim rsP As DAO.Recordset<BR> Dim rsR As DAO.Recordset<BR> <BR> Dim fldName As String<BR> <BR> Public Sub TestRun()<BR> <BR> Call uTData("t")<BR> <BR> End Sub</DIV>
<DIV>Public Sub uTData(objType As String)<BR> <BR> Dim passVar As String<BR> <BR> Set dbCurr = CurrentDb<BR> <BR> 'Clear the old values<BR> DoCmd.SetWarnings False<BR> DoCmd.RunSQL "DELETE * FROM t_DATA"<BR> DoCmd.SetWarnings True<BR> <BR> If objType = "t" Then<BR> passVar = "T_Data_Files"<BR>
Else<BR> passVar = "Q_Data_Files"<BR> End If</DIV>
<DIV>Call DataFiles(passVar, objType)</DIV>
<DIV>End Sub</DIV>
<DIV>Private Sub DataFiles(objName As String, objType As String)</DIV>
<DIV>'******************************************T_Data_Files************************************<BR>'******************************************************************************************<BR> Dim passVar As String<BR> <BR> Set rsD = dbCurr.OpenRecordset("SELECT * FROM T_Data WHERE REGION = 'CENTRAL'", dbOpenDynaset)<BR> Set rsF = dbCurr.OpenRecordset(objName, dbOpenDynaset)</DIV>
<DIV> Do Until rsF.EOF 'Move records from the T_Data_Files<BR> <BR> fldName = Left(rsF!PE, 4) & "_" & Right(rsF!PE, 2) & "F"<BR> rsD.AddNew<BR> rsD!CLIENT = rsF!CLIENT<BR> rsD!REGION = "FILE"<BR> rsD!REV_TYPE =
"FILE"<BR> rsD!FILE = rsF!FILE<BR> rsD(fldName) = rsF!AMOUNT<BR> rsD.Update<BR> <BR> rsF.MoveNext<BR> Loop</DIV>
<DIV>Set rsF = Nothing</DIV>
<DIV>'******************************************************************************************<BR>'******************************************T_Data_Files************************************</DIV>
<DIV> If objType = "t" Then<BR> passVar = "T_Data_Products"<BR> Else<BR> passVar = "Q_Data_Products"<BR> End If</DIV>
<DIV>Call DataProducts(passVar, objType)</DIV>
<DIV>End Sub</DIV>
<DIV>Private Sub DataProducts(objName As String, objType As String)</DIV>
<DIV>'******************************************T_Data_Products*********************************<BR>'******************************************************************************************<BR> <BR> Dim passVar As String<BR> <BR> Set rsD = dbCurr.OpenRecordset("T_DATA", dbOpenDynaset)<BR> Set rsP = dbCurr.OpenRecordset("SELECT * FROM " & objName _<BR> & " ORDER BY CLIENT, FILE", dbOpenDynaset)<BR> <BR> Do Until rsP.EOF 'Move records from the
T_Data_Products<BR> <BR> <BR> rsD.FindFirst ("NZ(CLIENT,'NULL') = '" & Nz(rsP!CLIENT, "NULL") & "' AND " _<BR> & " FILE = '" & rsP!FILE & "'")<BR> If rsD.NoMatch = True Then</DIV>
<DIV> <BR> fldName = Left(rsP!PE, 4) & "_" & Right(rsP!PE, 2) & "P"<BR> rsD.Edit<BR> rsD(fldName) = rsP!AMOUNT<BR> rsD.Update<BR> <BR> Else 'If one is not found, then add
one<BR> <BR> fldName = Left(rsP!PE, 4) & "_" & Right(rsP!PE, 2) & "P"<BR> rsD.AddNew<BR> rsD!CLIENT = rsP!CLIENT<BR> rsD!FILE = rsP!FILE<BR> rsD!REGION = "FILE"<BR> rsD!REV_TYPE =
"PRODUCT"<BR> rsD(fldName) = rsP!AMOUNT<BR> rsD.Update</DIV>
<DIV> End If</DIV>
<DIV> rsP.MoveNext<BR> Loop</DIV>
<DIV> Set rsP = Nothing</DIV>
<DIV>'******************************************************************************************<BR>'******************************************T_Data_Products*********************************</DIV>
<DIV> If objType = "t" Then<BR> passVar = "T_Data_Rev"<BR> Else<BR> passVar = "Q_Data_Rev"<BR> End If</DIV>
<DIV>Call DataRev(passVar, objType)</DIV>
<DIV>End Sub</DIV>
<DIV>Private Sub DataRev(objName As String, objType As String)</DIV>
<DIV>'******************************************T_Data_Rev**************************************<BR>'******************************************************************************************</DIV>
<DIV> Set rsD = dbCurr.OpenRecordset("T_Data", dbOpenDynaset)<BR> Set rsR = dbCurr.OpenRecordset("SELECT * FROM " & objName _<BR> & " ORDER BY CLIENT, FILE, REV_TYPE, REGION", dbOpenDynaset)</DIV>
<DIV> Do Until rsR.EOF 'Move records from the T_Data_Rev</DIV>
<DIV> rsD.FindFirst ("NZ(CLIENT,'NULL') = '" & Nz(rsR!CLIENT, "NULL") & "' AND " _<BR> & " FILE = '" & Nz(rsR!FILE, "No File Revenue") & "'")<BR> If rsD.NoMatch = True Then<BR> <BR> fldName = Left(rsR!PE, 4) & "_" & Right(rsR!PE, 2) &
"R"<BR> <BR> rsD.AddNew<BR> rsD!CLIENT = rsR!CLIENT<BR> rsD!REV_TYPE = rsR!REV_TYPE<BR> rsD!FILE = IIf(IsNull(rsR!FILE), "No File Revenue", rsR!FILE)<BR> rsD!REGION =
rsR!REGION<BR> rsD(fldName) = rsR!AMOUNT<BR> rsD.Update<BR> <BR> Else</DIV>
<DIV> fldName = Left(rsR!PE, 4) & "_" & Right(rsR!PE, 2) & "R"<BR> <BR> rsD.Edit<BR> rsD(fldName) = rsR!AMOUNT<BR> rsD.Update<BR>
<BR> End If<BR> <BR> rsR.MoveNext<BR> <BR> Loop<BR> <BR> Set rsR = Nothing</DIV>
<DIV>'******************************************************************************************<BR>'******************************************T_Data_Rev**************************************</DIV>
<DIV>End Sub<BR></DIV><BR><BR><DIV>
<DIV>
<P align=center><STRONG>Lonnie Johnson<BR></STRONG><STRONG>ProDev</STRONG>, Professional Development of MS Access Databases<BR>Visit me at ==> <A href="http://www.prodev.us/">http://www.prodev.us</A></P>
<P><BR><A href="http://www.galaxymall.com/software/PRODEV"><BR></A><BR> </P></DIV></DIV><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com">Yahoo! SiteBuilder</a> - Free, easy-to-use web site design software