Numbering Records

[Keith S]

I have a request to be able to sequentially number records in a query. I
have looked in the forum and found similar questions, but I guess I am not
making the connection. I have a table named, Cycle_Time, that I am running a
query off of and the fields include Facility, Ord Num, Ord Line Num, Cycle
Time. I am after a result to group it by facility. I am have an example
below.

Facility Ord Num Ord Line Num Cycle Time Result I want
HK 1234 001 10 1
HK 4567 003 9 2
HK 7899 002 8 3
JF 9874 003 12 1
JF 5632 001 11 2
JF 6543 002 10 3

I would appreciate any help on coding this. Thanks for all your help.

 

[Michel Walsh]

Not exactly what you want, the result will be:

Facility Ord Num Ord Line Num Cycle Time Result I want
HK 1234 001 10 1
HK 4567 003 9 2
HK 7899 002 8 3
JF 5632 001 11 1
JF 6543 002 10 2
JF 9874 003 12 3



with:



SELECT a.facility, a.ordNum, LAST(a.ordLine), LAST(a.cycle), COUNT(*) as
ResultIwant
FROM myTable AS a INNER JOIN myTable AS b
ON a.facility = b.facility
AND a.ordNum >= b.ordNum
GROUP BY a.facility, a.ordNum



where you will have to replace the name myTable with the real name of your
table (at two places).





Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

You need a ranking subquery in your main query

SELECT Facility
, Ord Num
, Ord Line Num
, Cycle Time
, 1 + (SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.Facility = YourTable.Facility
AND Tmp.[Cycle Time] > YourTable.[Cycle Time]) as Rank
FROM YourTable

In the query grid (Design view) in a new field "cell" enter the following
(all on one line)
Rank: 1 + (SELECT Count(*) FROM YourTable as Tmp WHERE Tmp.Facility =
YourTable.Facility AND Tmp.[Cycle Time] > YourTable.[Cycle Time])

Note that with a large set of records this typeof query can be slow.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Keith S]

Thank you Michel, this is exactly what I need to finish a project. I
appreciate your time.

 

[Keith S]

One last question about this, in my table it is sorted by Facility and Cycle
Time. I want it to number how it is sorted in the table. Is there syntax to
have it sort that way? I have tried moving the fields around in the select
statement and in the Group By.

Thanks again.

 

[Michel Walsh]

Try the following:

SELECT a.facility, a.cycle, LAST(a.ordLine), LAST(a.OrdNum), COUNT(*) as
ResultIwant
FROM myTable AS a INNER JOIN myTable AS b
ON a.facility = b.facility
AND a.cycle>= b.cycle
GROUP BY a.facility, a.cycle


Hoping it may help,
Vanderghast, Access MVP