Restart record count when the value in a specific field changes

  • Thread starter Thread starter RC
  • Start date Start date
R

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,
 
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
 
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.
 
Thanks Dale, this worked great!
--
RC


Dale Fye said:
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 said:
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,
Click to expand...
Click to expand...
 
Back
Top