Coleen Sullivan
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
SELECT fund_name, ROW_NUMBER() OVER (PARTITION BY fund_name ORDER BY fund_name ASC) AS COUNTER_FIELD FROM yourtable
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!
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 ?
.
does your table have a unique id field ?
.
ASKER
@capricorn1
Yes - I am just testing your solution now, thank you!
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
ASKER
@ 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.T icker 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!
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.T
FROM T_Master_Export
ORDER BY T_Master_Export.ticker, T_Master_Export.ID;
Thanks again this is perfect!
ASKER
@ magarity -
Thank you, I will check it out - always interested in learning new ways to do things!
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.
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.
ASKER
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?
<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.
I don't see the relevance of doing so.
ASKER
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.
Thanks again.
ASKER
Clear and concise solution. Also explained the use of declaring alias tables.
ASKER
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,(selec t 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.
Original solution:
select Yourtable.fund_name,(selec
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.