Create running sequence number

[lookforsmt]

Hi! all

I have searched in almost all the forums and unable to find anything close to my requirement.

i have a table with below field:

Field Name Data Type Field Size Sample
------------ ------------ ----------- --------
SrNoID AutoNumber
Prefix Text 2 digit AA
Seq Text 6 digit 000001
Suffix Text 4 digit 0001

I want to get the final result as:

AA-000001-0001
AA-000001-0002

AA-000001-9999
AA-000002-0001

AA-999999-9999
ZZ-999999-9999

Background:
- i receive a list of data approx 500 to 600 rows daily, which i need to tag a code (AA-000001-0001) to each of the rows.
- It should generate only that many codes for which data is received daily.
- The sequence number should lookup in the table where the generated codes will be saved and start from the next sequence nos to the end of the row and save the new codes and so on.

Currently I do this in excel by dragging the Alpha Numbers in columns and then importing it into access. But this is manual work and tedious which i want to avoid.

Is there a way that i can do this automatically in access.

Any suggestions how should i approach this in access pls.

 

[lookforsmt]

attach the snap shot of the code

 

[plog]

Not to get too shakespearan on you, but a number by any other name is still a number:

AA-000001-0001 = 1
AA-000001-9999 = 9999
AA-000002-0001 = 1000
AA-999999-9999 = ? (I don't know but there's a formula)
ZZ-999999-9999 = (AA-999999-9999)*676 + 675

So all those fields you have to store this thing are unnecessary. You only need one numeric field to store this data and then a function to convert that number to your number format.

With that said 2 observations:

1. If you started at AA-000001-0000 and allowed all -0000 numbers this thing would be a lot easier. It would simply be base 10, what you have now is a base 10 with an exception at -9999 which makes your function a little more difficult.

2. Why must these numbers be sequential without gaps? If you simply used an autonumber then that takes care of assigning numbers and you simply have to work on your number format function.

 

[lookforsmt]

Thanks for the response

Just to clarify:
These sequence will be generated for only those rows which are received, so it will not be till the ZZ-999999-9999 in the beginning. it will eventually get to that sequence at the end. But that will be some time now.

I am not able to understand the logic and the 1st point that you are trying to make me understand.
For point 2, the numbers can be in any sequence as long as it full fills the total digits which equals 14 digits

 

[plog]

My point is you don't need a complex numbering scheme in your table. You need a complex numbering formating function. Throw away your 3 text fields (Prefix, Seq & Suffix) and replace them with one numeric field. Numbers are made for math (in your case adding one to the prior), text fields are not. To store your sequence you simply need one number field.

Then, to display your sequence you need a function to convert a number to that complex format you have. In all honesty, I would just use that autonumber you have (SrNoId) and format it to the number format you want.

 

[Minty]

To back up what Plog is getting at just look at the last 5 digits of your sample data.


1-0001
1-0002

1-9999
2-0001

9-9999

If you removed the - you have a simple sequence of, wait for it, numbers.

So just increment a number then apply a format, and convert the leading two digits (if you ever get that far) into an Alpha representation using a function.

 

[lookforsmt]

thanks i got you point. Let me do some search on this forum to find some functions which can get me to that sequence number format.

I dont have very good knowledge in writing vba codes.

 

[lookforsmt]

i have created table with 2 fields, 1st is auto number and 2nd is number field (sqbrcd)
Now this table should generate e.g. 150 sequence numbers, since the data received is 150 row in another table.

 

[plog]

I'd really advise against a seperate field you have to generate the value for. Why not just use your autonumber field? It's going to be sequential, its going to be numeric. The only issue you might encounter is that it possibly could skip a few numbers. Who's that going to kill if it happens?

If you decide to use a seperate field you will have to generate the value for every record. This will involve using DMax to get the highest used value, then adding 1 to it for the new number. Search this form for "custom sequence number" to find examples of it being done along with more arguments against it.

 

[lookforsmt]

ok i hear you, i have now just one field autonumber and will have look at the forum for the custom sequence number. Hope i find something.

 

[lookforsmt]

i have found one post and tried in my project

Public Function fnSequence() As Variant
Dim ret As Variant
ret = DMax("chqbrcdId", "tbl_chqbrccd", "Left([chqbrcdId],2) = " & Format(Date, "yy") & " And Mid([chqbrcdId], 3, 2) = " & Format(Date, "mm"))
If IsNull(ret) Then
ret = Format(Date, "yy") & Format(Date, "mm") & "01"
Else
ret = Format(Val(ret) + 1, "000000")
End If
' add this number to our table
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into tbl_chqbrcd (chqbrcdId) SELECT " & ret & ";"
fnSequence = ret
End Function

any help from here pls

 

[Mark_]

Best help?

Stay with

SrNoID AutoNumber

.

To what use is this number being put? If we know WHY you need a SECOND sequential number that may explain what you are after.

 

[lookforsmt]

i am not sure how to get the sequence AA-000001-0001. As i said there is another table which will tell me how many records received in that table based on this that many sequence nos will be generated.

 

