duplicate records

[Guest]

hi,

i have the following:

Dim strSQLCommand6 As String
strSQLCommand6 = "INSERT INTO tbl_masterpop_main " _
& "([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 [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?] " _
& "FROM up_tbl_masterpop_main;"

DoCmd.RunSQL strSQLCommand6

when i click the button, duplicate records ar ebeing coped into
tbl_masterpop_main, even if the [loan acct #] exists already in the
tbl_masterpop_main. what do I need to do?

for referecne, here is the up_tbl_masterpop_main query:
SELECT "In Table 1" AS Source
, 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?]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, 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?]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;


thanks in advance,
geebee

 

[Tom Ellison]

Dear GB:

And hello again.

I recommend you get a working query going first, then create code. The
query you show would look like this:

INSERT INTO tbl_masterpop_main ([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 [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?]
FROM up_tbl_masterpop_main;

Now to simplify further, lets get a SELECT query alone that doesn't contain
those rows already in tbl_masterpop_main according to values in [Loan Acct
#].

SELECT [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?]
FROM up_tbl_masterpop_main
WHERE [Loan Acct #] NOT IN
(SELECT [Loan Acct #]
FROM tbl_masterpop_main)

First run the above query without the WHERE clause, and record the number of
rows in up_tbl_masterpop_main. Then run all of it and record the number of
rows. Any that would "duplicate" the [Loan Acct #] will be omitted.

A good question might be what to do with all the information in
up_tbl_masterpop_main that is not appended. It may have values in other
columns that differ from what is already in tbl_masterpop_main. Do you want
to ignore these differences, or do you want to update them, or what?

When the SELECT query is working correctly, you can add the INSERT clause
and convert the finished, working query back into code. I recommend you use
this process in the future. Get the query working first, then make code
from it.

Tom Ellison

hi,

i have the following:

Dim strSQLCommand6 As String
strSQLCommand6 = "INSERT INTO tbl_masterpop_main " _
& "([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 [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?] " _
& "FROM up_tbl_masterpop_main;"

DoCmd.RunSQL strSQLCommand6

when i click the button, duplicate records ar ebeing coped into
tbl_masterpop_main, even if the [loan acct #] exists already in the
tbl_masterpop_main. what do I need to do?

for referecne, here is the up_tbl_masterpop_main query:
SELECT "In Table 1" AS Source
, 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?]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, 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?]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;


thanks in advance,
geebee

 

[Vincent Johns]

I was looking at another message from you, and thought of trying to
answer it, but I really like what Tom Ellison suggested here. Your code
looked kind of complex (and repetitious) to me, and since Access
provides a very convenient Query Design View for developing and editing
many types of Queries, I usually try to use a named Query in my code
instead of expressing the raw SQL there. (Sometimes one must construct
the SQL inside a Module, but I don't think that's the case in the
example you cited.)

Having constructed named Queries to do what you want, you can do
something like DoCmd.OpenQuery in your VBA code and make the code much
shorter and therefore easier to read and understand. So in a sense I
agree with Tom about making code from your Query (after your Query is
working), but my suggestion is to do that by reference if you can, just
invoking the named Query in your code instead of reproducing it there.
And if you later need to revise the Query, you can do so in Query Design
View, which is not an option for you if the SQL is buried in your VBA.

BTW, not everyone agrees with me -- I know that some people prefer, for
example, to attach SQL directly to controls on Forms, instead of
invoking named Queries there, on the basis that having a couple of
hundred named Queries in your database can be confusing. They have a
point, but you can keep the confusion under control by having a simple,
clear system for naming your Queries and remembering to fill in the
"Description" property for each one. (For example, all of the named
Queries invoked by a Form called [F_CustInfo] might have names beginning
with "Q_CustInfo_" followed by the name of a control on that Form. Or
whatever works for you and whoever else has to maintain your database.)

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

Dear GB:

And hello again.

I recommend you get a working query going first, then create code. The
query you show would look like this:

INSERT INTO tbl_masterpop_main ([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 [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?]
FROM up_tbl_masterpop_main;

Now to simplify further, lets get a SELECT query alone that doesn't contain
those rows already in tbl_masterpop_main according to values in [Loan Acct
#].

SELECT [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?]
FROM up_tbl_masterpop_main
WHERE [Loan Acct #] NOT IN
(SELECT [Loan Acct #]
FROM tbl_masterpop_main)

First run the above query without the WHERE clause, and record the number of
rows in up_tbl_masterpop_main. Then run all of it and record the number of
rows. Any that would "duplicate" the [Loan Acct #] will be omitted.

A good question might be what to do with all the information in
up_tbl_masterpop_main that is not appended. It may have values in other
columns that differ from what is already in tbl_masterpop_main. Do you want
to ignore these differences, or do you want to update them, or what?

When the SELECT query is working correctly, you can add the INSERT clause
and convert the finished, working query back into code. I recommend you use
this process in the future. Get the query working first, then make code
from it.

Tom Ellison

hi,

i have the following:

Dim strSQLCommand6 As String
strSQLCommand6 = "INSERT INTO tbl_masterpop_main " _
& "([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 [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?] " _
& "FROM up_tbl_masterpop_main;"

DoCmd.RunSQL strSQLCommand6

when i click the button, duplicate records ar ebeing coped into
tbl_masterpop_main, even if the [loan acct #] exists already in the
tbl_masterpop_main. what do I need to do?

for referecne, here is the up_tbl_masterpop_main query:
SELECT "In Table 1" AS Source
, 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?]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, 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?]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;


thanks in advance,
geebee

 

[Tom Ellison]

Dear Vincent:

I agree strongly that making queries modular and in keeping a set of saved
queries that are re-usable.

When writing a query, I always ask myself whether this query is likely to be
re-usable, or if it contains portions that are likely to be re-usable. All
re-usable portions should be saved queries.

I do not recommend just always making every query a saved query, but doing
so judiciously.

This was not the exact sense in which I gave the advice here. I wasn't
talking about saving the query steps along the way, but rather in building
what you need in easy steps, and testing them. This does not detract from
the wisdom of Vincent's advice.

Tom Ellison

I was looking at another message from you, and thought of trying to answer
it, but I really like what Tom Ellison suggested here. Your code looked
kind of complex (and repetitious) to me, and since Access provides a very
convenient Query Design View for developing and editing many types of
Queries, I usually try to use a named Query in my code instead of
expressing the raw SQL there. (Sometimes one must construct the SQL inside
a Module, but I don't think that's the case in the example you cited.)

Having constructed named Queries to do what you want, you can do something
like DoCmd.OpenQuery in your VBA code and make the code much shorter and
therefore easier to read and understand. So in a sense I agree with Tom
about making code from your Query (after your Query is working), but my
suggestion is to do that by reference if you can, just invoking the named
Query in your code instead of reproducing it there. And if you later need
to revise the Query, you can do so in Query Design View, which is not an
option for you if the SQL is buried in your VBA.

BTW, not everyone agrees with me -- I know that some people prefer, for
example, to attach SQL directly to controls on Forms, instead of invoking
named Queries there, on the basis that having a couple of hundred named
Queries in your database can be confusing. They have a point, but you can
keep the confusion under control by having a simple, clear system for
naming your Queries and remembering to fill in the "Description" property
for each one. (For example, all of the named Queries invoked by a Form
called [F_CustInfo] might have names beginning with "Q_CustInfo_" followed
by the name of a control on that Form. Or whatever works for you and
whoever else has to maintain your database.)

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

Dear GB:

And hello again.

I recommend you get a working query going first, then create code. The
query you show would look like this:

INSERT INTO tbl_masterpop_main ([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 [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?]
FROM up_tbl_masterpop_main;

Now to simplify further, lets get a SELECT query alone that doesn't
contain those rows already in tbl_masterpop_main according to values in
[Loan Acct #].

SELECT [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?]
FROM up_tbl_masterpop_main
WHERE [Loan Acct #] NOT IN
(SELECT [Loan Acct #]
FROM tbl_masterpop_main)

First run the above query without the WHERE clause, and record the number
of rows in up_tbl_masterpop_main. Then run all of it and record the
number of rows. Any that would "duplicate" the [Loan Acct #] will be
omitted.

A good question might be what to do with all the information in
up_tbl_masterpop_main that is not appended. It may have values in other
columns that differ from what is already in tbl_masterpop_main. Do you
want to ignore these differences, or do you want to update them, or what?

When the SELECT query is working correctly, you can add the INSERT clause
and convert the finished, working query back into code. I recommend you
use this process in the future. Get the query working first, then make
code from it.

Tom Ellison

hi,

i have the following:

Dim strSQLCommand6 As String
strSQLCommand6 = "INSERT INTO tbl_masterpop_main " _
& "([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 [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?] " _
& "FROM up_tbl_masterpop_main;"

DoCmd.RunSQL strSQLCommand6

when i click the button, duplicate records ar ebeing coped into
tbl_masterpop_main, even if the [loan acct #] exists already in the
tbl_masterpop_main. what do I need to do?

for referecne, here is the up_tbl_masterpop_main query:
SELECT "In Table 1" AS Source
, 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?]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, 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?]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;


thanks in advance,
geebee