Try to search your question here, if you can't find : Ask Any Question Now ?

my query returns many records per id, I just one any of them per id

HomeCategory: stackoverflowmy query returns many records per id, I just one any of them per id
Avatarjohn asked 2 weeks ago

Hi friends from Stack Overflow. I am trying to run a query in MS Access, i just want one record per ID randomly, but I am getting all.

I tried with distinct is not working, and I try with top, didn’t work either.

this is my table below originaldata

ID  HotelName Role Email
__________________________
1  bandb  admin test1@email.com
1  bandb  admin test2@email.com
1  bandb  admin test3@email.com
1  bandb  user  myuser@email.com
2  myhtl  admin myhotel@email.com
3  ben    admin ben@test.com
3  ben    user  ben2@test.com
4  moon   admin moon@moon.com
4  moon   admin moon@moon2.com

I want to get the below results

ID  HotelName Role Email
__________________________
1  bandb  admin test1@email.com
2  myhtl  admin myhotel@email.com
3  ben    admin ben@test.com
4  moon   admin moon@moon.com


SELECT  *
FROM OriginalData
WHERE (((OriginalData.[Role])='admin') AND ((OriginalData.[ID]) In (Select Distinct [ID] from [OriginalData] where [Role] = 'Admin'  )));

Thank you for your time and help

1 Answers
Best Answer
AvatarMikhail answered 2 weeks ago
Your Answer

9 + 4 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com