Increment Sequence Count Within a Query

[Cyhill]

I need to see if there is a way to assign a sequence number to a particular
record within a query. For example...If an employee was hired multiple times
(hired / termed / then hired again), I need to count each hire on an
incremental basis. Below is an example of what I'm looking to do within a
query. Anyone have any ideas.

Empl ID Hire Date Hire Seq Count
125 1/22/2006 1
125 5/6/2006 2
125 2/22/2007 3
521 1/23/2006 1
644 6/25/2005 1
663 5/22/2005 1
663 9/2/2006 2
987 8/22/2005 1

 

[Duane Hookom]

Search these news groups or google on "rank query". This gets answered many
times each month. If you don't find an answer, come back.

 

[Cyhill]

I'm not seeing anything that matches my issue. I do not want an increment
count on all records in the query, only those that or duplicates. I.E. all
employees that have only one hire should be returned with a 1. It's the
employees with multiple hires that I want to increment the count for (based
on hire date). Any assistance you could provide would be greatly appreciated.

Empl ID Hire Date Hire Seq Count

 

[Duane Hookom]

I googled *.Access.Queries.* on "Rank Group" and found this generic SQL (I
believe from Tom Ellison) that you should be able to substitute your own
table and field names.

SELECT Column1, Column2,
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2)
AS Rank
FROM YourTable T
ORDER BY Column1, Column2

 

[Cyhill]

That does the trick : ) Thank you VERY much!!

I googled *.Access.Queries.* on "Rank Group" and found this generic SQL (I
believe from Tom Ellison) that you should be able to substitute your own
table and field names.

SELECT Column1, Column2,
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2)
AS Rank
FROM YourTable T
ORDER BY Column1, Column2

 

[CT25]

Select qry using code below works great but need output in table. Unable to
utilize Make or Append qry that calls out this select qry. The qry just
hangs. Do you have a way?

CT

 

[Michel Walsh]

You can also rank (ascending) using something like this:

SELECT a.column1, a.column2, COUNT(*) as rank
FROM yourTable AS a INNER JOIN yourTable AS b
ON a.column1=b.column1 AND a.column2 >= b.column2


(joins are at worst as fast as sub-select query, when both formulations
exist, since, if the optimizer fails to do its job right, you may end up,
with a sub-query, to 'open and touch' the hard disk a lot of time (once per
record of the main clause, for which the sub-query is re-evaluated)).



You can make a table out of it with a SELECT INTO:


SELECT a.column1, a.column2, COUNT(*) as rank INTO newTableNameHere
FROM yourTable AS a INNER JOIN yourTable AS b
ON a.column1=b.column1 AND a.column2 <= b.column2


(note there is very little difference)



Hoping it may help,
Vanderghast, Access MVP