Access 2010 query duplicates

[jacekwbak]

Hello,

I have following problem:

table1
id / ArticleCode / ArticleColor
1 / 123 / blue
2 / 123 / blue
3 / 222 / green
4 / 333 / red
5 / 444 / blue

Out of this data I need to pick unique values to return:

1 / 123 / blue
3 / 222 / green
4 / 333 / red
5 / 444 / blue

so basically, i need to keep all the fields, but throw out only records that have duplicate ArticleCode

When I run a wizard to find duplicates, and include ID and ArticleColor, it returns all recods.

When I use COUNT([ArticleCode])=1 it returns only record w/o duplicates.


Please help me out

[Seth Schrock]

You need a SELECT DISTINCT query.

SELECT *
FROM table1

[zmbd]

Respectfully Seth, That will not work, nor will "DISTINCTROW" in that the field [ID] contains a unique value. You really should try it in a test database, don't take my word for it... ;-)

Now, if OP does not need [ID] then you can include only the [ArticleCode] and [ArticleColor] in the query use either of the "DISTINCT" Predicates.

[jacekwbak]

Thanks for reply,

select distinct *
from table1

still will return all the records because of unique ID

If I only SELECT DISTINCT on ArticleCode then ok, but problem is that I need the ID.

What Now?

[zmbd]

AH, Thank You for attempting the SQL.
You really should read my post. It would have saved you some frustration.

I have one solution that I use quite often for things like this... took me ages to figure out and as it is not straight forward try the following:

I actually have a template table and query set for this:

tbl_test
PK is the primary key,

SELECT [test_PK], 
   [Test_Field1], 
   [Test_Field2]
FROM [tbl_test] AS  t1
WHERE NOT EXISTS 
   (SELECT 1
    FROM [tbl_test] AS t2
    WHERE [t2]![test_field1] = [t1]![test_field1]
         AND 
               [t2]![test_field2] = [t1]![test_field2]
         AND
              [t2]![test_pk] <[t1]![test_pk]);

Now I hope Rabbit or NeoPa have something more elegant :)

[Seth Schrock]

Duh. I'm not sure what I was thinking, but I clearly missed the ID field throwing off the DISTINCT query results.

[Narender Sagar]

If I have to solve this, I'd simply create following query :

SELECT Table1.COLOR, Table1.CODE, First(Table1.ID) AS FirstOfID
FROM Table1
GROUP BY Table1.COLOR, Table1.CODE;

I tried above query and got desired result..

[zmbd]

Told you there had to be a better method for your data set... the query I posted is a much shorter one I use against 16 fields... it's an old database that was not designed very well by one of my predecessors; however, given what it does, it's impressive (but slow).
I never thought to re-work it for smaller datasets...

[jacekwbak]

Wow. Problem Solved! Thanks

[Narender Sagar]

If you post what solves your problem, it will be beneficial for other readers.
thanks.

[NeoPa]

Narender:
If you post what solves your problem, it will be beneficial for other readers.

I suspect you misunderstand. My understanding from reading through the thread is that your post (which I've now selected as Best Answer) was exactly the breakthrough they were looking for.

@Jacejwbak.
I hope you noted that many experts were confused by your question asking for one thing, then indicating with the example something quite different. Seth actually answered the question perfectly, but that wasn't what you needed because the question was asked wrong. Typically, the clearer the question is, the quicker and more easily you will find an answer posted.