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. (1 Viewer)

[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.

 

[diamondDog]

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.

Thank you!!! this really clicked for me. Now I understand! I have an autonumber now, and found an example online that utilizes your suggestion in what might be a SELECT query, but now I am trying to figure out how to utilize the function in an Update query where the produced outcome updates the SeqNum column in an existing table.

The attached image is what produces what I want perfectly, but in a dataset. I would love to figure out how to apply this functionality in an Update query for a table. When I try to change this query to being Update and run it, I get an error message stating that the query “must have at least one destination field.”

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?

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

 

[diamondDog]

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.

So I introduced an autonumber ([ID]) field, which no one will really see. I want to update the SeqNum field with a running number based on the ClientName field value for each existing record in my table. The autonumber value probably won't matter at all because I plan on concatenating the ClientName and SeqNum fields into a user-facing unique ID field value.

Anyway, the pictured query creates a dataset with the sequential numbers as I desire, but how would I express this operation 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.

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

 

[GPGeorge]

Thank you!!! this really clicked for me. Now I understand! I have an autonumber now, and found an example online that utilizes your suggestion in what might be a SELECT query, but now I am trying to figure out how to utilize the function in an Update query where the produced outcome updates the SeqNum column in an existing table.

The attached image is what produces what I want perfectly, but in a dataset. I would love to figure out how to apply this functionality in an Update query for a table. When I try to change this query to being Update and run it, I get an error message stating that the query “must have at least one destination field.”

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 think it's a mistake to try to update a column in an existing table with a calculated value. This sequence is such a calculated value. As Pat pointed out, there is a risk of your saved, calculated sequence numbers going off the tracks when any of the component records is deleted. That WILL happen eventually and you won't know it until something else fails.

 

[Pat Hartman]

Will this query not work properly in an update query?

Did you try it? I said I don't think it will work in an append query and explained why. For an update query, it should be fine. To try it, just change the query to an update query. (do not forget to backup your database so you don't clobber production data. And just for safety, I would add criteria that only selects records where the sequence number is null. Did you check the select query? Does it show the values you want? Does it start the sequence with 0? If that is not what you want, you have to add +1 to the DCount() result

There is a difference between fixing old data, which is what you seem to be doing and adding new data. If you are going to use a custom sequence number, then the sequence number should be added at the time you add the record, not done later with an update query as you are now doing.

Storing calculated values is not recommended but, something like this should never change and if it gives your users some level of comfort, then it is fine as a user-friendly ID.

Notice that in the sample database, the sequence number is generated using dMax() NOT dCount(). The difference is that for your conversion of existing data you need to count the earlier records to add to the sequence but once the sequence number is populated for existing records, should you ever delete a record dCount() will not generate a unique sequence number. Therefore, for safety, you should ALWAYS use dMax() + 1.

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

 

[Pat Hartman]

Forgot to say "welcome". I just noticed your join date.

 

[diamondDog]

I think it's a mistake to try to update a column in an existing table with a calculated value. This sequence is such a calculated value. As Pat pointed out, there is a risk of your saved, calculated sequence numbers going off the tracks when any of the component records is deleted. That WILL happen eventually and you won't know it until something else fails.

Thank you for the response and the inciteful feedback! Right after my SeqNum column is calculated, I will immediately concatenate the SeqNum value into a combined field that will servce as the reliable value once it is established. WIll a concatenated field be updated when one of its original components is updated or is it sort of printed at the time of its creation? I hope it's the latter, as the SeqNum field won't matter in the record once the concatenated unqiue record ID is created.

 

[The_Doc_Man]

Pat has earlier expressed some doubts about the viability of this process. I must echo her idea. The problem with using an Update query OR an Append query (or, for that matter, a Delete query) is that they are action queries that don't return a recordset. Further, to my (admittedly sketchy) knowledge of the order of query operations, they only establish the set of records on which they will operate early in the process. By the time they get around to the actual operation, the behind-the-scenes recordset is fixed and immutable. You don't see the results after the query executes because you need a SELECT query to show you what happened. (Remember, action query = no returned recordset). Therefore, I am not sure what would even be available for evaluation in an action query. This has all the makings of a VBA loop with single-record-at-a-time Update or Append operation. Which, if the record count is high enough, can become a big, slow, ugly mess.

You also expressed some concern about continuity of numbering. Here is the rule to remember regarding autonumber. If you are going to use it, it CANNOT have ANY real meaning. If you have an auditor's rule that requires contiguous numbering with no gaps, autonumber is not your friend. Pat's comments about custom sequence numbers will be more relevant in that case. You use autonumber when the ONLY thing you need is that the number must be unique. The only way to use that is if you have parent/child table structure, the child records can have a foreign key that mirrors an autonumber primary key. That's ALL you are supposed to do with autonumber PK fields. They are for relationship management when you don't have a "natural" key so have to synthesize one.

 

[Pat Hartman]

WIll a concatenated field be updated when one of its original components is updated or is it sort of printed at the time of its creation?

Absolutely not. How would the database engine have any knowledge of how a field value was generated? When you store a calculated value, you NEVER, EVER change the parts it depends on. Or, you don't store the calculated value. Period.

The sequence number does matter and you always keep the constituent parts as independent fields. The question is rather do you store the mushed value. Not everyone agrees on whether it is OK to store the user-friendly ID or not. You need the sequence number separate from the concatenated userID so that you can use dMax() on it to generate the next value.