Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Record counter based on value from another field

Posted on 2014-01-22
15
Medium Priority
?
850 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

926 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