How do I set up sequential numbering for a table in one field for each alike value in another field?

[IronCat]

I have one table that gets a number of records imported into it several times a month. There are two fields I am trying to establish query programming for that will likely have to reference some VBA. I am a bit of an Access noob who sometimes has a hard time grasping how to cobble things together.

For each record in this table, the “SamplePeriod” column will already be populated with a value for each record that is imported. For each record with an identical value in this SamplePeriod field, I want a sequential three-digit count (001) to populate the “Case#” column of the same table. Whenever new cases with a matching SamplePeriod value are added, I want the count to continue where it left off for that specific SamplePeriod value.



What would the VBA look like for this and then how would I call on this VBA to apply to all records within my table where the Case# field is blank (as those would be the newly imported records that need their SamplePeriod field looked at to generate the next sequential # in the Case#). The sequential numbering doesn’t have to reference any other data in each record to be in a certain order; I just want to assign a number to all the new cases but I do want a sequential order for each unique SamplePeriod value.

I feel like DMAX will be involved, but I get overwhelmed with trying to make sense of the VBA. I learn so much from seeing how proven VBA code works but I have had a hard time finding the VBA relationship I am looking for.

Thanks for any and all guidance!

[Micron]

You probably have the option to do this with a query as well. Is vba a requirement?
I suggest you open the table, copy enough records to define the pattern and paste the data in a post. Or mock up something in Excel and paste that in a post. Data is usually better than describing the source data/field names/etc. and it gives anyone something to test on your specific situation.

[Welshgasman]

I believe you would need an autonumber field as well to control the order.
Then you would use something like mentioned here
https://answers.microsoft.com/en-us/...e-226d972ea8a9

with additional criteria of SamplePeriod being equal

[Welshgasman]

Now crossposted at https://www.access-programmers.co.uk...-query.332237/

[NoellaG]

If you have regular imports of several rows I would create a table ImportBatches with the fields:
- batchNumber (autonumber)
- Date import (date/time)
- StartImportNr (First new line number)
- EndImportNr( Last new line Nr)
- Filename: name of the import file (optional)

In the table itself you add the field ImportBatchNr (long integer) that you link to field ImportBatches.batchNumber
With each new import you:
- get the max(EndImportNr) from table ImportBatches
- add a new batch line where StartImportNr = max(EndImportNr) + 1
- import new lines with new batch number
- update the batch record : EndImportNr = StartImportNr + total imported lines

With this method you have the extra batch info when each line is imported and from which file

[IronCat]

You probably have the option to do this with a query as well. Is vba a requirement?
I suggest you open the table, copy enough records to define the pattern and paste the data in a post. Or mock up something in Excel and paste that in a post. Data is usually better than describing the source data/field names/etc. and it gives anyone something to test on your specific situation.

VBA is not a requirement at all and I prefer to have this achieved entirely in a Query, if possible, as I want updates applied to a large collection of records all at once.

I found a query example online that works very well to create a running sequence of numbers in one field based on identical values in another field where new records imported with the same ClientName value will have the SeqNum field populate with the next sequential number for that client name.

Anyway, the pictured query creates a dataset with the sequential numbers as I desire, but how would I express the above functions in an Update query? When I tried to change this query to an Update query, and ran it, I got an error message stating that the query “must have at least one destination field.”

I want to have an update query that updates the blank SeqNum field for each record of data which will have the ClientName prefilled. Will this query not work properly in an update query? Would I have to just save this query and have it referenced to run inside of an Update query, and if so, what would that look like?

I trip over myself when it comes to Queries. Thank you for your time.

SeqNum: DCount("[ClientName]","Query1","[ClientName] = '" & [ClientName] & "'")-DCount("[ClientName]","Query1","[ClientName] = '" & [ClientName] & "' AND [ID] > " & [ID])

[NoellaG]

You can't update inside a groupsquery. What you can do is insert the result of the groups query into another table or use the results to update data in a linked table.