Solved

Record counter based on value from another field

Posted on 2014-01-22
15
751 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
ID: 39801103
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39801203
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
ID: 39803873
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Coleen Sullivan
ID: 39807502
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39807531
@ ccsul,

does your table have a unique id field ?


.
0
 

Author Comment

by:Coleen Sullivan
ID: 39807556
@capricorn1

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

Expert Comment

by:magarity
ID: 39807724
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
 

Author Comment

by:Coleen Sullivan
ID: 39807770
@ 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
ID: 39807775
@ magarity -

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39807797
<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
ID: 39808203
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39808252
<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
ID: 39808544
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
ID: 39808547
Clear and concise solution.  Also explained the use of declaring alias tables.
0
 

Author Comment

by:Coleen Sullivan
ID: 39824278
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

830 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