Solved

Record counter based on value from another field

Posted on 2014-01-22
15
711 Views
Last Modified: 2014-01-31
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.
0
Comment
Question by:Coleen Sullivan
  • 8
  • 4
  • 3
15 Comments
 
LVL 13

Expert Comment

by:magarity
Comment Utility
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:magarity
Comment Utility
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
 

Author Comment

by:Coleen Sullivan
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@ ccsul,

does your table have a unique id field ?


.
0
 

Author Comment

by:Coleen Sullivan
Comment Utility
@capricorn1

Yes -  I am just testing your solution now, thank you!
0
 
LVL 13

Expert Comment

by:magarity
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Coleen Sullivan
Comment Utility
@ 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
 

Author Comment

by:Coleen Sullivan
Comment Utility
@ magarity -

Thank you, I will check it out - always interested in learning new ways to do things!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
 

Author Comment

by:Coleen Sullivan
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
 

Author Comment

by:Coleen Sullivan
Comment Utility
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
 

Author Closing Comment

by:Coleen Sullivan
Comment Utility
Clear and concise solution.  Also explained the use of declaring alias tables.
0
 

Author Comment

by:Coleen Sullivan
Comment Utility
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now