Removing Duplicates to Make New Table

[Talgo]

I have a table called ImpJobID which has been records imported from an Excel spreadsheet. It contains two fields called JobID & Description. When the table is imported from Excel, one JobID can appear multiple times in the ImpJobID table.

I would like to remove the duplicates using VBA & create a new table only containing one record for each JobID. So, if a JobID is in the ImpJobID table 5 times, I only want to show it once in the new table. Likewise, if a JobID is in the ImpJobID table once, then it should appear once in the new table.

I'm not sure how to go about this. Can anyone make a suggestion?

 

[MichaelRed]

Look into an Update Query with the JobId as a key field in hte New table. Many overlook the feature of an update query in this type of operation, so you may need recourse to the ubiquitous {F1} (a.k.a. HELP) to grasp some of the details.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Talgo]

Hi Michael,

I tried your suggestion but can not get it to work. I always get zero rows updated.

The only way I can get an update to work is when there are JobIDs in the new table's JobID field. However, my new table will not contain any JobID information initially. It's a blank table.

I'm trying to extact that info from the ImpJobID table into the new DB. However,the ImpJobID table contains the same JobID number multiple times. I want the JobID number to only appear once in the new table.

What am I doing wrong? Is an SQL SELECT DISTINCT statement a possible alternative?

 

[Talgo]

Michael,

I think I finally sorted this out myself. After some Help research & trial and error here's the solution I came up with:

Used a Make Table Query
Set the query property for Unique Values to "yes"
Established a query on the ImpJobID table
Set the Criteria in the query to "JobID"

Result: New table with desired fields & no duplicate records

I think setting the Unique Values property to "yes" is the same as the SQL SELECT DISTINCT statement. At least it seems to function that way.

Thanks for your help.

Don