append only if [status] is different

[Guest]

I have the following APPEND QUERY:

INSERT INTO tbl_archive ( [Loan Acct #], status, PopEnterDt )
SELECT tbl_history.[Loan Acct #], tbl_history.status, tbl_history.PopEnterDt
FROM tbl_history;

however, i only want a record from the tbl_history to appended to the
tbl_archive ONLY if the status for the particular [account number] is
different. how can i do this?

thanks in advance,
geebee


thanks

 

[OfficeDev18 via AccessMonster.com]

What's stopping you from using the "WHERE tbl_history.status......" clause?

Sam

I have the following APPEND QUERY:

INSERT INTO tbl_archive ( [Loan Acct #], status, PopEnterDt )
SELECT tbl_history.[Loan Acct #], tbl_history.status, tbl_history.PopEnterDt
FROM tbl_history;

however, i only want a record from the tbl_history to appended to the
tbl_archive ONLY if the status for the particular [account number] is
different. how can i do this?

thanks in advance,
geebee

thanks

 

[Guest]

I am already using this:

INSERT INTO tbl_archive ( [Loan Acct #], status, PopEnterDt )
SELECT tbl_history.[Loan Acct #], tbl_history.status, tbl_history.PopEnterDt
FROM tbl_history LEFT JOIN Tbl_archive ON Tbl_history.[Loan Acct
#]=Tbl_archive.[Loan Acct #]
WHERE (((Tbl_archive.status)<>tbl_history.status));

However, all records are being appended from tbl_history into tbl_archive
EVEN if the record already exists in tbl_archive. For example, if loan
account number 456 with a status of 5 exists in tbl_history, and that exact
same record exists in tbl_archive, that record is still being appended to the
tbl_archive. This should NOT be happening. What am I doing wrong?

thanks in advance,
geebee

What's stopping you from using the "WHERE tbl_history.status......" clause?

Sam

I have the following APPEND QUERY:

INSERT INTO tbl_archive ( [Loan Acct #], status, PopEnterDt )
SELECT tbl_history.[Loan Acct #], tbl_history.status, tbl_history.PopEnterDt
FROM tbl_history;

however, i only want a record from the tbl_history to appended to the
tbl_archive ONLY if the status for the particular [account number] is
different. how can i do this?

thanks in advance,
geebee

thanks

 

[OfficeDev18 via AccessMonster.com]

Oh, OK. Try changing your join as follows:

LEFT JOIN Tbl_archive ON (Tbl_history.[Loan Acct #]=Tbl_archive.[Loan Acct #])
And (Tbl_history.status = Tbl_archive.status). Then change the WHERE clause
to:

WHERE Tbl_archive.status Is Null;

See if this helps.

Sam

I am already using this:

INSERT INTO tbl_archive ( [Loan Acct #], status, PopEnterDt )
SELECT tbl_history.[Loan Acct #], tbl_history.status, tbl_history.PopEnterDt
FROM tbl_history LEFT JOIN Tbl_archive ON Tbl_history.[Loan Acct
#]=Tbl_archive.[Loan Acct #]
WHERE (((Tbl_archive.status)<>tbl_history.status));

However, all records are being appended from tbl_history into tbl_archive
EVEN if the record already exists in tbl_archive. For example, if loan
account number 456 with a status of 5 exists in tbl_history, and that exact
same record exists in tbl_archive, that record is still being appended to the
tbl_archive. This should NOT be happening. What am I doing wrong?

thanks in advance,
geebee

What's stopping you from using the "WHERE tbl_history.status......" clause?

[quoted text clipped - 14 lines]