Getting unique records from a make table query

[Xzibit7]

I am trying to get non-duplicate records for the fields in a make table query in an automated fashion. I have looked at Microsoft's suggestion of copying the table to structure only and then making the field that I do not want duplicates the primary key and then appending the tables in a query. This does not seem to work on a table made by a make table query though. When I click run on the query it does nothing at all. Does anyone have any ideas. Thanks

 

[jedraw]

Xzibit7,

That will work with the following change.

1. Create the make table query, let's say you're creating Newtable with fields f1, f2, f3.

2. When you create the Make table query, set a criteria for one of the fields to be something that won't be found.
eg criteria for f1 is 1=2

3. Execute the query; No records will be found but the query will run and the table will be created.

4. Go to Table design for NewTable. Set the field you want no duplicates in , say f1, to be the primary key.

5. Go to the MakeTable query -- design -- and make this an append query. This will ONLY insert (append) records to the table NewTable.

6. Execute the Append query. records will be found, and inserted into Newtable. Some may not be added because they violate the primary key constraint.

Result is NewTable with your data without duplicates.


Good luck.

 

[Xzibit7]

Okay I am lost on step 5 I make an append query with "newtable" as my table to append to and then click on all the items in this table and I get nothing when I Execute. What Table/Query should I select from show table in my query design view screen?

 

[Xzibit7]

Okay I think I got it..I made a table that had all the fields that I would use in the query and set the primary key. Then I made a query that would give me all the information that I wanted duplicates included. Then I made an Append query for for the last query to the original table. Now if I can only figure out a way to automate this. Thanks for all your help.