Record counter based on value from another field

I am new to VBA and struggling with how to accomplish the following:

I need to assign consecutive numbers to each record in a table, based on the value in another field.  The counter must re-set each time the value in the other field changes.
Example:

Fund_Name    Counter_Field
Fund_1                     1
Fund_1                     2
Fund_1                     3
Fund_2                     1
Fund_2                     2
Fund_2                     3

I am currently trying to accomplish this by writing an expression for the counter_field in an append query, but I think I should also be able to do with an update query, but I am not clear how to refer to the 'prior-record' (if prior-record fund_Name <> current record fund_Name, etc.)

Ideally, I would like to figure out how to do it with VBA,  declaring record-set, counter and using rowcount, but I am lost.  Any help much appreciated.
Coleen SullivanIndependent ContractorAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
you need a unique id for each record.

select Yourtable.fund_name,(select count(*) from Yourtable as T where T.fund_name=Yourtable.fund_name and T.id>=Yourtable.ID) as ItemCounter
from Yourtable
order by Yourtable.fund_name;
0
 
magarityCommented:
The easiest way is to use a ranking function and let the database do all the work.  Here's the description on TechNet: http://technet.microsoft.com/en-us/library/ms176102.aspx
0
 
magarityCommented:
I should be clearer that the particular ranking function you need is not RANK but another one, ROW_NUMBER.  This query will give you the result:

SELECT fund_name, ROW_NUMBER() OVER (PARTITION BY fund_name ORDER BY fund_name ASC) AS COUNTER_FIELD FROM yourtable
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Coleen SullivanIndependent ContractorAuthor Commented:
Thank you maragity, this syntax seemed to make more sense to me, as it looks similar to the sql that Access uses, however I get a 'syntax error, missing operator' error when I add this sql code to an access query:

SELECT T_Master.Ticker, ROW_NUMBER() OVER (PARTITION BY T_Master.Ticker ORDER BY T_Master.Ticker ASC) AS PtnrNmbr FROM T_Master

I am new to posting on this forum and I realize I added an SQL tag to my question, which may have been the wrong tag to add if that means "SQL Server" -which I have no experience with- so I was lost when I reviewed your first resolution.

In researching a bit more on line, it seems as though this code is indeed SQL server, which I am guessing Access doesn't read(?) and which is why I am having trouble using it.  Can you offer some tips on how to convert this to sql or VBA? Or - if I should be able to use this in Access 2010, perhaps there is a tool or add-in I am not aware of that needs to be enabled?  Any thought appreciated!
0
 
Rey Obrero (Capricorn1)Commented:
@ ccsul,

does your table have a unique id field ?


.
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
@capricorn1

Yes -  I am just testing your solution now, thank you!
0
 
magarityCommented:
Sorry, I am so used to SQL Server I didn't notice the tag for Access.  Access does have some ranking functions but I don't have any experience with them.  Here is Microsoft's help on it: http://support.microsoft.com/kb/208946
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
@ capricorn1 -

This works - thank you!
I am not familiar with using 'select (count(*))' so I am not clear as to how the "T" variable is working, but I will research some more and hopefully figure it out.

One questions though:
My IDs are consecutive within each fund, but the counter field is not applying consecutively, is there a way to do this?  Here is my existing sql:

SELECT T_Master_Export.ticker, T_Master_Export.ID, (select count(*) from T_Master_Export as T where T.ticker=T_Master_Export.Ticker and T.id>=T_Master_Export.ID) AS PtnrNum
FROM T_Master_Export
ORDER BY T_Master_Export.ticker, T_Master_Export.ID;

Thanks again this is perfect!
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
@ magarity -

Thank you, I will check it out - always interested in learning new ways to do things!
0
 
Rey Obrero (Capricorn1)Commented:
<do you mean tthis  "T_Master_Export as T " >

that is called aliasing a table, it creates a copy of the table "T_Master_Export" in memory and giving it a name "T".  so, basically you are doing a query using a table and its copy.
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
Yes, that is what I was referring to, and - wow - I had no idea you could do that, it's like a query within a query - very cool- thanks for enlightening me!  
Any tips on my question re: forcing the counter to apply consecutively based in the consecutive ID?
0
 
Rey Obrero (Capricorn1)Commented:
<Any tips on my question re: forcing the counter to apply consecutively based in the consecutive ID?>

I don't see the relevance of doing so.
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
Not relevant for use with system, more for user benefit when included in a down-stream excel application.  The actual sort-order of data is based on 3 separate fields, keeping the  counter/'partner-number' sequential based on those fields simply provides clarity for user when doing their analysis. If it can't be done, so be it, I was just wondering if it could.
Thanks again.
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
Clear and concise solution.  Also explained the use of declaring alias tables.
0
 
Coleen SullivanIndependent ContractorAuthor Commented:
While playing around with this great tool, I thought I would post some additional notes on ways I have found this to be even more useful.

Original solution:
select Yourtable.fund_name,(select count(*) from Yourtable as T where T.fund_name=Yourtable.fund_name and T.id>=Yourtable.ID) as ItemCounter
from Yourtable
order by Yourtable.fund_name;

Notes:
1:  As written, the counter is applied randomly.

2: Add the existing primary IDkey in the 'order by' to ensure consecutive ordering.  

3: Using "> =" will apply the counter consecutively from the 'greatest' primary_ID to the 'smallest'; i.e. if there are 100 records to count, counter_1 will apply to ID_100, counter_2, to ID_99, etc.

4: Using "< =" will apply the counter consecutively in the order of the ID.

I am using the results in a downstream excel application that becomes a feed for a large tax system.  Users must review the excel data sorted in a particular order, so my actual data is ordered-by various fields.  The 'counter' field here assigns a partner number based on that sort-order and applying the operator as noted in #4, I am able to keep the downstream data in the order I need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.