• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

ACCESS

How to find Unique Records in ACCESS
0
lux sat
Asked:
lux sat
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
A little more detail would be helpful?  This isn't a homework assignment, is it?

Are you trying to identify only those records in a table which are unique for one or more fields, or are you simply trying to return a set of records with unique values.  If the latter, you can use the DISTINCT predicate in your SELECT statement:

SELECT DISTINCT Field1, Field2 FROM your Table

Or you might want to use a GROUP By clause:

SELECT Field1, Field2 FROM yourTable GROUP By Field1, Field2
0
 
lux satTechnical ConsultantAuthor Commented:
No it is not hw. but I am new to ACCESS. I need to eliminate Identical rows

Ex.

1  2 3
1 2  3
3 4 5

i just want my result set as
1 2 3
3 4 5
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Dale mentioned, more detail is helpful.

Are those values in the SAME field, or in DIFFERENT fields? How you go about showing distinct records depends greatly on that.

Also, you mention you want to "eliminate" identical rows. Does that mean you want to remove them, or just not show them in your query?

Note that Access includes a "Find Duplicates Query Wizard" which may be helpful. to use that, click the Create tab, and select "Query Wizard". You'll see the Duplicates Query in that list. Select that, and follow the prompts.
0
 
lux satTechnical ConsultantAuthor Commented:
Dale/Scott
I have attached the sample table as an image. I want to eliminate Row 3 ( 2 A B C).

ROW 1 is the fieldnames in my table.
TABLEIMAGE.JPG
Thanks,
LAKSHMI
0
 
Dale FyeCommented:
Either of the methods I described above (using DISTINCT) or using a GROUP BY Clause will return a recordset that only has one result for A,B, C.  However, if you want to keep Field1 in the query, and that field is a unique value, it will force all rows to be returned with DISTINCT.  However, you might try:

SELECT MIN(Field1) as FirstOccurance, Field2, Field3, Field4
FROM yourTable
GROUP BY Field2, Field3, Field4
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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