Auto Number with Letter Prefix

Sorry, my brain leaked out of my ears like a runny nose. I should say I've
worked on Excel for one week to be exact and am now "in charge" of 1800 lines
of data.

I have a master sheet of every person who has appeared in a show for five
seasons. Each season has 12 shows. So my serial number wouldn't have the
same TAV 101 prefix. It will go all the way to TAV 509 (for Season 5 show 9)

Do you still recommend using a long integer field? There is going to be a
point where I will have to just type the numbers in manually. Reason is I'm
only temperorary and eventually some other clueless monkey will be running
the database. I was looking for a formula to type in and be done with it but
please let me know if this won't work. Thanks for your advice...

"John W. Vinson" wrote:

> On Sat, 29 Aug 2009 17:03:01 -0700, Ray wrote:
>
> >Unique Serial Numbers:
> >Here is the issue: I want to give everyone on a list a unique "serial
> >number" Except that serial number should begin with a prefix, in this case
> >"TAV 101" So the end product in this column should be a unique number that
> >appears like this "TAV 101-1" "TAV 101-2" "TAV 101-3" I've looked on the
> >forums and in help and I don't think I'm wording my question right. Can
> >anyone help?
>
> If every record has the same prefix, then you need not - and should not -
> store it in the table at all. You can use a Format property (in a table field,
> or as the Format of a form or report textbox) such as
>
> "TAV 1\01-#"
>
> to display the literal characters followed by a number.
>
> However, Autonumbers are not really fit for human consumption. Their ONLY
> purpose is to provide a meaningless unique key. They will always have gaps; if
> you delete a record it will leave a gap; if you even hit <ESC> while entering
> a new record it will leave a gap; if you use an Append query to populate the
> table it can leave a huge gap; the numbers can even become random (if you
> Replicate). If you want sequential numbers, use a Long Integer field and
> manage them yourself, manually or with code - search the groups for "Custom
> counter" for examples.
> --
>
> John W. Vinson [MVP]
>
 >> Stay informed about: Auto Number with Letter Prefix 

Ray, this newsgroup is not for EXCEL. It is for technical discussion of and
questions and answers about Microsoft ACCESS database software. In the
online interface you used to post here, you need to scroll farther to find
an Excel newsgroup.

John gave you an "Access Answer" but seems you may have been asking an
"Excel Question". They are not the same, and don't work the same.

Please note that John didn't suggest that his solution would work only if
the whole serial number were "prefix"... he was talking about the same
_character_ prefix "TAV" which does seem consistent. How did you plan to
generate the "101" to "509" part of the number? But, for Access, his idea
would work for multiple characters that are alphanumeric.

You should also be aware, in Access, that an Autonumber is strictly intended
for use internal to the database, to guarantee uniqueness, and to join
records in related tables -- it is not guaranteed to be "consecutive",
"monotonically increasing" as you may have assumed, and it is, by
definition, numeric... it is a Long Integer with an autoincrement feature.
But, there are a number of situations in which it may end up being
non-consecutive.

Larry Linson
Microsoft Office Access MVP

"Ray" wrote in message

> Sorry, my brain leaked out of my ears like a runny nose. I should say
> I've
> worked on Excel for one week to be exact and am now "in charge" of 1800
> lines
> of data.
>
> I have a master sheet of every person who has appeared in a show for five
> seasons. Each season has 12 shows. So my serial number wouldn't have the
> same TAV 101 prefix. It will go all the way to TAV 509 (for Season 5 show
> 9)
>
> Do you still recommend using a long integer field? There is going to be a
> point where I will have to just type the numbers in manually. Reason is
> I'm
> only temperorary and eventually some other clueless monkey will be running
> the database. I was looking for a formula to type in and be done with it
> but
> please let me know if this won't work. Thanks for your advice...
>
> "John W. Vinson" wrote:
>
>> On Sat, 29 Aug 2009 17:03:01 -0700, Ray wrote:
>>
>> >Unique Serial Numbers:
>> >Here is the issue: I want to give everyone on a list a unique "serial
>> >number" Except that serial number should begin with a prefix, in this
>> >case
>> >"TAV 101" So the end product in this column should be a unique number
>> >that
>> >appears like this "TAV 101-1" "TAV 101-2" "TAV 101-3" I've looked on the
>> >forums and in help and I don't think I'm wording my question right. Can
>> >anyone help?
>>
>> If every record has the same prefix, then you need not - and should not -
>> store it in the table at all. You can use a Format property (in a table
>> field,
>> or as the Format of a form or report textbox) such as
>>
>> "TAV 1\01-#"
>>
>> to display the literal characters followed by a number.
>>
>> However, Autonumbers are not really fit for human consumption. Their ONLY
>> purpose is to provide a meaningless unique key. They will always have
>> gaps; if
>> you delete a record it will leave a gap; if you even hit <ESC> while
>> entering
>> a new record it will leave a gap; if you use an Append query to populate
>> the
>> table it can leave a huge gap; the numbers can even become random (if you
>> Replicate). If you want sequential numbers, use a Long Integer field and
>> manage them yourself, manually or with code - search the groups for
>> "Custom
>> counter" for examples.
>> --
>>
>> John W. Vinson [MVP]
>>
 >> Stay informed about: Auto Number with Letter Prefix 

