rodrunner79
IS-IT--Management
Hello everyone,
I have not been in this site for quite some time now. It seems that I only come here when I need something and always leave with a smile on my face because everyone is just very helpful and very professional. I am somewhat still intimidated to post a reply even if I think I have the answer to someone's question because someone always has a better answer; which I don't mean any harm by it, it just shows how intelligent members are in this site.
Anyway, again, I came here to post an issue that's been puzzling me and need help getting it resolve. I tried searching the older threads and other forums and can't seem to find the correct link to help me.
Here's my dilemna: I have a table that has a primary key that is 23 chars in length, the primary (unique) key is generated by concatenating several fields together, including a Julian Day function. This method creates a unique value for each record permanently, there will never be a time were a record is duplicated. Well, the problem is, I've been ask to change the field lenght from 23 chars to 6 chars max.
If I use Access autonumbering method, I will be limited to 999,999 records because that's the number of permutations that a 6 digit field can produce. Therefore, I need some type of routine that will generate an auto-value with one character prefix (e.g. A000001). To accomplish this, I created a sample database with 3 tables. First table (tblAutoNoPrefix) contains 3 fields: the first field called "ID: corresponds to the second field called "Prefix", and the third field "DefVal" indicates whether that record is the default value which will be use in a DLookup function.
The second table (tblTransactions_Permanent) will be use to append the data from the third table (tblTransactions_Temp). That's where the problem lies, I need a autonumbering scheme that look up the default value on the first table and assigns a sequential number to it by adding 1 to the last number use. But I don't know how to do it.
With that said, I am glad to be here as always and any help is appreciated.
-=True wisdom comes from knowing you know nothing.=-
I have not been in this site for quite some time now. It seems that I only come here when I need something and always leave with a smile on my face because everyone is just very helpful and very professional. I am somewhat still intimidated to post a reply even if I think I have the answer to someone's question because someone always has a better answer; which I don't mean any harm by it, it just shows how intelligent members are in this site.
Anyway, again, I came here to post an issue that's been puzzling me and need help getting it resolve. I tried searching the older threads and other forums and can't seem to find the correct link to help me.
Here's my dilemna: I have a table that has a primary key that is 23 chars in length, the primary (unique) key is generated by concatenating several fields together, including a Julian Day function. This method creates a unique value for each record permanently, there will never be a time were a record is duplicated. Well, the problem is, I've been ask to change the field lenght from 23 chars to 6 chars max.
If I use Access autonumbering method, I will be limited to 999,999 records because that's the number of permutations that a 6 digit field can produce. Therefore, I need some type of routine that will generate an auto-value with one character prefix (e.g. A000001). To accomplish this, I created a sample database with 3 tables. First table (tblAutoNoPrefix) contains 3 fields: the first field called "ID: corresponds to the second field called "Prefix", and the third field "DefVal" indicates whether that record is the default value which will be use in a DLookup function.
The second table (tblTransactions_Permanent) will be use to append the data from the third table (tblTransactions_Temp). That's where the problem lies, I need a autonumbering scheme that look up the default value on the first table and assigns a sequential number to it by adding 1 to the last number use. But I don't know how to do it.
With that said, I am glad to be here as always and any help is appreciated.
-=True wisdom comes from knowing you know nothing.=-