Append if Not Exist

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

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
 
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.
 
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 said:
QB said:
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.
Click to expand...


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
Click to expand...
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top