Solved Increment field for a set of records (1 Viewer)

[Romio_1968]

I have a continuous subform linked to a query that adds records to a table
Here is the code of the query

SELECT
TAJunction.Title_IDFK,
TAJunction.Author_IDFK,
TAJunction.Join_ID,
Author.Author_Name,
TAJunction.Timestamp,
TAJunction.Rank
FROM Author
INNER JOIN TAJunction ON Author.Author_ID = TAJunction.Author_IDFK;

Title_IDFK is the bonding control with the parent form.

After filling the control with Author_Name, Title_IDFK, Author_IDFK and Timestamp are passed to the table and a new record is opened.The user may introduce as many authors he wants (0 to n) under the same Title_IDFK until he closes the form.
Up to here, all is fine.
I need that Rank field on the table (and control on the Subform) to be filled too, starting from 1 in the first record, up to n, where n is the total number of authors saved under the same Title ID.
The Rank will restart at 1 at the next set of records.

If you please give me some help on this.

Thank you

 

[June7]

Review http://allenbrowne.com/ranking.html

If your db is split with a SQLServer or MySQL or Oracle backend, there is function for this requirement and could be used in a pass-through query. Unfortunately, Access SQL does not support this function.

 

[theDBguy]

Could you use the timestamp to determine the ranking automatically?

 

[Romio_1968]

Could you use the timestamp to determine the ranking automatically?

I think i can do that. Actualy, that was my first approach, but I tought that having a Rank field will be a reinforced sollution

 

[Romio_1968]

Could you use the timestamp to determine the ranking automatically?

The problem is the risk of having two records in the same second... I know that is quite hard to do it, but you never know

 

[The_Doc_Man]

The problem is the risk of having two records in the same second... I know that is quite hard to do it, but you never know

Do you have simultaneous data entry from multiple users? Because if you don't, two different records won't be in the same second. However, this reveals a potential design flaw. Add an autonumber to the record that worries you. Even if you NEVER use that autonumber for anything else, if it is set to Increment (vs. Random), you can use its order to uniquely identify entry order.

 

[Romio_1968]

Do you have simultaneous data entry from multiple users? Because if you don't, two different records won't be in the same second. However, this reveals a potential design flaw. Add an autonumber to the record that worries you. Even if you NEVER use that autonumber for anything else, if it is set to Increment (vs. Random), you can use its order to uniquely identify entry order.

Actually you have right. I have an autonumber. I remember somebody asking me to remove it
Yet, it is still there and here is the use of it.

 

[Pat Hartman]

That may have been me but if so it was because you were not using the autonumber as the PK. There is a bug that makes the autonumber go wonky and it is linked to having an autonumber that is NOT designated as the PK.

There needs to be a separate unique index on the book and the author. You do this using the indexes dialog. There is no way to do it with the table interface. The multi-field select in table design view only works for the PK.

PS the sequence number is not a "Rank". Rank has a different meaning and shouldn't be used in this context.