troubleshooting Question

Selecting only one record per account number from each record.when multiple records exist

Avatar of morinia
moriniaFlag for United States of America asked on
Microsoft Access
2 Comments1 Solution15 ViewsLast Modified:

Experts,

I have a file that has multiple occurrences for the same account number.  The date is the same.  The file has multiple fields on it. I want only one record for each account.

The query below works for one column but my file has many columns.

How can I get just one record in each group?


SELECT  pat_acct_no
      From Test1 S1
       where pat_acct_no IN
      (Select TOP 1 pat_acct_no
        from Test1  S2
        where s1.pat_acct_no = S2.pat_acct_no
        Group  by  pat_acct_no
    )
    Group by pat_acct_no
    Order by pat_acct_no


ASKER CERTIFIED SOLUTION
Mark Edwards
Chief Technology Officer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 2 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 2 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004