Using an Append query, but not adding duplicates

[Gambit17]

Hi,

I would like to use an append query that appends data from a union query into a table, but doesn't add duplicates based on a certain field.

I have been trying to use this code, but it doesnt seem to work.

INSERT INTO [Cotton13/14]
SELECT DISTINCT [Contracted Farmers].*
FROM [Contracted Farmers]
WHERE NOT (SELECT * FROM [Cotton13/14] FROM [Cotton13/14] AS [Cotton13/14] WHERE [Cotton13/14].[OF Codec] = [Contracted Farmers].[OF Codec])

Cotton13/14 is the table I want to append to and Contracted Farmers is the union query. However the code is giving me an error. Im not even sure if thats the way I wanna be doing it. Any suggestions would be great.

Thank you

 

[billmeye]

Try this:

INSERT INTO [Cotton13/14]
SELECT DISTINCT [Contracted Farmers].*
FROM [Contracted Farmers] LEFT JOIN [Cotton13/14] ON [Contracted Farmers].[OF Codec] = [Cotton13/14].[OF Codec]
WHERE ((([Cotton13/14].[OF Codec]) Is Null));

 

[Gambit17]

Hi I got it work doing this.

INSERT INTO [Cotton13/14] ( Station, AC, [Field Officer], [FO Code], [Lead Farmer], [LF Code], [LF Codec], [Organic Farmer], [OF Code], [OF Codec], Acre, [Yield Est], [Sub-County], Village, Contracted )
SELECT
FROM [Contracted Farmers]
WHERE (((Exists (Select * FROM [Cotton13/14] WHERE [Contracted Farmers].[OF Codec] = [Cotton13/14].[OF Codec]))=False));

 

[billmeye]

That's good. Using a query inside a query could begin to get slow the larger your recordset as it needs to resolve the second query for each row in the main query and that means it needs to go through all your records in the table over and over and over again.

 

[Gambit17]

Try this:

INSERT INTO [Cotton13/14]
SELECT DISTINCT [Contracted Farmers].*
FROM [Contracted Farmers] LEFT JOIN [Cotton13/14] ON [Contracted Farmers].[OF Codec] = [Cotton13/14].[OF Codec]
WHERE ((([Cotton13/14].[OF Codec]) Is Null));

Thanks Billmeye. What does SELECT DISTINCT do? Could you maybe explain your code to me in laymens terms if its not too much hassle.

Is yours more efficient?

Thanks

 

[RainLover]

Thanks Billmeye. What does SELECT DISTINCT do? Could you maybe explain your code to me in laymens terms if its not too much hassle.

Is yours more efficient?

Thanks

Some questions like this one cannot be answered too easilly.

The simple answer is that it does not allow duplicates.

I would however suggest that you search Access for a comprehensive answer to your question.

I have often heard that Help is no Help, but when you do find what you want to know about the answer is quite comprehensive.

 

[billmeye]

Thanks Rainlover for jumping in. In this case I'm sure it is even needed but I didn't know how Gambit17's data was structured since his ultimate goal was not to have duplicates.

 

[RainLover]

Thanks Rainlover for jumping in. In this case I'm sure it is even needed but I didn't know how Gambit17's data was structured since his ultimate goal was not to have duplicates.

The Yellow colour is noy posible to be read on my screen so this is an obstacle for me.

Also he is using a Union Query. This usually means problems with the structure. (Not always but most of the time)

So I am happy to hand back to you if you think you can help.