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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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
63,289
Office Version
  1. 365
Platform
  1. 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
63,289
Office Version
  1. 365
Platform
  1. 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
63,289
Office Version
  1. 365
Platform
  1. 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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,739
Messages
5,833,418
Members
430,207
Latest member
MedicineVan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top