ms-accessms-query

Find the First occurrence value of data in Query

I have a table that has a lot of data, but only of four categories. I would like to return the ID of the First occurrence and last occurrence, of the type. The following is the simplified version of my data.. 

ID    |    FName    |    Password   |    Category    |    Outcome
-----------------------------------------------------------------
1     |    Dan      |    something  |    NEW         |    8
2     |    Faye     |    another    |    NEW         |    1
:
:
189   |    Chris    |    Password   |    OLD         |    2
190   |    Matt     |    Milk       |    OLD         |    7
:
:
1169  |    Mark     |    Dog        |    LITE        |    3
1170  |    Nick     |    Land       |    LITE        |    1

So I would like to have a query that will return the result as

CATEGORY    |    ID_START   |    ID_END
----------------------------------------
NEW         |    1          |    188     
OLD         |    189        |    1168     
LITE        |    1169       |    9999

I am using Access 2010. Any help greatly appreciated.

Solution

  • I can sugest this:

    select category, min(id) as idStart, max(id) as idEnd
from tbl
group by category

    Hope this helps you