Counting Number of Times Data Appears

rowcpa

New Member
Joined
Aug 10, 2010
Messages
9
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I think best to have a new autonumber field for the unique key. OK?
 

rowcpa

New Member
Joined
Aug 10, 2010
Messages
9
No, auto number will not work.

I have to go back and download data from the same period multiple times. Use May as an Example. For the May accounting period close we may have $20,000 of card transactions. $12,000 will have been allocated to our accounting codes and $8,000 not. The allocated ones will be added to a permenant table and the unallocated are a table that is replaced every month.

Now we move on a month to June. I have to go back to May and again download allocated and unallocated data. But, people have gotten around to doing exepnse allocation so allocated now totals $18,000; unallocated $2,000. The $18,000 includes the $12,000 fron May plus $6000 more. Because our people have a hard time getting the acocunt coding correct many manual changes were done to the $12,000 when it was first posted in May. I don't want to redo all that manual correction so the $12,000 has to stay in the permenant table.

Because of the manual changes to the account coding the $12,000 in the new download no longer matches the corresponding $12,000 in the permenant table. So simple delete queries don't work, nor does a system of incorporating the account number in the primary key with the Ref.

I need to sort the data on the reference field and number each instance as I indictaed in the first post. I have already thought through the easy solutions, trust me
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,178
Office Version
365
Platform
Windows

ADVERTISEMENT

I tried using this solution once before (the one provided in the link), and found it to be very volatile, and actually scrapped it altogether.

Don't miss the disclaimer on it:
Please note: This won't work very well for queries & forms where you intend to update data or scroll up and down as the calculated fields gets calculated again and the sequence number will keep going up and up.
If it were me, I think I would try adding a blank field to my table, then sorting the records in a query, and then use RecordSet VBA to loop through all the records in the query and write the appropriate "counter" to the new field.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,178
Office Version
365
Platform
Windows

ADVERTISEMENT

Good to know.
Yes, I found out the hard way that Access is really not the tool you want if you are looking for some sort of "on-the-fly" counter. You can pull it off in Reports (probably because you aren't editing data in Reports but working with a "static" dataset at run-time), but it does not work well in queries.

Being a database, Access isn't really designed to tell you where a record falls in the table relative to other records in the table. This isn't just true of Access, but most databases. It is just the nature of how databases are designed to work. Relative positions in the table has very little meaning to them.
 

rowcpa

New Member
Joined
Aug 10, 2010
Messages
9
"If it were me, I think I would try adding a blank field to my table, then sorting the records in a query, and then use RecordSet VBA to loop through all the records in the query and write the appropriate "counter" to the new field."

Yes, that is kind of the solution I had in mind when I posted originally. Unfortunately I have never done VBA with my Access work and was hoping someone could give me some coding that does what I want.

Honestly, my situation doesn't seem that unusual so I thought some fixes would be fairly routine.

This data is downloaded from the credit card company as an Excel file and then imported. Perhaps I should look at Excel solutions to the sequencing prior to importing it.<!-- / message --><!-- sig -->
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,178
Office Version
365
Platform
Windows
We should be able to work up a VBA solution for you.

Just a question though. Is this table going to be wiped clean each time, or will you be constantly adding data to it and need to update the "counts" accordingly?

For example, in your example you have REF 7234 listed twice, since there is a 1 in the Count column for the first instance, and 2 for the second instance. If you are leaving the data in there, and at some point in the future, REF 7234 gets added again, do you want a 3 in the Count column for this new record?

FWIW, this is something that can be done very easily in Excel. Let' say that your data is in cells A2:A100, then if you want your Counts in column B, simply add this formula to B2 and copy down to B100:
=COUNTIF(A$2:A2,A2)
 

rowcpa

New Member
Joined
Aug 10, 2010
Messages
9
The Excel solution works well with some test data.

Since this data is downloaded in Excel form prior to importing to my DB the intermediate stop in Excel is not a new step. The amount of time invested in applying a standard sort and then copying this formula is minimal.

Excellent solution!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,594
Messages
5,512,290
Members
408,886
Latest member
kashifziatevta423

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top