Insert with Select and DISTINCT

[mud2]

It gets worse and worse! One step forward, 6 back!
So, I have two One-field tables, TableA and TableB. Each table's field is named This_Name, and each is Text.

I want to add those names from one table to the names in the other, with no duplicates:
"INSERT INTO TableB SELECT DISTINCT This_name From TableA" Copys the whole D___ table
If I use a WHERE clause instead of DISTINCT...Where TableA.This_Name <> TableB.This_Name, ACCESS treats one of These fields as a parameter!

Think I'll go eat worms!

[OTWarrior]

Why not copy both tables into one excel spreadsheet. Then import that sheet into access as a table (lets call it "tbl_excelImport")

create a make-table query, where the name field is set to group, which will group together all the names that are the same.

SELECT tbl_excelImport.This_Name INTO tbl_TableC
FROM tbl_excelImport
GROUP BY tbl_excelImport.This_Name;


Hope that helps

[CreganTur]

If you don't want duplicates to carry over then there's a simple way to deal with this.

Make your This_Name field the primary key for both tables. Then use a standard append query to move all of the data from one table to the next. Access will automatically discard all records that are duplicates and import the others.

This sounds like a bad design issue.

[mud2]

"...when she was good she was very good, but when she was bad she was HORRID.."
Just like this GD'D SQL! I give up...back to VBA and transparent programming!

Thanks randy anyway. If I can't do a simple insert into select distinct.....I'll NEVER be able to do an UPDATE With conditions yet!

[CreganTur]

SQL can be a very unforgiving and picky language... I often call it the "fussy old lady" of programming languages.

I think I've found out what your issue is, and I hope my explination will help you out.

Here's the SQL that should work for you:
INSERT INTO TableB
SELECT TableA.This_Name
FROM TableA LEFT JOIN TableB ON TableA.This_Name = TableB.This_Name
WHERE (((TableB.This_Name) Is Null));


First of all, before you can even think about making an Append query, you have to build a Find Unmatched query- this consists of everything above except for the INSERT INTO statement. It shows all of the records that are unique to TableA. This is good- it's what you want, since these are the records you are going to want to move into TableB. (I may have my TableA and TableB backwards from what you're wanting- just adjust accordingly).

Once you're certain you're getting the correct records, then you can add the INSERT INTO statement, which transforms your SELECT query into an Append Query.

You probably didn't JOIN your tables together- that's why you were getting a parameter popup.

HTH:thumb

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: in my example my tables did not have duplicate entries. If you have duplicates, then just add in the DISTINCT keyword.