CODEWITHSUNDEEP
sqlms-access-2010
Yaco Zaragoza
Yaco Zaragoza

Reputation: 437

MS Access Selecting Distinct Records

I am using MS Access 2010:

I am working on a calendar and would like to show the unique events types each day.. Can someone help me out wit this..

This is what the table looks like

Events 

 - Event_ID (PK)
 - Event_type_id (FK)
 - Event_name
 - Event_Date

This is my data:

1, 1, Test Event 0, 06/06/2014    
2, 1, Test Event 1, 06/06/2014   
3, 1, Test Event 2, 06/07/2014    
4, 2, Test Event 3, 06/07/2014    
5, 3, Test Event 4, 06/09/2014

I need the query to only return 1 event of each type each for each date..

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this with a clever where clause:

select e.*
from Events as e
where not exists (select 1
                  from Events as e2
                  where e2.event_date = e.event_date and e2.event_type_id = e.event_type_id and
                        e2.event_id > e.event_id
                 );

This will return the rows with the largest event id on each date for each type.

Upvotes: 1

Related Questions