Link to home
Start Free TrialLog in
Avatar of Coleen Sullivan
Coleen SullivanFlag for United States of America

asked on

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.
Avatar of magarity
magarity

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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Coleen Sullivan

ASKER

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!
@ ccsul,

does your table have a unique id field ?


.
@capricorn1

Yes -  I am just testing your solution now, thank you!
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
@ 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!
@ magarity -

Thank you, I will check it out - always interested in learning new ways to do things!
<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.
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?
<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.
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.
Clear and concise solution.  Also explained the use of declaring alias tables.
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.