Assign incrementing number based on a common field

[Matt16]

Hi Guys,



Trying to do something that seems pretty simple but is doing my head in. My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.

So in simple terms, return all trips for each docket number then number each trip. Hoping someone can assist.

Cheers

Matt

[June7]

What is the need for this? This line numbering can be done dynamically in a report. Textbox in report has a RunningSum property. Saving this line number in table will require some VBA code and can get tricky.

It is also a common topic in forum. Search forum or Google: VBA sequential record number

[Matt16]

The need is driven from a business and system requirement to sequentially number each trip on a docket by date/time and maintain unique Trip ID's. The docket number and trip number are concatenated to form a unique trip ID. These trips are fed to a back end system that requires unique trip ID's.

I've done a fair bit of searching before posting but couldn't find a relevant answer - I'll try that search term.

[June7]

Here is one for starters https://www.accessforums.net/forms/h...eld-33966.html

The real trick is figuring out what event to put code in.

An autonumber field could be used as unique trip ID if you don't care about gaps in sequence. Then concatenate the docket number and autonumber field for a value that has meaning to users.

[Matt16]

Thanks for that but I missed a key piece of information - apologies. The data is entered initially via an import and subsequently by manual keying for any missing trips. I'm currently using an autonumber, but that numbers trips based on the import then the manual keying, meaning the end result numbers are all over the place. I really need a function to go back at the end of processing and select all rows by docket number, ordered by date/time and sequentially number the results for that docket in the TripNo field.

Any further assistance would be appreciated!

[June7]

Review https://www.accessforums.net/queries...ers-33632.html

That thread shows how to run UPDATE SQL to generate a unique sequential ID for all records in a table meeting filter criteria.

In your case the filter criteria would be the docket number.