Record Counter reset at control break

  • Thread starter Thread starter Krisse
  • Start date Start date
K

Krisse

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

Help!

Thanks!
 
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
 
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
 
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
 
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!
 
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
 
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!!!
 
Tom, I got the detail rank going great, but I am having
trouble translating your code (pasted below from your
earlier message) to count at the CONTRACT_NBR level.

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

I left off the AND T1.AccountNo < T.AccountNo but that
didn't work. I want the same rank # for every occurrence
of the same CONTRACT_NBR incrementing by 1 for each
subsequent CONTRACT_NBR.

Would you help? THANKS!
 
Back
Top