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.

 

[Fazza]

I think best to have a new autonumber field for the unique key. OK?

 

[rowcpa]

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

 

[revans]

You could try something like this http://www.mrexcel.com/forum/showthread.php?t=462515&highlight=running+query

You might need to fiddle with the logic for your situation but it returns a running count.

hth,

Rich

 

[Joe4]

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.

 

[revans]

Good to know.

thx,

Rich

 

[Joe4]

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]

"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 -->

 

[Joe4]

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]

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!