1

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.

LWC
  • 1,084
  • 1
  • 10
  • 28
PaulFrancis
  • 5,748
  • 1
  • 19
  • 36
  • 1
    If Barranka's answer isn't exactly what you needed, it may help to explain why those FName, Password, and Outcome columns are relevant to your question. – HansUp Oct 19 '12 at 15:45
  • Thanks HansUp the other fields are in the table I only would like to display the ID start and End.. @Barranka's solution worked good thanks for your reply.. – PaulFrancis Oct 22 '12 at 09:24

1 Answers1

2

I can sugest this:

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

Hope this helps you

Barranka
  • 20,547
  • 13
  • 65
  • 83
  • That works perfect @Barranka.. I did that to start with, but for some reason it did not return what I wanted.. I tried as you showed me, it works.. strange.. but works.. Thanks. :) – PaulFrancis Oct 22 '12 at 09:17