Solved

using distinct keyword in SQL on Access DB

Posted on 2006-11-13
8
460 Views
Last Modified: 2008-02-01
I have to use the distinct keyword against three fields in my Access DB. I can do this fine but I need to be able to retain a field called "autoid", which is an auto number because I need to know what record number this is. How could I code this in SQL to get the distinct values from the 3 fields put also keep the autoid field as well?

TIA.
 
0
Comment
Question by:samic400
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 200 total points
ID: 17931705
Hi samic400,
If you have 3 records with the values for 3 fields but different autoid values, how do you decide which autoid value to keep?

One solution

Select field1, field2, field3, max(autoid) as maxid from mytable
Group by field1, field2, field3

Pete
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 50 total points
ID: 17931727
Your DISTINCT requirement indicates that you could have more than 1 autoid associated with the three fields.
So which autoid woulld you use?


select first(autoid) as identity, field1, field2, field3 from yourtable group by field1, field2, field3

0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17931730
hmm, should have refreshed.
0
 
LVL 13

Author Comment

by:samic400
ID: 17931781
I want the autoid data from the first record that was unique because I will be using this number to go after something else.

I just want to eliminate all duplicate records that is why I am using the distinct keyword on the three fields but I want to know what the autoid field holds so I can do something else with it.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 6

Expert Comment

by:gvlob
ID: 17931904
Use peter57r's solution except change "max(autoid) as maxid" to "first(autoid) as firstid"
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17931919
Oops, I should have refreshed also. Jerryb30 will work as is.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17932011
lol. :>)
My choice of aggregation was dumb luck.  Peter answered as well as anyyone could, given information provided in original question.  
0
 
LVL 13

Author Comment

by:samic400
ID: 17932603
thank you for the help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now