[MarkK]

There are people in the world who believe they need to construct a value laden human readable number to attach to things. It needs to be sequential, or have the first three letters of the last name, or restart from zero every July 15th. I did some work on someone's system in which the most complicated code, and the biggest problem that was solved was predicting, constructing, attaching, managing, and keeping current THE JOB NUMBER. The job number was composed of a part of the customer name, the year, the month, the ID of the salesman, the ID of the tech, the type of the job, and a sequence number restarting Jan 1 FOR EACH JOB TYPE! It was the primary key! I ditched it all for an Autonumber and relinked all the child records, and it solved a ton of problems with orphaned records resulting from data edits. The main problem they had was the franken-code itself.

So, to me, think about why you need some complex value-laden human-readable franken-code? What value does it add to you data? If humans need to read a label to find something, print actual data on that label, customer name, year, etc... There is no need to chop up little bits of data and stick them back together in a concatenated monstrosity that you save somewhere so humans can understand your data. Just show them actual data!

My 2c...
Mark

 

[lookforsmt]

The sequence is actually a barcode which i print and attach to the physical file which i store in the warehouse.

I was happy printing the barcode which generated the sequence based on the total records for that day. It was in format EN-081217-0001. But i had a problem, on the stationary (A4 self sticker stationary) which prints 68 codes per page, leave the last page with some blank space which i cannot use. So i decided to change the coding to AA-000001-0001 without giving any reference.

This is the reason i want to print the barcodes in different form.

 

[Mark_]

If this is a barcode, do you need

AA-000001-0001

instead of

00-000000-0001

I do ask because some systems have issues with barcodes beginning with "00".

 

[lookforsmt]

i am not sure if there will be any issue with the barcode starting with 00-
i need to check this at work, will be able to tell you only tomorrow, its past mid-night here.

 

[The_Doc_Man]

OK, the number you are showing actually cannot be generated by a sequential autonumber plus a remap anyway. I don't think you can autonumber a QUAD integer, besides which it might not be available on older version of Access. And a LONG integer of 32 bits only gives you 9 reliable digits. Your scheme encompasses more than 9 digits total. Two digits A-Z is 676, then -000001 to 999999 might as well be a million, and -0001 to -9999 is another ten thousand, so call it roughly 67,610,000,000,000, short version 67 trillion and change.

Unlike the others, I see that you can't get there from here without a compound key. So if this number is really, honestly, truly what you want for business reasons, my advice is to read up on VBA so that you can write a subroutine to generate the numbers you want in a compound key comprised of a Text(2) field plus a LONG field plus a WORD field. These three fields would become your compound primary key. I won't write the subroutine for you but I'll outline it.

Pass in three fields - the text, LONG, and WORD parts as separate fields - by reference. Increment the WORD field. If it flips from 9999 to 10000, reset it to 0 (or 1) and increment the LONG field. If THAT flips from 999999 to 1000000, reset it to 0 (or 1) and look at the text field. Break that apart into first character, second character.

You would normally step to the next character in ASCII sequence with

character = Chr$( ASC( character) + 1 )

However, if the second character is equal to Z, reset it to A and increment the first character. Remember, when dealing with character strings, you number from the left, so for the string AX, the first character is A and the second character is X, but using this scheme you would increment AX to AY.

Now the only other thing to remember is that the formatting of this mess requires you to format the LONG and the WORD using the "leading zeros" format, which is a "0" character in the format string rather than a "#" character. The dashes? Supply them as constants using concatenation.

I'm also going to point out the obvious, since the "Captain Obvious" character in the Brewster Rockit strip was modeled after me. If you have 67.6 trillion of anything, you will exceed the capacity of an Access database very quickly. If if you had only ONE BYTE per record, 67.6 trillion is bigger than 2 GB and a single Access database file will NOT get bigger than 2 GB. So, do you really need your bar codes to go that big? I'm not even sure that an ORACLE Enterprise Server database hosted on ANY Windows box that I know could ever get THAT big. UNIX boxes might have trouble with it.

So... here's the next question. The sample you gave us in post #15 LOOKS like it might have a date encoded in the middle section, which would RADICALLY alter the nature of your question. Please explain in greater detail what you really seek here.

I analyzed your original question, dashed a small amount of reality into the picture, and then pointed out what appears to be a strange coincidence in the sample you gave us - and I don't believe in that kind of coincidence. So what does that middle number represent?

 

[Solo712]

OK, the number you are showing actually cannot be generated by a sequential autonumber plus a remap anyway. I don't think you can autonumber a QUAD integer, besides which it might not be available on older version of Access. And a LONG integer of 32 bits only gives you 9 reliable digits. Your scheme encompasses more than 9 digits total. Two digits A-Z is 676, then -000001 to 999999 might as well be a million, and -0001 to -9999 is another ten thousand, so call it roughly 67,610,000,000,000, short version 67 trillion and change.