On Sat, 29 Aug 2009 18:12:01 -0700, Ray wrote:

>Sorry, my brain leaked out of my ears like a runny nose. I should say I've
>worked on Excel for one week to be exact and am now "in charge" of 1800 lines
>of data.

As Larry says, you're asking in the wrong place. This group is for Microsoft
Access, the database application in Office; Excel is a quite different
program. Are you using Excel, or Access? If Excel, please scroll down the list
of subject areas and find a forum for Excel. The volunteers there will be glad
to help.

>I have a master sheet of every person who has appeared in a show for five
>seasons. Each season has 12 shows. So my serial number wouldn't have the
>same TAV 101 prefix. It will go all the way to TAV 509 (for Season 5 show 9)

The Access answer (if that's what you're using) would be to have THREE fields,
not one: Season; Show; SerialNumber. It's not necessary (or a good idea) to
store the text string TAV since it's (apparently) identical in every record in
the table; just use a format to display it.

You can easily concatenate the three fields for display purposes.

>Do you still recommend using a long integer field? There is going to be a
>point where I will have to just type the numbers in manually. Reason is I'm
>only temperorary and eventually some other clueless monkey will be running
>the database. I was looking for a formula to type in and be done with it but
>please let me know if this won't work. Thanks for your advice...

Either with Access or Excel, you'll want to use a program (VBA code, a Module
in Access, or a Macro in Excel - same code, different name) to increment the
number. It's not necessary to enter them manually, and it's not a good idea to
use an Access Autonumber (and, as best as I know, there is no such thing as an
autonumber in Excel).
 >> Stay informed about: Auto Number with Letter Prefix 

Guys, the OP mentioned autonumber, which is not available in Excel (I don't
think so anyway). He may have confused Excel with Access and might be in the
right place afterall.

But it looks like John did answer the question anyway.

"Larry Linson" wrote:

> Ray, this newsgroup is not for EXCEL. It is for technical discussion of and
> questions and answers about Microsoft ACCESS database software. In the
> online interface you used to post here, you need to scroll farther to find
> an Excel newsgroup.
>
> John gave you an "Access Answer" but seems you may have been asking an
> "Excel Question". They are not the same, and don't work the same.
>
> Please note that John didn't suggest that his solution would work only if
> the whole serial number were "prefix"... he was talking about the same
> _character_ prefix "TAV" which does seem consistent. How did you plan to
> generate the "101" to "509" part of the number? But, for Access, his idea
> would work for multiple characters that are alphanumeric.
>
> You should also be aware, in Access, that an Autonumber is strictly intended
> for use internal to the database, to guarantee uniqueness, and to join
> records in related tables -- it is not guaranteed to be "consecutive",
> "monotonically increasing" as you may have assumed, and it is, by
> definition, numeric... it is a Long Integer with an autoincrement feature.
> But, there are a number of situations in which it may end up being
> non-consecutive.
>
> Larry Linson
> Microsoft Office Access MVP
>
> "Ray" wrote in message
>
> > Sorry, my brain leaked out of my ears like a runny nose. I should say
> > I've
> > worked on Excel for one week to be exact and am now "in charge" of 1800
> > lines
> > of data.
> >
> > I have a master sheet of every person who has appeared in a show for five
> > seasons. Each season has 12 shows. So my serial number wouldn't have the
> > same TAV 101 prefix. It will go all the way to TAV 509 (for Season 5 show
> > 9)
> >
> > Do you still recommend using a long integer field? There is going to be a
> > point where I will have to just type the numbers in manually. Reason is
> > I'm
> > only temperorary and eventually some other clueless monkey will be running
> > the database. I was looking for a formula to type in and be done with it
> > but
> > please let me know if this won't work. Thanks for your advice...
> >
> > "John W. Vinson" wrote:
> >
> >> On Sat, 29 Aug 2009 17:03:01 -0700, Ray wrote:
> >>
> >> >Unique Serial Numbers:
> >> >Here is the issue: I want to give everyone on a list a unique "serial
> >> >number" Except that serial number should begin with a prefix, in this
> >> >case
> >> >"TAV 101" So the end product in this column should be a unique number
> >> >that
> >> >appears like this "TAV 101-1" "TAV 101-2" "TAV 101-3" I've looked on the
> >> >forums and in help and I don't think I'm wording my question right. Can
> >> >anyone help?
> >>
> >> If every record has the same prefix, then you need not - and should not -
> >> store it in the table at all. You can use a Format property (in a table
> >> field,
> >> or as the Format of a form or report textbox) such as
> >>
> >> "TAV 1\01-#"
> >>
> >> to display the literal characters followed by a number.
> >>
> >> However, Autonumbers are not really fit for human consumption. Their ONLY
> >> purpose is to provide a meaningless unique key. They will always have
> >> gaps; if
> >> you delete a record it will leave a gap; if you even hit <ESC> while
> >> entering
> >> a new record it will leave a gap; if you use an Append query to populate
> >> the
> >> table it can leave a huge gap; the numbers can even become random (if you
> >> Replicate). If you want sequential numbers, use a Long Integer field and
> >> manage them yourself, manually or with code - search the groups for
> >> "Custom
> >> counter" for examples.
> >> --
> >>
> >> John W. Vinson [MVP]
> >>
>
>
>
>
 >> Stay informed about: Auto Number with Letter Prefix