How to get a sequence number in a query

[doyle60]

If I have a query where two of the columns are as follows:

GroupID Style
1876 S100
1876 S101
1876 S107
8238 P111
8238 P111
5211 W500
5211 W500
7772 JS103
7772 JS103
7772 JS105
7772 JS105
7772 JS105

How do I get a field to return a sequence number, beginning again at
the number 1 with every change in the Group ID? That is, how do I get
the query to return this:

GroupID Style SeqNo
1876 S100 1
1876 S101 2
1876 S107 3
8238 P111 1
8238 P111 2
5211 W500 1
5211 W500 2
7772 JS103 1
7772 JS103 2
7772 JS105 3
7772 JS105 4
7772 JS105 5

I have searched the Web and cannot find how to do such. I have done
this sort of thing for sequencing the whole of one query's results, but
not something like this. (This question was asked on another thread
but never answered). Thanks.

Matt

 

[John Spencer]

Try searching the groups for "Ranking query"

SELECT GroupId
, Style
, (SELECT Count(GroupID)
FROM YourTable as Temp
WHERE Temp.GroupID = YourTable.GroupID
AND Temp.Style <=YourTable.Style) as Rank
FROM YourTable

Unfortunately unless you have some other criteria that will return duplicate
values for the RANK when GROUP ID and Style are identical. If you need a
sequential one-up number then you will need a way to distinguish between the
members of the matching GroupID and Style.

If you are doing this in a report, the report can handle the numbering
without any need to do it in the query.

 

[Martin]

The following works but you have to have an autonumber field in the table
(you could add it now if you like or perhaps it already has one for its
primary key). I've called it ID in the code and the table Table2:

SELECT (SELECT Count(*) FROM Table2 As X WHERE X.[Group ID] = T.[Group ID]
AND X.ID>=T.ID) AS [SeqNo], T.[Group ID], T.Style
FROM Table2 AS T
ORDER BY T.[Group ID];

 

[doyle60]

I have not yet looked up "Ranking Query." However, John, I do have a
third column called "Color" that creates the unique combination of
Style-Color.

Martin, the query I wish to perform this operation on is coming from
other queries and getting a single autonumber is just not possible. I
would have to throw it into a table to get it and I would rather not do
that.

In truth, the Style column is not even needed for my question. I do
not care what order the ranking comes in on. I really just want to
number the GroupID results starting with one and going till the next
change in GroupID, where it would begin with one again. It appears
from my question that I want it to rank it in alphbetical order by
Style. But that is not really important.

Matt

 

[John Spencer]

If Color plus style plus group gives you a unique record then you could try.

SELECT GroupId
, Style
, (SELECT Count(GroupID)
FROM YourTable as Temp
WHERE Temp.GroupID = YourTable.GroupID
AND Temp.Style =YourTable.Style
AND Temp.Color < YourTable.Color) +1 as Rank
FROM YourTable

 

[Martin]

I think you're going to be lucky to get an answer that matches your high
expectations Doyle: Access just doesn't think in terms of related records
(beyond basic grouping and sorting). This may be a case for dumping your
results into Excel and putting together a suitable function there.

Good luck!