Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Access - Deduplicate on multiple fields

I have attempted to use a SELECT DISTINCT query on, de-duplicating on three fields, without success.

I have a table, with the following three fields:

Favorite_Shoe        Last_Name     First_Name

I want to de-cuplicate records, based on unique values of all three fields, using a SQL DISTINCT query

Thanks
0
tahirih
Asked:
tahirih
  • 7
  • 6
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try

select distinct [Favorite_Shoe] & [Last_Name] & [First_Name], Favorite_Shoe, Last_Name,First_Name
from tableX
0
 
tahirihAuthor Commented:
I get an extra [Expression Column]

I am also comparing these results with a De-Duplicate in Excel, and am not getting the same results.

Please advise, and how to not include the [Expression]

Thanks
0
 
tahirihAuthor Commented:
Additionally, I would like to include additional fields in the table other than the thre De-Dup fields.

Thanks
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
tahirihAuthor Commented:
Is there a ways to obtain Distinct Records based on the columns above, but if there are additional columns, include them in the output table?

For instance, my table has 5 columns, 3 of which will be included in the "DISTINCT" SQL programming. The solution may be the proper placement of an *, but all attempts I have made have not been successful.

Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
what happen when you add the other fields

select distinct [Favorite_Shoe] & [Last_Name] & [First_Name], Favorite_Shoe, Last_Name,First_Name,Field4,field5
from tableX

0
 
tahirihAuthor Commented:
I continue to get unwanted "Expr" fields. The Query inlcludes the non-dedup fields (i.e Field4 or Field5) as part of what is being used in De-Duplication.

Thanks
0
 
Rey Obrero (Capricorn1)Commented:
tahirih,
attach your db.

check Attach File below. if db >4 mb. zipped it
0
 
tahirihAuthor Commented:
File attached, with a few notes.

Thanks
Sample-DeDup.xls
0
 
Rey Obrero (Capricorn1)Commented:
what do you want to do with the duplicates?
0
 
Rey Obrero (Capricorn1)Commented:
does your Access table have a unique recordID?
0
 
tahirihAuthor Commented:
At this time, I am not interested in keeping the duplicates. However, if you have a SQL coding method to export the duplicates into a new table, this is appreciated.

No, there is not a RecordID.

Thanks
0
 
Rey Obrero (Capricorn1)Commented:
see if this will work for you

How to delete duplicate records from a table in Access
http://support.microsoft.com/?kbid=209183
0
 
tahirihAuthor Commented:
I thank you for this.

I did come across this link myself. However, I am particular on obtaining SQL code.

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now