Select Duplicates & Append?

[suess]

I have a table with a column labeled payee (table of invoice details). The payee column is a combo box with a list of possible payees (come from a separate table of payees) but they can add others also. I would like to be able to pick out the ones that are added and add them to the table of payees to reduce mistakes in the future. I am pretty sure I need two queries to do this but I can't figure it out. Pick out duplicates and then append or update? Thanks for any help.

I have just started using Access this year and still have a lot to learn!

Sue

 

[thouston]

Yes you do need 2 queries! [To make things clearer I'm calling your main table Invoice and the one with the definitive list of names Names.] Do something like this:

First query, use the 2 tables Invoice and Names, joined by payee name. Change the join properties so that you select everything from Invoice plus any from Names if present (double click on the join arrow to do this - you will need join type 2 or 3 depending on which table is first in your query). Select name twice (once from each table) and group by it. Use the criteria "Is Null" for the name from Names. This will result in a list of all different names that appear in Invoice but not in Names. Save this query.

Second query - this is the easy one! Simply use your first query in an Append query to append all the new names to your Names table (first having checked for any typos and silly entries .....)

Hope this is clear - easier to do than explain!