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.

 

[Joe4]

Glad it will all work out!

 

[rowcpa]

Excel appears to have some limitations on recognizing differences. If two consecutive records have the same reference approximately 18 places in Excel recognizes them as identical and counts the as the same.

I don't think this limitations hinders use of the formula.

 

[Racquet]

I like Excel. I use it every day. I am an accountant, Excel works really well for me. I am an accountant.

However, I think Access is a much more robust tool for summarizing accounting data. Unfortunately, I did not fully understand your problem, but my guess is that it is somewhat more complex than it appears, particularly since you said you have tried a number of approaches.

The first suggestion that I have is that you have a scheme designed within Access wherein in you have the queries and tables that you need, and you know the sequence that the queries have to run in, before you attempt to automate the application through macros or vba.

I use the count function a lot, which would have given you the information that you started your discussion with. Without seeing a flowchart or a whiteboard schematic of your data and your objective, I can't design an application that meets your needs. My approach though would probably be use some update queries and temporary tables to accomplish what you are trying to accomplish, while preserving the original source data should you have to go back and create something again. It certainly makes sense to me that it takes some time to fully allocate the credit card charges, and it also makes sense that a credit card charge goes to multiple accounts. Standard stuff.

Please let us know where you are at, I would love to help with design of a solution.

 

[Joe4]

I am not sure of what the "bigger" picture is here, but as for the original question, taken at face value, I think it is pretty straightforward, and I understand what is being asked. It is something that Access does not handle particularly well (I know because I ran into the same instance a few years ago, and solicited the help of many different experts and sites).

If you still need an Access solution, let me know and I will create the VBA to do the numbering, like I mentioned. I couldn't tell from your last post if you thought the Excel solution was or was not going to work for you, so I am not sure if you are still looking for something.

 

[rowcpa]

Yes, I am going to work with the Excel solution.

Yes, my previous post on the topic accepting the Excel solution was poorly written.

Addressing both Ravens and Joe, I have been able to accomplish everything I set out to accomplish in the past using Access and Cystal Reports for handling large amounts of accounting data. I actually rely on Crystal quite heavily for working with my output from Access and suspect my Crystal skills are what have kept me from having to resort to attempting something with VBA in Access previously. I also think my Crystal work is what has kept me away from working in Excel much at all the last 8 years or so and my skills with Excel may be getting a little behind the times

My initial assumption that I need VBA coding was simply that I knew Access didn't handle my issue well and this was really "front end" work (keeping duplicate records out of the permenant table), not something where I could rely on my usual fall back - Crystal Reports.

In any case, the Excel solution works fine, with one minor issue. The credit card reference numbers are very long, I think 23 characters. The Excel formula Joe gave appears to have a small limitation in that if after sorting my Excel download by reference number and then amount I end up with two reference numbers adjacent to each other in the Excel table that are the same about 18 characters in before there is a difference Excel is numbering then xxxxxxxxxxxxxxxxxxxxxx1, xxxxxxxxxxxxxxxxxxxxxxx2 instead of recognizing them as different reference number, each numbered 1.

I don't know if this is even a problem. My initial inclination was to ignore the issue and let Excel number them. I don't think it would cause a problem down the road. But, I have since changed my mind and to be extra careful. Actual cases where our users ligitimately need to allocate one credit card transaction accross multiple accounting codes are infrequent. Even the cases where Excel is numbering 1,2,3 etc are the minority. After copying the formula it is fairly simple to scroll down through the formula column and manually change cases where Excel numbered sequentially incorrectly back to 1.

In any case, I have been rebuilding my permenant table since getting the Excel solution and found that my primary purpose, creating a good solid primary key that will keep duplicate records out of the table, is working perfectly. So I am moving on and appreciate the help I received here.

 

[Joe4]

Write back if anything changes and you want that Access solution.

Regarding the Excel limitation, is the Credit Card Number formatted as numeric or text in Excel? It may not have a problem if you format it as text.

I also have another idea, such as sorting your Excel sheet by the Credit Card Numbers, adding a helper column where you extract the right-most 18 characters, and then apply a formula like this to do your counting:
(assuming your helper column is column B, and your first row of data is in row 2):

enter in cell C2: =IF(B2=B1,C1+1,1)
and copy down for all rows

 

[rowcpa]

The problem appears to happen either numeric or text. Excel appears to sort the refeerence number properly as text, it's just the sequence numbering.

 

[Joe4]

Interesting.

In any event, I think the second method should work.

 

[rowcpa]

Interesting. The 2nd formula does appear to do a better job analysing the data and not mistaking references numbers that are not equal as equal.

But the result is not what I wanted and the first formula is.

The 2nd formula adds one to the reference number when it duplicates. If it was:

1234
2234
3234
3234

The formula field returns:

1
1
1
3235

Again, scrolling through the results from the first formula is not oddious and I am going to go with it

 

[Joe4]

You got the range references messed up.
You want it to add one to the counter just above it, not to your helper column. It should be comparing the helper column, but adding to the count column.

If you can't figure it out, let me know what columns everything is in, and I will show you how to adjust the formula accordingly.