Inserting records and checking for nonduplicates

[Steven Cheng]

i have two tables, one linked from my accounting system (don't want to alter
for obvious reasons) and another which has the same fields.

the second table allows me to manually code out each line under a different
categories (e.g. project costing), but because it is done periodically
through a fiscal period/quarter, i am don't want to just Append
all...duplicated records.

is the best of setting this up is using recordsets, gathering a subset of
records from my accounting system, looping through each record in this subset
to see if it has already been imported or not....

here is the coding i am using. can someone let me know if this is the best
way or another way to optimize it.

Private Sub importrecords()
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim db As DAO.Database
Dim sqlstring As String
Dim sqlstring1 As String
Dim period As String
Dim fiscalyr As String
Dim x As Integer
Dim i As Integer

On Error Resume Next

period = InputBox("which fiscal period ")

fiscalyr = InputBox("Which fiscal year")

If Val(period) >= 1 And Val(period) <= 13 Then
If Val(fiscalyr) >= 2006 Then
sqlstring = "SELECT A.ACCTID, A.FISCALYR, A.FISCALPERD,
A.SRCELEDGER, A.SRCETYPE, A.POSTINGSEQ, A.CNTDETAIL, A.JRNLDATE, A.BATCHNBR,
A.ENTRYNBR, A.TRANSNBR, A.JNLDTLDESC, A.JNLDTLREF, A.TRANSAMT"
sqlstring = sqlstring & " FROM GLAMF AS B RIGHT JOIN GLPOST AS A
ON B.ACCTID = A.ACCTID"
sqlstring = sqlstring & " WHERE A.FISCALPERD='" & Format(period,
"00") & "' AND A.FISCALYR = '" & Format(fiscalyr, "0000") & "' AND
B.ACCTGRPCOD='02'"
Set db = CurrentDb
Set rst = db.OpenRecordset(sqlstring)

i = 0
Do While Not rst.EOF
sqlstring1 = "SELECT A.ACCTID, A.FISCALYR, A.FISCALPERD,
A.SRCELEDGER, A.SRCETYPE, A.POSTINGSEQ, A.CNTDETAIL, A.JRNLDATE, A.BATCHNBR,
A.ENTRYNBR, A.TRANSNBR, A.JNLDTLDESC, A.JNLDTLREF, A.TRANSAMT"
sqlstring1 = sqlstring1 & " FROM tblCapexDetails AS A"
sqlstring1 = sqlstring1 & " WHERE A.POSTINGSEQ = " &
rst.Fields("PostingSEQ").Value & " AND A.CNTDETAIL = " &
rst.Fields("CNTDETAIL").Value
Set rst1 = db.OpenRecordset(sqlstring1)

rst1.MoveLast
rst1.MoveFirst

If rst1.RecordCount = 0 Then
With rst1
.AddNew
For x = 0 To rst1.Fields.Count - 1
rst1.Fields(x).Value =
rst.Fields(rst1.Fields(x).Name).Value
Next
.Update
i = i + 1
End With
End If
rst.MoveNext
Loop
MsgBox "There were " & i & " new records added."
Else
MsgBox "Invalid fiscal year"
End If

Else
MsgBox "Invalid period"
End If
End Sub

 

[Klatuu]

This would be a lot faster and easier using an Append query with a subquery
to append records not already in tblCapexDetails. You would use a Where
statement similar to:

WHERE NOT IN (SELECT POSTINGSEQ, CNTDETAIL FROM tblCapexDetails);

to filter out records that are already in the table.

 

[Steven Cheng]

thanks but can you confirm that it should be:

WHERE (A.POSTINGSEQ NOT IN (SELECT POSTINGSEQ FROM tblCapexDetails) AND
(A.CNTDETAIL NOT IN (SELECT CNTDETAIL from tblCapexDetails))

can you combine the two in one IN lookup?

Steven

 

[Klatuu]

Yes, you are correct. My bad.