Counting Number of Times Data Appears

[rowcpa]

Hello,

New to this list. I am an Accountant who dabbles in Access but have never got into programing. I have an issue I think may require some coding.

I want to count and number each time a specific item appears in a field and return the results in a query.

For example I have a table with 8 records in the "Ref" field:

Ref
1234
2234
3234
4234
5234
6234
7234
7234

Note that "7234" apears twice.

After I run my query I want to see the following results:

Ref Count
1234 1
2234 1
3234 1
4234 1
5234 1
6234 1
7234 1
7234 2

Purpose - I am trying to create a key field. In real life the "Ref" field is a transaction reference number for a credit card and it is unique. But, our corporate card holders have the ability to allocate a specific transaction to multiple expense accounts, such a one hotel bill to room, meals, entertainment, etc. This allocation creates multiple instances of the same "Ref." I will use the query to again make each item unique.

Thanks.

 

[rowcpa]

Yes Joe, I messed it up.

Actually, with hindsight it seems simple enough that I should have known what I did wrong. In fact, this is such a logical and simple solution it almost seems like I should have thought of this without asking

In any case, the 2nd formula is working without any issues, excellent work.

 

[Joe4]

Actually, with hindsight it seems simple enough that I should have known what I did wrong. In fact, this is such a logical and simple solution it almost seems like I should have thought of this without asking

Been there and done that.
Sometimes you get in so deep, you can't see the forest for the trees!

Glad you got it all working.

 

[Joe4]

In case you are curious, I did help someone do this with Access VBA not too long ago (I don't know why I didn't remember that before - maybe that bike I accident I had last week affected my head more than I thought!).

Here is a link to that thread:
http://www.mrexcel.com/forum/showthread.php?t=479526