counting in query everytime the record appear

[Diezz]

Hello,

This is unusual but it's not for me, it's for a client.

The table is :

ID Name
1 A
2 A
3 B
4 A


The query will have the ID field, the name field and an occurence.

The result should be :

1 A 1
2 A 2
3 B 1
4 A 3

So it should counts how many time it appearad until current ID.

Anyone has some ideeas?

 

[kjv1611]

That's easier than you might think.

Create the new query.

In design view, choose your fields (choose 2 of the name or whatever field).

Next, hit the "Totals" button in the toolbar (looks like a big E like the AutoSum in Excel)

Next, on the new "Total" row under the second selection of the same field, change it to "Count"

Then, if you want a "pretty" name, go into the "Field" row for that second field, and enter something like:

"How Many":FieldName

 

[PHV]

One way (SQL code):
SELECT A.ID, A.Name, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON (A.ID>=B.ID) AND (A.Name=B.Name)
GROUP BY A.ID, A.Name;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886