Record Counter reset at control break

[Krisse]

I need to have a field holing a record counter that will
reset at each control break.

Help!

Thanks!

 

[Tom Ellison]

Dear Krisse:

This sounds like a "ranking" of the rows within groups. It can be
done with a subquery that counts the number of preceding rows within
that group. These are then ranked according to one or more fields
other than those defining a "group". These fields must form a unique
key within each group or you may have "ties" in the ranking.

If you will write a simple SELECT query and post it here, I can work
from that to write a rank column for you. You must specify which
columns specify a "group" (what you call a control break) and which
additional columns specify the sorting within each group.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Krisse]

Thanks for your help, Tom. I want rank the AccountNo's,
starting back at one for each ContractNo.

Select tblDetail.ContractNo, tblDetail.AcctYear,
tblDetail.AccountNo from tblDetail

 

[Tom Ellison]

Dear Krisse:

As a starting point, you can try this:

SELECT ContractNo, AcctYear, AccountNo,
(SELECT COUNT(*) + 1 FROM tblDetail T1
WHERE T1.ContractNo = T.ContractNo
AND T1.AccountNo < T.AccountNo) AS Rank
FROM tblDetail T
ORDER BY ContractNo, AccountNo

Please let me know if this helps, and if I can be of any other
assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Krisse]

I'll give this a try and will reply back again.

Thanks so much.

 

[Krisse]

Problems, Tom.

Here is my select statement:

SELECT T.CONTRACT_NBR AS Expr1, T.Account AS Expr2,
(SELECT Count(*) + 1 From F4311Z1_JDEAcct as T1 WHERE
T1.Contract_nbr = T.Contract_Nbr) AS RANK
FROM F4311Z1_JDEACCT AS T
ORDER BY T.CONTRACT_NBR, T.Account;

Here are the results I am getting:

Expr1 Expr2 RANK
029801 010016400057686480 2
029802 010016400057686480 2
029803 010016400057686480 2
029804 010016400057686480 2
029819 010016400057681411 3
029819 010016400057686411 3

I am expecting a 1 for verything excetp the last record
which should have a 2.

Any ideas?

Thanks!

 

[Tom Ellison]

Dear Krisse:

You've omitted significant portions of the query I sent you. Please
try pasting it in just as I sent it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Krisse]

Thanks, Tom. I got it to work.

Shame on me for being sloppy when translating to my actual
table and field names. I am going to be using this
technique for several different files while working on
data conversion to a new system, so I gave you a generic
scenario.

Thanks again!!!

 

[Krisse]

FYI: I took a break and came back to try again and I
finally got it!

Thanks again, Tom!