Restart record count when the value in a specific field changes

[RC]

What is needed here appears to be very similar to restarting a numbering
sequence in a query each time the value in a specific field changes. The
example below shows what is desired in a query of a table named RECORDS ---
where the numbering sequence in the LABEL_ORDINAL field restarts each time
the value in the LABEL field changes:

LABEL LABEL_ORDINAL
ABC 1
ABC 2
ABC 3
ABC 4
DEF 1
DEF 2
DEF 3
DEF 4
GHI 1
GHI 2
JKL 1
JKL 2
JKL 3

Yet, when I tried the following in SQL view:

SELECT a.LABEL, Count(*) AS LABEL_ORDINAL
FROM RECORDS AS a INNER JOIN RECORDS AS b
ON a.LABEL = b.LABEL
GROUP BY a.LABEL;

The results in the query look like this below, which is not the desired
outcome:

LABEL LABEL_ORDINAL
ABC 16
DEF 16
GHI 4
JKL 9

What will I need to do get it to work?

Thanks,

 

[Lord Kelvan]

you need a sub query

but you also need another field that in changes in value like a date
or a id field

select label,(select count(label) from records as subrecords where
subrecords.label = records.label and subrecords.id > records.id)
from records

as i said you nedd an id field or a date field that for each label is
different so it can be compared to get the increment

Hope this helps

Regards
Kelvan

 

[Dale Fye]

Do you need this value in a report or in a query? If you are going to use
this in a report, you can take advantage of the RunningSum property and just
put the number one (1) in a field of your query. Then, in the report, you
indicate that you want a running sum on that field, and it will do what you
are asking.

I actually have a function that I have used in the past for this type of
thing. It takes advantage of the Static variable declaration. Call this
function in your query, passing it the value in your [Label] column.

Public Function fnOrdinal(SomeValue As Variant) As Long

Static myOrdinal As Long
Static myValue As Variant

If IsEmpty(myValue) Or (myValue <> SomeValue) Then
myValue = SomeValue
myOrdinal = 0
End If

myOrdinal = myOrdinal + 1
fnOrdinal = myOrdinal

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[RC]

Thanks Dale, this worked great!
--
RC

Do you need this value in a report or in a query? If you are going to use
this in a report, you can take advantage of the RunningSum property and just
put the number one (1) in a field of your query. Then, in the report, you
indicate that you want a running sum on that field, and it will do what you
are asking.

I actually have a function that I have used in the past for this type of
thing. It takes advantage of the Static variable declaration. Call this
function in your query, passing it the value in your [Label] column.

Public Function fnOrdinal(SomeValue As Variant) As Long

Static myOrdinal As Long
Static myValue As Variant

If IsEmpty(myValue) Or (myValue <> SomeValue) Then
myValue = SomeValue
myOrdinal = 0
End If

myOrdinal = myOrdinal + 1
fnOrdinal = myOrdinal

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

What is needed here appears to be very similar to restarting a numbering
sequence in a query each time the value in a specific field changes. The
example below shows what is desired in a query of a table named RECORDS ---
where the numbering sequence in the LABEL_ORDINAL field restarts each time
the value in the LABEL field changes:

LABEL LABEL_ORDINAL
ABC 1
ABC 2
ABC 3
ABC 4
DEF 1
DEF 2
DEF 3
DEF 4
GHI 1
GHI 2
JKL 1
JKL 2
JKL 3

Yet, when I tried the following in SQL view:

SELECT a.LABEL, Count(*) AS LABEL_ORDINAL
FROM RECORDS AS a INNER JOIN RECORDS AS b
ON a.LABEL = b.LABEL
GROUP BY a.LABEL;

The results in the query look like this below, which is not the desired
outcome:

LABEL LABEL_ORDINAL
ABC 16
DEF 16
GHI 4
JKL 9

What will I need to do get it to work?

Thanks,