DanBrill Posted December 29, 2003 Share Posted December 29, 2003 Can I create a calculation that will give me a running count of the number of records in a self-join relationship? For example, I have a db with 10 records, and a field called TxnNum. The first four records have a TxnNum of 1. The next 4 have a TxnNum of 2. The last 2 have a TxnNum of 3. I'd like to get a running count so that the records would be enumerated within the TxnNum = TxnNum relationship. The TxnNum, RunningCount pairs would be: 1 , 1 1 , 2 1 , 3 1 , 4 2 , 1 2 , 2 2 , 3 2 , 4 3 , 1 3 , 2 Can I do this? Thanks, Dan Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted December 30, 2003 Share Posted December 30, 2003 Hi Dan, There are in fact several ways for this. You could have the "remainder" (1 to 4) auto-entered from a lookup, and then calculated. This was discussed a while back. Have a search for "enter from previous" Do you have an order (sort) for this ? If not, you could also base your calc upon the value list item function, returning each Ids, where the remainder would be a wordcount of these Ids.. i.e. WordCount(Left(ValueListItems(Status(CurrentFileName), "YourIdsList"), Position(ValueListItems(Status(CurrentFileName), "YourIdsList"), ID, 0, 1))) if your Ids are built using a "ID"&Left("0000"&serial,4) for example Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted December 30, 2003 Share Posted December 30, 2003 Attached a quick test file for this using the Value List Items function (unstored though) and the auto-entered from serial. HTH RankingSJ.fp5.zip Link to comment Share on other sites More sharing options...
DanBrill Posted December 30, 2003 Author Share Posted December 30, 2003 Thanks! I've never used the Value List Items function before. Didn't even know it was there to tell the truth. I'm pulling apart your example to see how it works. This will do the job once I figure it out. Happy New Year, Dan Link to comment Share on other sites More sharing options...
DanBrill Posted December 31, 2003 Author Share Posted December 31, 2003 Thanks again. All is working well. Slick technique. Dan Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7415 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now