Number Rows by ID (1 Viewer)

[Nickek02]

I am trying to number each row (update the AmendmentNumber) in sequential order based off an ID (ContractID). Example below but also attached a screenshot.

I would like to Order By the Document Name as the Name has a combination of [Contract ID] & [DocumentID] in the beginning of the Name.

ContractID	DocumentName	AmendmentNumber
1000	1000_100_ABC Company AMD 1-1-2001	1
1000	1000_525_ABC Company AMD 1-1-2005	2
1001	1001_105_Smith Inc AMD 4-5-2008	1
1002	1002_106_Tom's Auto AMD 2-3-2021	1
1002	1002_415_Tom's Auto AMD 2-3-2022	2
1002	1002_515_Tom's Auto AMD 6-3-2022	3
1003	1003_110_Apple Inc AMD 12-1-2012	1
1004	1004_125_XYZ Inc AMD 10-17-2014	1
1004	1004_485_XYZ Inc AMD 10-17-2021	2

 

[jdraw]

? What does AmendmentNumber give you that isn't available from the composite DocumentName??

Better to use the old standby for relational database ---1 fact in 1 field

I see ContractId, DocumentID,DocName,(not sure what AMD may represent) and a Date value.
Perhaps you could describe how all this fits together in business terms.

 

[GPGeorge]

I am trying to number each row (update the AmendmentNumber) in sequential order based off an ID (ContractID). Example below but also attached a screenshot.

I would like to Order By the Document Name as the Name has a combination of [Contract ID] & [DocumentID] in the beginning of the Name.

ContractID	DocumentName	AmendmentNumber
1000	1000_100_ABC Company AMD 1-1-2001	1
1000	1000_525_ABC Company AMD 1-1-2005	2
1001	1001_105_Smith Inc AMD 4-5-2008	1
1002	1002_106_Tom's Auto AMD 2-3-2021	1
1002	1002_415_Tom's Auto AMD 2-3-2022	2
1002	1002_515_Tom's Auto AMD 6-3-2022	3
1003	1003_110_Apple Inc AMD 12-1-2012	1
1004	1004_125_XYZ Inc AMD 10-17-2014	1
1004	1004_485_XYZ Inc AMD 10-17-2021	2

There are two aspects to this question. The first is the one posed by Jack. What is the end goal? Amendment Number appears to be redundant since the DocumentName already sorts by ContractID and DocumentID. How would it be used?

The second is to create a query to do this, which can be done as illustrated in this Allen Browne page. It has one significant difference in that the ranking is overall, rather than by group. However, you can adapt it to your needs.

We can dig deeper into the query, of course, but let's start with a clearer picture of the requirement.

 

[monheimx9]

On SQL server it would be quite easy if you use partitions

SELECT ROW_NUMBER() OVER (
                PARTITION BY ContractID ORDER BY ContractID ASC
                ) AS [AmendmentNumber]
FROM your_Table_Name

However, I don't know if it works in Access SQL

Another solution would be to loop all the records

 

[Nickek02]

? What does AmendmentNumber give you that isn't available from the composite DocumentName??

Better to use the old standby for relational database ---1 fact in 1 field

I see ContractId, DocumentID,DocName,(not sure what AMD may represent) and a Date value.
Perhaps you could describe how all this fits together in business terms.

We are switching to a new Contract Lifecycle Management system. AMD is just a document short naming convention for Amendment. This new system requires the Amendments be numbered under each Contract using the Amendment Number Field.

 

[arnelgp]

you can create a Query to generate the Amendment Number?

SELECT
    ID,
    [Contract ID],
    [Primary Agreement File Name], 
    [Supporting Document Name]
    DCOUNT("1", "YOUR_TABLE_NAME_HERE", "ID <= " & [ID]) AS [Amendment Number]
FROM YOUR_TABLE_NAME_HERE
ORDER BY [Contract ID], [ID];

 

[jdraw]

Nickek02,
You say you are switching to a new system, but are you still using your old structure?
You have unnecessary redundancy in your DocumentName - it may come back to haunt you.
Is every Document really an Amendment as shown in your sample data?

You might get more advice and constructive feedback if you provide more info on the new system and
a graphic showing readers your proposed tables and relationships. (Relationship window with tables expanded to show all fields)
If you have a sample database with only a few "mocked up" records, then post that. It will give readers a broader picture of your set up.

Good luck.

 

[Gasman]

you can create a Query to generate the Amendment Number?

SELECT
    ID,
    [Contract ID],
    [Primary Agreement File Name],
    [Supporting Document Name]
    DCOUNT("1", "YOUR_TABLE_NAME_HERE", "ID <= " & [ID]) AS [Amendment Number]
FROM YOUR_TABLE_NAME_HERE
ORDER BY [Contract ID], [ID];

Wouldn't you also need the ContractID as criteria?

 

[arnelgp]

Wouldn't you also need the ContractID as criteria?

you got it.

 

[Nickek02]

Nickek02,
You say you are switching to a new system, but are you still using your old structure?
You have unnecessary redundancy in your DocumentName - it may come back to haunt you.
Is every Document really an Amendment as shown in your sample data?

You might get more advice and constructive feedback if you provide more info on the new system and
a graphic showing readers your proposed tables and relationships. (Relationship window with tables expanded to show all fields)
If you have a sample database with only a few "mocked up" records, then post that. It will give readers a broader picture of your set up.

Good luck.

The structure is completely different on our new vendors software. We have 3 different instances on two different Vendor Software Applications now each containing Client Contracts for different Sectors (Companies) within our Organization. The different Sectors may have the same Clients and each instance may have a Document with the same name. Each Document Name and matching physical file must be unique in the new Vendors system. There are 25 different document types but only Amendments and Addendums will need to be numbered like my example. To make each Document unique we added the ContractID and DocumentID to the name of each document as we have multiple documents per Client that can have the same name. The only thing I need to do at this point to finish the migration and send the data to the new Vendor is get the required Amendment Number for each Amendment Document by Contract.

 

[Pat Hartman]

The simple answer is to use a dMax() to assign the next sequential number as the row is added. If you want to add a sequence number to an existing row, it is a little harder. You can also generate the sequence number on the fly as you are copying the data from one table to another.

Here's a link that might help with the dMax() method.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

The second method is what you would use once you have assigned a sequence number to any existing data.