how to get instance number of string

[Kermit]

Say I have a table with the following values. How can I get the occurrence
number of each one?

Table1.Field1
--
Red
Red
Orange
Red
Green
Blue
Blue

This would be the following output:

Red 1
Red 2
Orange 1
Red 3
Green 1
Blue 1
Blue 2

In other words:

Red 1 First "Red" so it's 1
Red 2 Second "Red" so it's 2
Orange 1
Red 3 Third "Red" so it's 3
Green 1
Blue 1
Blue 2 Second Blue so it's 2

I know that database values have no concept of row or order, but I need to
mimic that.

Thanks a lot.

 

[Allen Browne]

Bottom line is that you will need some way to distinguish between the
records in order to be able to number them. Adding an autonumber (or any
kind of primary key) will do.

Details of your options in
Ranking and numbering records
at:
http://allenbrowne.com/ranking.html

 

[Kermit]

Beautiful. Thank you.

If you have an autonumber column named ID:

SELECT C2.Field1,
(
SELECT COUNT(*) + 1
FROM Table1 AS C1
WHERE (C1.Field1 = C2.Field1 AND C1.ID < C2.ID)
) AS Cntr
FROM Table1 AS C2

The Cntr column shows the progressive numbering for that color.

Chris
Microsoft MVP

 

[Kermit]

Thanks. Once I got the Primary Key in there this made a lot more sense.

 

[Kermit]

Chris, one other question. I notice this takes quite a while to do if there
are many records (say 10,000 or so).

Is there an alternative? I can add whatever keys/indexes/columns necessary.

Thanks

 

[Kermit]

Thanks. I've modified it.

The weird thing is it's faster to roll my own occurrence number using VBA.
For a couple thousand records the query is reasonably fast, but 5-15K takes
way way longer. Surprisingly, VBA is faster in all cases.

Thanks again.

 

[Kermit]

Well I'm using an API timer for VBA. VBA can determine 10K occurrence
numbers in about 6 seconds, while the query takes a 1 minute 20 seconds
(used a stopwatch for that).

To avoid screen refresh issues and make the timing more concrete, I modified
the query to return the COUNT:

SELECT Count((SELECT COUNT(*)+1 FROM Table1 t2 WHERE t2.KeyValue =
t.KeyValue AND t2.KeyId < t.KeyId)) AS Expr1
FROM Table1 AS t;

16K rows took 15 seconds in VBA to generate occurrence numbers for, and 3
minutes 34 seconds in SQL. I suppose technically some time may be deducted
due to the aggregation of the occurrences and environmental differences
between SQL and VBA but nevertheless VBA is vastly faster for this purpose.
Again I'm surprised. Prior to this I thought Access would blow away my
procedure.

 

[Kermit]

In Access I'm using the COUNT function to display the total count of
occurrences to avoid exactly the issue you describe.

In VBA I'm not using Jet. I'm rolling my own occurrence finder.

Are you opening a query in the window so you display all the rows in both
your vba version and the one with the correlated subquery?

You may be comparing apples and oranges if you're measuring the display
time
and calculating time for the vba function on a small subset of rows, but
measuring the time for the correlated subquery to calculate and display
*all*
rows. If you have a single column calculated by a custom vba function in
a
query you open in a window, Jet will use the vba function only on the rows
that are currently showing. Calculating for a small portion of the rows
is
going to be much faster that calculating for all the rows.

You can tell if that's happening by scrolling up and down rapidly in the
datasheet after the query is finished. If there's a drag while you're
scrolling in the vba version query, Jet is calling your vba function again
for every row showing in that window, but didn't calculate for every row
in
the table like the correlated subquery one did.

Chris
Microsoft MVP

Well I'm using an API timer for VBA. VBA can determine 10K occurrence
numbers in about 6 seconds, while the query takes a 1 minute 20 seconds
(used a stopwatch for that).

To avoid screen refresh issues and make the timing more concrete, I
modified
the query to return the COUNT:

SELECT Count((SELECT COUNT(*)+1 FROM Table1 t2 WHERE t2.KeyValue =
t.KeyValue AND t2.KeyId < t.KeyId)) AS Expr1
FROM Table1 AS t;

16K rows took 15 seconds in VBA to generate occurrence numbers for, and 3
minutes 34 seconds in SQL. I suppose technically some time may be deducted
due to the aggregation of the occurrences and environmental differences
between SQL and VBA but nevertheless VBA is vastly faster for this
purpose.
Again I'm surprised. Prior to this I thought Access would blow away my
procedure.

That is surprising. What code are you using to compare running times?

[quoted text clipped - 7 lines]

takes
way way longer. Surprisingly, VBA is faster in all cases.

 

[Kermit]

I'm not displaying the values from the recordset. Just the count of all of
them. (did you read my first test comparison post?)

SELECT Count((SELECT COUNT(*)+1 FROM Table1 t2 WHERE t2.KeyValue =
t.KeyValue AND t2.KeyId < t.KeyId)) AS Expr1
FROM Table1 AS t;

Access displays1 number. How is that not an "almost equivalent" timing
comparison to the VBA custom way?

Also, Access can display 16K records in its sleep. If I insert the VBA
results into an Access table and then open it, I can paginate to the last
record immediately.

 

[Kermit]

Ok I tried it your way.

I have a table with 10,000 records. I have a query ("Query1") with the
occurrence finder:

SELECT t.KeyValue, (SELECT COUNT(*) FROM Table1 t2 WHERE t2.KeyValue =
t.KeyValue AND t2.KeyId <= t.KeyId) AS Instance
FROM Table1 AS t;

Then I have an empty table ("Table3") with two fields that match the
datatypes of the above query.

and a second append query:

INSERT INTO Table3 ( KeyValue, [Index] )
SELECT Query1.KeyValue, Query1.Instance
FROM Query1;

10K records now times at 1 minute 17 seconds (compared to 1 minute 20
seconds before).

16K records now times at 3 minutes 30 seconds (compared to 3 minutes 34
seconds before).

Have you tried this? I would like to know if you can generate occurrence
numbers on your machine quickly. Everything I have tried in Access has
failed miserably. I'm open to any suggestions to make this work quickly.