Append if Not Exist

I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that do
not already exist in the table already. How would I go about this?

Thank you,

QB

QB wrote:

I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that do
not already exist in the table already.


INSERT INTO tbl_Clients ( ClientName )
SELECT DISTINCT Tmp_Imp.[Client Name]
FROM Tmp_Imp LEFT JOIN tbl_Clients
ON Tmp_Imp.[Client Name] = tbl_Clients.ClientName
WHERE tbl_Clients.ClientName Is Null

--
Marsh
MVP [MS Access]

One way to accomplish this would be to add an index to the underlying table
into which you are trying to insert. The index would be on the field that
you with not to duplicate, and would require unique values.

But I'm concerned that if you are inserting [Client Name], what is supposed
to happen when you have two "John Smith"s as clients? And it looks like
you're putting a full name in the field ... you're never going to need to
sort by lastname, then? g

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that do
not already exist in the table already. How would I go about this?

Thank you,

QB

Marshall,

Your SQl Statement works perfectly! Thank you. Now I just need to try and
understand it so I can stop asking these types of questions.

Could you possibly explain the Join/Where clause, if it isn't too much to ask.

QB






"Marshall Barton" wrote:

QB wrote:

I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that do
not already exist in the table already.


INSERT INTO tbl_Clients ( ClientName )
SELECT DISTINCT Tmp_Imp.[Client Name]
FROM Tmp_Imp LEFT JOIN tbl_Clients
ON Tmp_Imp.[Client Name] = tbl_Clients.ClientName
WHERE tbl_Clients.ClientName Is Null

--
Marsh
MVP [MS Access]
.

In this instance, client's are companies so Client Name is actually a
business' name and as such duplication should never occur.

I like the idea of the unique index on the field. So if I do this and try
to insert using VBA I'm assuming it will still throw me an error which I will
need to ignore using proper error handling (Correct me if I am wrong).

Thank you for the idea,

QB





"Jeff Boyce" wrote:

One way to accomplish this would be to add an index to the underlying table
into which you are trying to insert. The index would be on the field that
you with not to duplicate, and would require unique values.

But I'm concerned that if you are inserting [Client Name], what is supposed
to happen when you have two "John Smith"s as clients? And it looks like
you're putting a full name in the field ... you're never going to need to
sort by lastname, then? g

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that do
not already exist in the table already. How would I go about this?

Thank you,

QB



.

When the tables are joined that way, you will get each
record in Tmp joined to its matching record in Clients or
nulls if there is no matching clients record. The Where
clause just selects the records in Tmp where there is no
matching record in Clients (the Where clause can check any
field that is guaranteed to be non null in Clients).
--
Marsh
MVP [MS Access]


QB wrote:
Your SQl Statement works perfectly! Thank you. Now I just need to try and
understand it so I can stop asking these types of questions.

Could you possibly explain the Join/Where clause, if it isn't too much to ask.


"Marshall Barton" wrote:
QB wrote:
I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that do
not already exist in the table already.


INSERT INTO tbl_Clients ( ClientName )
SELECT DISTINCT Tmp_Imp.[Client Name]
FROM Tmp_Imp LEFT JOIN tbl_Clients
ON Tmp_Imp.[Client Name] = tbl_Clients.ClientName
WHERE tbl_Clients.ClientName Is Null

When I use indexes, I also trap for the error and handle it. Sounds good!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
In this instance, client's are companies so Client Name is actually a
business' name and as such duplication should never occur.

I like the idea of the unique index on the field. So if I do this and try
to insert using VBA I'm assuming it will still throw me an error which I
will
need to ignore using proper error handling (Correct me if I am wrong).

Thank you for the idea,

QB





"Jeff Boyce" wrote:

One way to accomplish this would be to add an index to the underlying
table
into which you are trying to insert. The index would be on the field
that
you with not to duplicate, and would require unique values.

But I'm concerned that if you are inserting [Client Name], what is
supposed
to happen when you have two "John Smith"s as clients? And it looks like
you're putting a full name in the field ... you're never going to need to
sort by lastname, then? g

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
I created the following basic Append query

INSERT INTO tbl_Clients ( ClientName )
SELECT [Tmp_Imp].[Client Name]
FROM [Tmp_Imp]
GROUP BY [Tmp_Imp].[Client Name];

But now I need to add a twist, I need to only append those Clients that
do
not already exist in the table already. How would I go about this?

Thank you,

QB



.