Unlike the others, I see that you can't get there from here without a compound key. So if this number is really, honestly, truly what you want for business reasons, my advice is to read up on VBA so that you can write a subroutine to generate the numbers you want in a compound key comprised of a Text(2) field plus a LONG field plus a WORD field. These three fields would become your compound primary key. I won't write the subroutine for you but I'll outline it.

Pass in three fields - the text, LONG, and WORD parts as separate fields - by reference. Increment the WORD field. If it flips from 9999 to 10000, reset it to 0 (or 1) and increment the LONG field. If THAT flips from 999999 to 1000000, reset it to 0 (or 1) and look at the text field. Break that apart into first character, second character.

You would normally step to the next character in ASCII sequence with

character = Chr$( ASC( character) + 1 )

However, if the second character is equal to Z, reset it to A and increment the first character. Remember, when dealing with character strings, you number from the left, so for the string AX, the first character is A and the second character is X, but using this scheme you would increment AX to AY.

Now the only other thing to remember is that the formatting of this mess requires you to format the LONG and the WORD using the "leading zeros" format, which is a "0" character in the format string rather than a "#" character. The dashes? Supply them as constants using concatenation.

I'm also going to point out the obvious, since the "Captain Obvious" character in the Brewster Rockit strip was modeled after me. If you have 67.6 trillion of anything, you will exceed the capacity of an Access database very quickly. If if you had only ONE BYTE per record, 67.6 trillion is bigger than 2 GB and a single Access database file will NOT get bigger than 2 GB. So, do you really need your bar codes to go that big? I'm not even sure that an ORACLE Enterprise Server database hosted on ANY Windows box that I know could ever get THAT big. UNIX boxes might have trouble with it.

So... here's the next question. The sample you gave us in post #15 LOOKS like it might have a date encoded in the middle section, which would RADICALLY alter the nature of your question. Please explain in greater detail what you really seek here.

I analyzed your original question, dashed a small amount of reality into the picture, and then pointed out what appears to be a strange coincidence in the sample you gave us - and I don't believe in that kind of coincidence. So what does that middle number represent?

It's not as bad as that Doc, is it ?

' Function generates next code in sequence
' to a passed argument in the form of
' ZZ-000001-0001
Public Function GenInvCode(gnIN As String) As String
  Dim oo As Variant, a As Long, b As Long, pfx As String, om As String
   
  
  pfx = Left(gnIN, 2)
  a = Val(Mid(gnIN, 4, 6))
  b = Val(Mid(gnIN, 11, 4))
  
  oo = 10 ^ 10 + a * 10 ^ 4 + b + 1
  
  '
  om = CStr(Mid(oo, 2, Len(oo)))
  If om = "0000000000" Then
     If Right(pfx, 1) = "Z" Then
        pfx = Chr$(Asc(Left(pfx, 1)) + 1) + "A"
     Else
        pfx = Left(pfx, 1) + Chr$(Asc(Right(pfx, 1)) + 1)
     End If
  End If
  ' if all zeros are not allowed in the two numeriuc zones
  If Mid(om, 1, 6) = "000000" Then Mid(om, 1, 6) = "000001"
  If Mid(om, 7, 4) = "0000" Then Mid(om, 7, 4) = "0001"

  GenInvCode = pfx & "-" & Mid(om, 1, 6) & "-" & Mid(om, 7, 4)
End Function

Best,
Jiri

 

[The_Doc_Man]

Jiri, my point was that while the question asked in post #1 could be answered by the code I briefly outlined, the subsequent discussion contained some suspicious anomalies that made me pause because I don't think the question as originally asked is consistent with later posts.

I decided to not write any code until we resolved the question of that middle 6-digit number that looks awful much like a "mmddyy" date string in the later example. If that is the case, then there is a MASSIVE inconsistency in the problem description and I don't know what I'm programming. Didn't want to waste the coding effort until I was sure of the REAL question. So I asked for clarification of meaning on a fine point.

Further, if the number really IS just a big, sequential up-counter with a formatting issue, where will it be stored? Remember,

i receive a list of data approx 500 to 600 rows daily, which i need to tag a code (AA-000001-0001) to each of the rows.

This sequence number is being used as a tag on a longer record. You can't hold as many records as are implied by that size of a sequence number in an Access BE file, for sure, with only 2 GB per file.

Also, the OP claims 500-600 items per day. At that rate, if it is truly sequential, it will take maybe 67.6 trillion / 600 = 0.112 trillion DAYS to count that high, so it will take 200 days to reach the increment of the second field, and 200 MILLION days to increment the first letter. Forgive me if I think maybe the program in question and the problem description might be a tad impractical. But it makes a lot more sense if the middle digits are a date even though the original description SEEMS to imply that it is not.

Because I saw those questions, I realized we didn't know everything we needed to know about the problem. Or at least I don't see it as a discrete whole. There is a disconnect somewhere. OK, maybe I'm being a bit harsh, but I just think we have an incomplete description, that's all.

So yes, for me it WAS that bad.