If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unique fields using AAAA instead of/as well as AutoNum
Hi,
Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#2
|
|||
|
|||
For what purpose? It's rare that you even show the value of an AutoNumber
field to the end user. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#3
|
|||
|
|||
Hi Doug,
It's all very bizarre really, and originates from a legacy IBM system which generated a unique client account ID of 4char. Not a problem initially but it now appears that a 3rd party service provider requires the continuation of the unique 4char Account ID as a sinon to their web support service - a service which just happens to be a key part of the product being offerred. For some reason the 4char Account ID were never generated in the range GLAA-GLZZ. Therefore, an ideal opportunity to start new business account ID's during the interim, until a replacement strategic web based solution is implemented replacing the whole lot over the next 6 months (which of course would be the time it takes the 3rd party to change it's login process and 5 months later than the commencement of issuing new business) Hence trying to find some way around this, if you see what I mean. Ben "Douglas J. Steele" wrote: For what purpose? It's rare that you even show the value of an AutoNumber field to the end user. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#4
|
|||
|
|||
I'd keep your standard AutoNumber field as the PK for the table, but add
another field where you generate the 4 character field. Put a unique index on the field. Write a function to generate the character key. Obviously the first two characters will always be "GL". Generate a random number between 1 and 26 for the next two characters using the RND function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BenjieB" wrote in message ... Hi Doug, It's all very bizarre really, and originates from a legacy IBM system which generated a unique client account ID of 4char. Not a problem initially but it now appears that a 3rd party service provider requires the continuation of the unique 4char Account ID as a sinon to their web support service - a service which just happens to be a key part of the product being offerred. For some reason the 4char Account ID were never generated in the range GLAA-GLZZ. Therefore, an ideal opportunity to start new business account ID's during the interim, until a replacement strategic web based solution is implemented replacing the whole lot over the next 6 months (which of course would be the time it takes the 3rd party to change it's login process and 5 months later than the commencement of issuing new business) Hence trying to find some way around this, if you see what I mean. Ben "Douglas J. Steele" wrote: For what purpose? It's rare that you even show the value of an AutoNumber field to the end user. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#5
|
|||
|
|||
AA to ZZ is 676 records (26*26). Keep your autonumber. Make the last
autonumber you have GLAA and create a function that calculates the four characters based on the number of records entered since your last autonumber. For example: say your last autonumber is 34511 and records have been added and none deleted to where your current autonumber is 34536. 25 records have been added so your four characters are GLAZ. BTW, you can test if there are any gaps in the numbers to determine if any records have been deleted after 34511. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#6
|
|||
|
|||
Nice one Doug,
Now to write the function...DOH! Many thanks, Ben "Douglas J. Steele" wrote: I'd keep your standard AutoNumber field as the PK for the table, but add another field where you generate the 4 character field. Put a unique index on the field. Write a function to generate the character key. Obviously the first two characters will always be "GL". Generate a random number between 1 and 26 for the next two characters using the RND function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BenjieB" wrote in message ... Hi Doug, It's all very bizarre really, and originates from a legacy IBM system which generated a unique client account ID of 4char. Not a problem initially but it now appears that a 3rd party service provider requires the continuation of the unique 4char Account ID as a sinon to their web support service - a service which just happens to be a key part of the product being offerred. For some reason the 4char Account ID were never generated in the range GLAA-GLZZ. Therefore, an ideal opportunity to start new business account ID's during the interim, until a replacement strategic web based solution is implemented replacing the whole lot over the next 6 months (which of course would be the time it takes the 3rd party to change it's login process and 5 months later than the commencement of issuing new business) Hence trying to find some way around this, if you see what I mean. Ben "Douglas J. Steele" wrote: For what purpose? It's rare that you even show the value of an AutoNumber field to the end user. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#7
|
|||
|
|||
Hiya,
Thanks for this...greatly appreciated. As it's for a high value low turnover product application with an extremely optimistic estimate of 30 quotes max per month, 676 should be more than sufficient for a 6 month interim solution. Also am none too sure of the feasibility of this due to the user wants/needs to generate the 4char during the quote stage; ineffect the quote ID becomes the acount ID upon sale as it were. Ben "PC Datasheet" wrote: AA to ZZ is 676 records (26*26). Keep your autonumber. Make the last autonumber you have GLAA and create a function that calculates the four characters based on the number of records entered since your last autonumber. For example: say your last autonumber is 34511 and records have been added and none deleted to where your current autonumber is 34536. 25 records have been added so your four characters are GLAZ. BTW, you can test if there are any gaps in the numbers to determine if any records have been deleted after 34511. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#8
|
|||
|
|||
That's OK! Just use the method with QuoteID when entering quotes to generate
the four character unique field. Orders are related to quotes so just carry over the four character field when entering an order. Don't calculate the four characters from OrderID. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "BenjieB" wrote in message ... Hiya, Thanks for this...greatly appreciated. As it's for a high value low turnover product application with an extremely optimistic estimate of 30 quotes max per month, 676 should be more than sufficient for a 6 month interim solution. Also am none too sure of the feasibility of this due to the user wants/needs to generate the 4char during the quote stage; ineffect the quote ID becomes the acount ID upon sale as it were. Ben "PC Datasheet" wrote: AA to ZZ is 676 records (26*26). Keep your autonumber. Make the last autonumber you have GLAA and create a function that calculates the four characters based on the number of records entered since your last autonumber. For example: say your last autonumber is 34511 and records have been added and none deleted to where your current autonumber is 34536. 25 records have been added so your four characters are GLAZ. BTW, you can test if there are any gaps in the numbers to determine if any records have been deleted after 34511. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
#9
|
|||
|
|||
Many thanks for your help on this one.
Ben "PC Datasheet" wrote: That's OK! Just use the method with QuoteID when entering quotes to generate the four character unique field. Orders are related to quotes so just carry over the four character field when entering an order. Don't calculate the four characters from OrderID. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "BenjieB" wrote in message ... Hiya, Thanks for this...greatly appreciated. As it's for a high value low turnover product application with an extremely optimistic estimate of 30 quotes max per month, 676 should be more than sufficient for a 6 month interim solution. Also am none too sure of the feasibility of this due to the user wants/needs to generate the 4char during the quote stage; ineffect the quote ID becomes the acount ID upon sale as it were. Ben "PC Datasheet" wrote: AA to ZZ is 676 records (26*26). Keep your autonumber. Make the last autonumber you have GLAA and create a function that calculates the four characters based on the number of records entered since your last autonumber. For example: say your last autonumber is 34511 and records have been added and none deleted to where your current autonumber is 34536. 25 records have been added so your four characters are GLAZ. BTW, you can test if there are any gaps in the numbers to determine if any records have been deleted after 34511. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "BenjieB" wrote in message ... Hi, Does anyone know of a way of creating an auto generated/increasing in value field (key) that uses chars instead of (or in addition to) Autonum e.g. GLAA, GLAB, GLAC, GLAD...etc.? Any ideas, views, comments or help would be very welcome. Thanks Ben |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Many-to-many implementation problem | Al Williams | Database Design | 15 | April 29th, 2005 05:19 PM |
Additional fields for form based parameter query/null fields | geeksdoitbetter | Running & Setting Up Queries | 2 | January 7th, 2005 10:05 PM |
Populate fields in Form Header based on header fields in startup f | Pat Dools | Using Forms | 0 | January 7th, 2005 03:19 PM |
Selecting Fields for Update | Steve Daigler | Page Layout | 4 | October 15th, 2004 02:13 PM |
Table fields with unique future dates as drop down | Jennifer B. | General Discussion | 4 | June 9th, 2004 05:44 PM |