DUPLICATE QUERY results

G

Guest

hi,

I have the following code behind a form button:

Dim strSQL1 As String
strSQL1 = "SELECT tbl_masterpop_new.[Loan Acct #], " _
& "tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt], " _
& "tbl_masterpop_new.[chg off date], " _
& "tbl_masterpop_new.[loan status], " _
& "tbl_masterpop_new.[inventory status], " _
& "tbl_masterpop_new.[active bk flag], " _
& "tbl_masterpop_new.[active inactive flag], " _
& "tbl_masterpop_new.[paid off?], " _
& "tbl_masterpop_new.[payoff date], " _
& "tbl_masterpop_new.[days delinquent], " _
& "tbl_masterpop_new.[amended this mo?], " _
& "tbl_masterpop_new.[extended this mo?] " _
& "INTO tbl_history FROM tbl_masterpop_new INNER JOIN tbl_masterpop " _
& "ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #] " _
& "WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status"
DoCmd.RunSQL strSQL1

Dim strSQL2 As String
strSQL2 = "SELECT tbl_masterpop.[Loan Acct #], tbl_masterpop.[status], " _
& "tbl_masterpop.[PopEnterDt], " _
& "tbl_masterpop.[chg off date], " _
& "tbl_masterpop.[loan status], " _
& "tbl_masterpop.[inventory status], " _
& "tbl_masterpop.[active bk flag], " _
& "tbl_masterpop.[active inactive flag], " _
& "tbl_masterpop.[paid off?], " _
& "tbl_masterpop.[payoff date], " _
& "tbl_masterpop.[days delinquent], " _
& "tbl_masterpop.[amended this mo?], " _
& "tbl_masterpop.[extended this mo?] " _
& "INTO tbl_history2 FROM tbl_masterpop " _
& "INNER JOIN tbl_masterpop_new ON tbl_masterpop.[Loan Acct #] = " _
& "tbl_masterpop_new.[Loan Acct #] WHERE " _
& "tbl_masterpop_new.Status <> tbl_masterpop.Status"
DoCmd.RunSQL strSQL2

Dim strSQL3 As String
strSQL3 = "INSERT INTO tbl_history([Loan Acct #], status, PopEnterDt, " _
& "[chg off date], [loan status], [inventory status], [active bk flag],
[active inactive flag], " _
& "[paid off?], [payoff date], [days delinquent], [amended this mo?],
[extended this mo?]) " _
& "SELECT tbl_history2.[Loan Acct #], tbl_history2.status, " _
& "tbl_history2.PopEnterDt, " _
& "tbl_history2.[chg off date], " _
& "tbl_history2.[loan status], " _
& "tbl_history2.[inventory status], " _
& "tbl_history2.[active bk flag], " _
& "tbl_history2.[active inactive flag], " _
& "tbl_history2.[paid off?], " _
& "tbl_history2.[payoff date], " _
& "tbl_history2.[days delinquent], " _
& "tbl_history2.[amended this mo?], " _
& "tbl_history2.[extended this mo?] " _
& "FROM tbl_history2 LEFT JOIN Tbl_history " _
& "ON (tbl_history2.status=Tbl_history.status) AND " _
& "(tbl_history2.[Loan Acct #]=Tbl_history.[Loan Acct #]) " _
& "WHERE (((tbl_history.PopEnterDt) Is Null) OR ((Tbl_history2.status) Is
Null));"
DoCmd.RunSQL strSQL3


