Insert Into Where account numbers are inequal

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Office 2003 on Excel XP.

I need to append all records from T1 INTO T2, if [T2].[Period] = 2 and the
account number appears in T1 but NOT in T2. Would the following SQL do the
trick?

INSERT INTO [T2] SELECT * FROM [T1] WHERE [T1].[Period] = 2 AND
[T2].Account <> [T1].Account

Can someone please help me out? Thanks a lot in advance.
 
You need to join the two tables, or use a Not In.

(NOTE: both of the following are untested air queries!)

INSERT INTO [T2]
SELECT * FROM [T1], [T2]
WHERE [T1].[Period] = 2 AND
[T2].Account <> [T1].Account

or

INSERT INTO [T2]
SELECT * FROM [T1] WHERE [T1].[Period] = 2 AND
[T1].Account NOT IN (SELECT Account FROM [T2])
 
I am using Office 2003 on Excel XP.

I need to append all records from T1 INTO T2, if [T2].[Period] = 2 and the
account number appears in T1 but NOT in T2. Would the following SQL do the
trick?

INSERT INTO [T2] SELECT * FROM [T1] WHERE [T1].[Period] = 2 AND
[T2].Account <> [T1].Account

Can someone please help me out? Thanks a lot in advance.
Click to expand...

This won't work: it will find every record in T1 for which there is
ANY record in T2 that doesn't match!

Instead, use a "Frustrated Outer Join":

INSERT INTO T2(field, field, field, ...)
SELECT T1.field, T1.field, T1.field
FROM T1 LEFT JOIN T2
ON T1.Account = T2.Account
WHERE T2.Account IS NULL
AND T1.Period = 2;


John W. Vinson[MVP]
 
Thanks, I will give this a try tomorrow!

John Vinson said:
I am using Office 2003 on Excel XP.

I need to append all records from T1 INTO T2, if [T2].[Period] = 2 and the
account number appears in T1 but NOT in T2. Would the following SQL do the
trick?

INSERT INTO [T2] SELECT * FROM [T1] WHERE [T1].[Period] = 2 AND
[T2].Account <> [T1].Account

Can someone please help me out? Thanks a lot in advance.
Click to expand...

This won't work: it will find every record in T1 for which there is
ANY record in T2 that doesn't match!

Instead, use a "Frustrated Outer Join":

INSERT INTO T2(field, field, field, ...)
SELECT T1.field, T1.field, T1.field
FROM T1 LEFT JOIN T2
ON T1.Account = T2.Account
WHERE T2.Account IS NULL
AND T1.Period = 2;


John W. Vinson[MVP]
Click to expand...
 
Thanks, Douglas.

I will give yours and John's a try and keep the one that runs fastest. I
plan to keep all solutions in my code library.

Thanks again.

Douglas J. Steele said:
You need to join the two tables, or use a Not In.

(NOTE: both of the following are untested air queries!)

INSERT INTO [T2]
SELECT * FROM [T1], [T2]
WHERE [T1].[Period] = 2 AND
[T2].Account <> [T1].Account

or

INSERT INTO [T2]
SELECT * FROM [T1] WHERE [T1].[Period] = 2 AND
[T1].Account NOT IN (SELECT Account FROM [T2])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



XP said:
I am using Office 2003 on Excel XP.

I need to append all records from T1 INTO T2, if [T2].[Period] = 2 and the
account number appears in T1 but NOT in T2. Would the following SQL do the
trick?

INSERT INTO [T2] SELECT * FROM [T1] WHERE [T1].[Period] = 2 AND
[T2].Account <> [T1].Account

Can someone please help me out? Thanks a lot in advance.
Click to expand...
Click to expand...
 
Back
Top