CODEWITHSUNDEEP
sqlms-accessjoin
s.bramblet
s.bramblet

Reputation: 199

How do I add records from one table missing in another table : MS Access

Ok this should be easy.

As an example, I have a table (TABLE 1) populated with the following records:

Account#  TrxDate  Description  Amount
123456    20130719  trx-1       500.00
123456    20130819  trx-2       500.00

I have a second table (TABLE 2) populated with the following records:

Account#  TrxDate  Description  Amount
123456    20130719  trx-A       500.00
123456    20130819  trx-B       500.00
123456    20130919  trx-C       500.00

I want to add the last record in TABLE 2 to TABLE 1.

I thought I could do this using WHERE NOT EXISTS but that isn't getting the job done.

Here is an example of what I was trying that didn't work:

INSERT INTO Table1 (Account#, TrxDate, Description, Amount)
SELECT Account#, TrxDate, Description, Amount
FROM Table2
WHERE NOT EXISTS
(SELECT * FROM Table1 WHERE Table1.Account# = Table2.Account# AND Table1.TrxDate = Table2.TrxDate AND Table1.Amount = Table2.Amount)

Any suggestions? Thanks in advance!

Upvotes: 0

Views: 2111

Answers (3)

s.bramblet
s.bramblet

Reputation: 199

Thanks for the responses! My bad on this one... I suspected this might be an issue but I wasn't sure.

The TrxDate field on Table1 was DATE/TIME and the TrxDate on Table2 was NUMERIC (LONG INT). I was using a function to convert the DATE/TIME value to Numeric in the subquery part of WHERE NOT EXISTS.

Apparently this wasn't working. I converted both values to NUMERIC and ran the exact same query I had tried initially and it worked. Whew!

Again, thanks for the responses... if I run into more issues on this one I will definitely be giving those a try.

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

insert into table1 
SELECT TOP 1 Account#,trxdate,description,amount FROM table2
ORDER BY trxdate DESC;

Upvotes: 1

Amar Mishra
Amar Mishra

Reputation: 89

Try this

insert into Table_1
select Table_2.* from Table_1 
right outer join Table_2 on Table_1.Account#=Table_2.Account# and Table_1.TrxDate=Table_2.TrxDate and Table_1.Amount=Table_2.Amount
where Table_1.Account# is null

Upvotes: 1

Related Questions