WHen I click the button with this code behind it, I notice that it is
putting exactly 2 copies of every single [loan acct #], even if the [status]
is the same for that [loan acct #] from tbl_masterpop into tbl_history2.

Also putting exactly 2 copies of every single [loan acct #], even if the
[status] is the same for that [loan acct #] from tbl_masterpop into
tbl_history, and exactly 2 copies of every single [loan acct #], even if the
[status] is the same for that [loan acct #] from tbl_masterpop_new into
tbl_history.

then when I look at tbl_archives, i notice exactly 2 copies of every single
[loan acct #], even if the [status] is the same for that [loan acct #].

all I want here is for the [loan account #] to be appended to tbl_archive
for each [status] for that [loan acct #].

here is some data from tbl_archive:

Loan Acct # status
32401 PASTDUE_D61-90
32401 PASTDUE_D61-90
32401 PASTDUE_C31-60
32401 PASTDUE_C31-60

I only want ONE of row1 and ONE of row3.

what do I need to do now?

thanks in advance,
geebee
 
V

Vincent Johns

I think Tom Ellison's response to your later posting applies here.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
hi,

I have the following code behind a form button:

Dim strSQL1 As String
strSQL1 = "SELECT tbl_masterpop_new.[Loan Acct #], " _
& "tbl_masterpop_new.[status], tbl_masterpop_new.[PopEnterDt], " _
& "tbl_masterpop_new.[chg off date], " _
& "tbl_masterpop_new.[loan status], " _
& "tbl_masterpop_new.[inventory status], " _
& "tbl_masterpop_new.[active bk flag], " _
& "tbl_masterpop_new.[active inactive flag], " _
& "tbl_masterpop_new.[paid off?], " _
& "tbl_masterpop_new.[payoff date], " _
& "tbl_masterpop_new.[days delinquent], " _
& "tbl_masterpop_new.[amended this mo?], " _
& "tbl_masterpop_new.[extended this mo?] " _
& "INTO tbl_history FROM tbl_masterpop_new INNER JOIN tbl_masterpop " _
& "ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #] " _
& "WHERE tbl_masterpop.Status <> tbl_masterpop_new.Status"
DoCmd.RunSQL strSQL1

Dim strSQL2 As String
strSQL2 = "SELECT tbl_masterpop.[Loan Acct #], tbl_masterpop.[status], " _
& "tbl_masterpop.[PopEnterDt], " _
& "tbl_masterpop.[chg off date], " _
& "tbl_masterpop.[loan status], " _
& "tbl_masterpop.[inventory status], " _
& "tbl_masterpop.[active bk flag], " _
& "tbl_masterpop.[active inactive flag], " _
& "tbl_masterpop.[paid off?], " _
& "tbl_masterpop.[payoff date], " _
& "tbl_masterpop.[days delinquent], " _
& "tbl_masterpop.[amended this mo?], " _
& "tbl_masterpop.[extended this mo?] " _
& "INTO tbl_history2 FROM tbl_masterpop " _
& "INNER JOIN tbl_masterpop_new ON tbl_masterpop.[Loan Acct #] = " _
& "tbl_masterpop_new.[Loan Acct #] WHERE " _
& "tbl_masterpop_new.Status <> tbl_masterpop.Status"
DoCmd.RunSQL strSQL2

Dim strSQL3 As String
strSQL3 = "INSERT INTO tbl_history([Loan Acct #], status, PopEnterDt, " _
& "[chg off date], [loan status], [inventory status], [active bk flag],
[active inactive flag], " _
& "[paid off?], [payoff date], [days delinquent], [amended this mo?],
[extended this mo?]) " _
& "SELECT tbl_history2.[Loan Acct #], tbl_history2.status, " _
& "tbl_history2.PopEnterDt, " _
& "tbl_history2.[chg off date], " _
& "tbl_history2.[loan status], " _
& "tbl_history2.[inventory status], " _
& "tbl_history2.[active bk flag], " _
& "tbl_history2.[active inactive flag], " _
& "tbl_history2.[paid off?], " _
& "tbl_history2.[payoff date], " _
& "tbl_history2.[days delinquent], " _
& "tbl_history2.[amended this mo?], " _
& "tbl_history2.[extended this mo?] " _
& "FROM tbl_history2 LEFT JOIN Tbl_history " _
& "ON (tbl_history2.status=Tbl_history.status) AND " _
& "(tbl_history2.[Loan Acct #]=Tbl_history.[Loan Acct #]) " _
& "WHERE (((tbl_history.PopEnterDt) Is Null) OR ((Tbl_history2.status) Is
Null));"
DoCmd.RunSQL strSQL3


WHen I click the button with this code behind it, I notice that it is
putting exactly 2 copies of every single [loan acct #], even if the [status]
is the same for that [loan acct #] from tbl_masterpop into tbl_history2.

Also putting exactly 2 copies of every single [loan acct #], even if the
[status] is the same for that [loan acct #] from tbl_masterpop into
tbl_history, and exactly 2 copies of every single [loan acct #], even if the
[status] is the same for that [loan acct #] from tbl_masterpop_new into
tbl_history.

then when I look at tbl_archives, i notice exactly 2 copies of every single
[loan acct #], even if the [status] is the same for that [loan acct #].

all I want here is for the [loan account #] to be appended to tbl_archive
for each [status] for that [loan acct #].

here is some data from tbl_archive:

Loan Acct # status
32401 PASTDUE_D61-90
32401 PASTDUE_D61-90
32401 PASTDUE_C31-60
32401 PASTDUE_C31-60

I only want ONE of row1 and ONE of row3.

what do I need to do now?

thanks in advance,
geebee
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

duplicate records 3
query error 1
unwanted duplicate loan account numbers 1
query differences 1
query not acting right 5
combine 2 access queries 3
query speed 5
delete query 1

Top