Append query?

[alex]

Hello Experts,

I have an Append query that takes data from one table and puts it into
another.

I only want the query to insert rows, however, where two records are
distinct.

I've tried using the QBE and writing some SQL code.

Something like this:

Insert into Table_A
Select Table_B.*

From Table_B

Where
Table_A.cust_no <> Table_B.cust_no
And
Table_A.empl_no <> Table_B.empl_no

Does anyone have any ideas?

I'm basically trying to use an Append query to back up a table. I
only want the query, however, to append new records.

alex

 

[Allen Browne]

Try something like this:

INSERT INTO [Table_A]
SELECT [Table_B].*
FROM [Table_B]
WHERE NOT EXISTS
(SELECT Dupe.[cust_no]
FROM [Table_A] AS Dupe
WHERE Dupe.[cust_no] = [Table_B].[cust_no]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[alex]

Try something like this:

INSERT INTO [Table_A]
SELECT [Table_B].*
FROM [Table_B]
WHERE NOT EXISTS
(SELECT Dupe.[cust_no]
FROM [Table_A] AS Dupe
WHERE Dupe.[cust_no] = [Table_B].[cust_no]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hello Experts,

I have an Append query that takes data from one table and puts it into
another.

I only want the query to insert rows, however, where two records are
distinct.

I've tried using the QBE and writing some SQL code.

Something like this:

Insert into Table_A
Select Table_B.*
Where
Table_A.cust_no <> Table_B.cust_no
And
Table_A.empl_no <> Table_B.empl_no

Does anyone have any ideas?

I'm basically trying to use an Append query to back up a table. I
only want the query, however, to append new records.

alex- Hide quoted text -

- Show quoted text -

Thanks Allen for the response.

Am I able to add a second criteron to the SQL statement. I've tried
and it's asking me for a parameter value.

INSERT INTO [Table_A]
SELECT [Table_B].*
FROM [Table_B]
WHERE NOT EXISTS
(SELECT Dupe.[cust_no], Dupe.[empl_no]
FROM [Table_A] AS Dupe
WHERE Dupe.[cust_no] = [Table_B].[cust_no]
AND Dupe.[empl_no] = [Table_B].empl_no]);

 

[Allen Browne]

If it asks for a parameter, it means Access is not able to find the field
you named.

By taking careful note of the parameter name requested, you know what to
look for. Perhaps there is a space in the name, or some slight variation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Try something like this:

INSERT INTO [Table_A]
SELECT [Table_B].*
FROM [Table_B]
WHERE NOT EXISTS
(SELECT Dupe.[cust_no]
FROM [Table_A] AS Dupe
WHERE Dupe.[cust_no] = [Table_B].[cust_no]);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Hello Experts,

I have an Append query that takes data from one table and puts it into
another.

I only want the query to insert rows, however, where two records are
distinct.

I've tried using the QBE and writing some SQL code.

Something like this:

Insert into Table_A
Select Table_B.*
From Table_B
Where
Table_A.cust_no <> Table_B.cust_no
And
Table_A.empl_no <> Table_B.empl_no

Does anyone have any ideas?

I'm basically trying to use an Append query to back up a table. I
only want the query, however, to append new records.

alex- Hide quoted text -

- Show quoted text -

Thanks Allen for the response.

Am I able to add a second criteron to the SQL statement. I've tried
and it's asking me for a parameter value.

INSERT INTO [Table_A]
SELECT [Table_B].*
FROM [Table_B]
WHERE NOT EXISTS
(SELECT Dupe.[cust_no], Dupe.[empl_no]
FROM [Table_A] AS Dupe
WHERE Dupe.[cust_no] = [Table_B].[cust_no]
AND Dupe.[empl_no] = [Table_B].empl_no]);