Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

using distinct keyword in SQL on Access DB

Posted on 2006-11-13
8
Medium Priority
?
506 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 800 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 200 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

580 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