Record Counter reset at control break

K

Krisse

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

Help!

Thanks!
 
T

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
 
K

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
 
T

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
 
K

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!
 
T

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
 
K

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!!!
 
K

Krisse

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

Thanks again, Tom!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top