asked on

duplicate results

I have imported a set of data from an excel spreadsheet with around 10 fields. in one field (called concatenate), there will be a number of duplicates based on these sequence of numbers. However, there is another field called name. I need a way to query the data to essentially, select all rows where if concatenate is the same but the name is different. for example there will be rows where the concatenate field is the same on different rows of data, but the name field could either be the same for all those rows, or different names, I only want to see unique records, so for example from the below data

concatenate name
123456 pma111
123456 pma111
123456 abc111
67890 pma111
67890 pma111
67890 acb111
88888 pma111
88888 pma1111

the query above should only show

123456 pma111
123456 abc111
67890 pma111
67890 abc111

(and it would return no results for 88888 as their is only ever 1 name associated with 67890). I only want where for each unique concatenate value, a list of unique names associated with that value. If there is only 1 unique value associated with the concatenate, I am not interested in the results. I need the results to include all fields in this table if that complicates things..

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

HI,

pls try

SELECT First(myTable.[concatenate name]) AS concatenate name Field]
FROM myTable
GROUP BY myTable.[concatenate name]
HAVING ((Count(myTable.[concatenate name]))>1);

Open in new window

Regards

Pau Lo

ASKER

>you only want to see one of those rows - but which one?

It doesnt really matter, the first is sufficient..

Pau Lo

ASKER

@rgonzo1971

this produes errors, for example mytable in this case is called duplicates

SELECT First(duplicates.[concatenate name]) AS concatenate name Field]
FROM duplicates
GROUP BY duplicates.[concatenate name]
HAVING ((Count(duplicates.[concatenate name]))>1);

"concatenate" and "name" are 2 different fields in this table. Running the above query exactly returns the error "the SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect"

Rgonzo1971

Sorry

 SELECT First(duplicates.[concatenate name]) AS [concatenate name Field]
 FROM duplicates
 GROUP BY duplicates.[concatenate name]
 HAVING ((Count(duplicates.[concatenate name]))>1);

Open in new window

Pau Lo

ASKER

that query now prompts me to enter parameter value for duplicates concatenate name

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial