how to get the occurence of the count of values or texts

[punna111]

Hi all,

below is my data i have.

Amt
100
50
100
50
300
50
50



i need the count based on the occurrence, Count if is giving only total of count, but i need as below.

Amt time of occurrence
100 1
50 1
100 2
50 2
300 1
50 3
50 4




Thanks,

 

[plog]

You run into the same issue as your last post (http://www.access-programmers.co.uk/forums/showthread.php?t=284585). You havent provided enough data to uniquely identify each record.

Do you have another field in that table that uniquely identifies each row? An ID field, or a date field?

 

[punna111]

No, There is no unique ID,

 

[plog]

Then no, you cannot accomplish what you want with the data you have in a query. If you add an autonumber primary key, then you could do a Running Count Query (search the forum for that term).

 

[CJ_London]

duplicate post here

http://www.accessforums.net/showthread.php?t=58191

and the post identified by Plog is also double posted to accessforums

all giving the same advice - you need a uniqueID

I strongly recommend you read this post

http://www.excelguru.ca/content.php?184

 

[punna111]

Thank you for your advise, I admit that.

To get maximum response only i posted, from Next time I will give the Original post link if any.

coming to my question:
I am very surprised we are not able get a cumulative count for one value in access.

 

[plog]

Its easy to understand if you correct your assumption about your table's data. Your data isn't stored like you displayed it. It isn't in nice rows, its stored more like the attached image. There is no order to your data in a table.

In that image, which 50 should be 1? Which 50 should be 2? Which 50 should be 3? etc.

That's why you can't do what you want. There is no order in a table. Now, if you are able to apply order, you can decide which 50 should be first, second, third, etc.. Until then, they are just another 50 randomly stored somewhere.

 

[punna111]

thanks for reply,

can i use "order by" so that, we will get one type of sorting

 

[plog]

No, because it will not produce a unique enough order. You need a way to uniquely sort your list. You can't do that on a field with same values.