How do I set up sequential numbering in one field of a table for each alike value in another field? I want to have this applied in a query.

[diamondDog]

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!

 

[arnelgp]

you don't need Case# field on your table because you can get the sequential number in a query.
firstly you need to add an Autonumber (ID) field to your table.

select samplePeriod, DCount("1", "yourTableName", "samplePeriod = #" & Format([samplePeriod], "mm/dd/yyyy") & "# And ID <= " & [ID]) As Case#
from yourTableName Order By samplePeriod, ID;

 

[diamondDog]

you don't need Case# field on your table because you can get the sequential number in a query.
firstly you need to add an Autonumber (ID) field to your table.

select samplePeriod, DCount("1", "yourTableName", "samplePeriod = #" & Format([samplePeriod], "mm/dd/yyyy") & "# And ID <= " & [ID]) As Case#
from yourTableName Order By samplePeriod, ID;

Thanks for the response! Doesn't Autonumber get tricky though? and there will be different SamplePeriod values and so there would be a number of multiple sequences, one per unique SamplePeriod field value. Autonumber plays well having multiple sequences per table?

I always hear that it's easy to create a record that you then have to delete and then that AutoNumber value is just gone and you cannot get that # back for the sequence. I am a real noob, but I thought I heard these things, which is why I thought it might be simpler just to add a Case# column.

 

[GPGeorge]

Thanks for the response! Doesn't Autonumber get tricky though? and there will be different SamplePeriod values and so there would be a number of multiple sequences, one per unique SamplePeriod field value. Autonumber plays well having multiple sequences per table?

I always hear that it's easy to create a record that you then have to delete and then that AutoNumber value is just gone and you cannot get that # back for the sequence. I am a real noob, but I thought I heard these things, which is why I thought it might be simpler just to add a Case# column.

The actual values of the AutoNumbers don't matter. What matters is that, presumably, each new record is added with an AutoNumber greater than the previous AutoNumber value. This means the comparison ID <= " & [ID] will sort records appropriately for this task of creating a sequence for the query at that point in time.

 

[Gasman]

Crossposted at https://www.accessforums.net/showthread.php?t=90158

 

[Pat Hartman]

The question is do you need to save the number or is it just a counter that you want to show? If you allow deletes and you delete record 2, the query method where you generate the count on the fly will ignore the fact that record 2 ever existed. However, if you save the sequence number, then there will be a gap.

Also, and I don't have time to check this but, I'm not sure you can generate a sequence number in an append query because the DCount() looks at the existing data in the table, it is not considering the new records which are not added until the end of the process. So, the DCount() will always return the same value so the generated number will be the same for every new row. When I use this type of sequence number, the rows are typically added using a form so the sequence number is generated one row at a time. With a bulk append, you have a different problem and so if you really need the generated number to be stored, you may need to use a VBA loop to add the rows one at a time using a counter you control in the code loop. An alternative may be to create a function with a static counter but you will need a way to set/reset the static counter as a separate step.