Append if Not Exist
in [Queries]
|
Prev: You tried to execute a query that does not include the specified e
Next: Building/Storing a passthrough:How to assign connection string andmake passthrough
From: QB on 20 Apr 2010 18:06 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
From: Marshall Barton on 20 Apr 2010 18:14 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]
From: Jeff Boyce on 20 Apr 2010 18:20 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" <QB(a)discussions.microsoft.com> wrote in message news:A8228381-00E0-45A4-A16C-15E4E1454B1A(a)microsoft.com... >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 >
From: QB on 20 Apr 2010 19:05 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] > . >
From: QB on 20 Apr 2010 20:38
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" <QB(a)discussions.microsoft.com> wrote in message > news:A8228381-00E0-45A4-A16C-15E4E1454B1A(a)microsoft.com... > >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 > > > > > . > |