Two autonumber fields in one table possible?

Jeff-
You are so helpful... thank you so much. Okay, here's the rationale for why
I need the numbering separate. We are going to be managing two separate
tracks of a project (with different people leading/managing each track).
However, all of the data will be coming into the group via a central
repository. Because "Sue" will be managing Category A and "Joe" will be
managing Category B, the requirement from each of them was to have their
areas separate and distinct from each other. If I assign tracking #s to each
of pieces of data consecutively (0001 to 2000), I'm/they are afraid that
something may slip through the cracks. Therefore, it is preferred that the
numbering start from 1 and increase by one for each category.

Therefore, the tracking numbers for Category A would be numbered from A0001,
A0002.... A188, A189...etc. And Category B would be numbered from B0001,
B0002.... B345, B346, etc. This way each manager will be know they are
responsible. We will also know exactly how many files each manager is
responsible. Having those numbers sequential by track will also make
reporting a lot more simple as well.

Does that make better sense to you? Can you think of another way to
differentiate between the tracks other than doing it manually before we start
assigning numbers or by having two separate copies of the database?


"Jeff Boyce" wrote:

As long as your approach requires you to "assign the next sequential number
.... (e.g. B1825)", you are still confusing data with display.

If you have a category field ("A" or "B" ...) and you have a sequence number
field (1, 2, ..., 1825, ...), you do NOT need to store the two fields in a
third field. Use a query to concatenate, say, "B" and "1825" for display
purposes.

Now, if you are saying that you want to have sequence numbers for BOTH
category A and category B, and that the sequence numbers are independent
(i.e., you could have "A" & "123" AND "B" & "123"), that's only slightly
more work.

If that's the case, check online for "custom autonumber" for several
different approaches/procedures. The generic notion is that your procedure
checks for the maximum sequence number used for whatever category is
indicated, then adds one.

By the way, why?! What is it that you want to be able to use the, say,
"A123" to do? If you'll describe a bit more specifically what you hope to
accomplish (not "how"), folks here may be able to offer alternate solutions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sharon_wv" wrote in message
...
Jeff -

I have a field to collect the category. The choices are in a separate
table
(Category Choice) w/ just two records (Category A and Category B). The
person doing data entry will select one. That's the easy part for me.

The difficulty comes in the next step -- if the user selects Category A in
that record, I want it to the assign the next sequential number for that
Category (e.g. A0121 or A0122). If the user selects Category B, I want it
to
assign the next sequential number that that category (e.g. B1825 or B1921,
etc.). I will probably use the Category Choice table to capture the A and
B
by adding another column to that table. Then I will use that field in a
query to concantenate to give me the Identifier.

The problem I'm having is figuring out how to have Access give me a
sequential numbers for both Category A and Category B (dependent on user
selection on form).

Does that make better sense of my scenario and what I am attempting to do?
I find it difficult to capture all my thoughts and needs for you in a
message. I appreciate your patience with me.

Sharon

"Jeff Boyce" wrote:

Sharon

If you or your data entry person(s) have to enter data and categorize it,
you probably need a field in which to put the category.

First add the field to the table. Then add the field to the form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sharon_wv" wrote in message
...
Jeff -

I'm sorry I wasn't clear. The records to be entered would come to the
group
in a random order and fall into either Category A or Category B. I am
trying to prevent us having to receive all records adn then have to
manually
separate the records by Category prior to beginning the data entry
process
into Access. I would like to have the ability to enter the records as
they
are received regardless of what category they fall into.

Thanks again for the help. I am truly appreciative of your assistance!

Sharon


"Jeff Boyce" wrote:

Sharon

In your original post, it seemed like you were saying that sequence
numbers
1-200 all belonged in category A. Then all the remaining sequence
numbers
(201-1800) belonged in category B.

If that's true, the sequence numbers would be unique ... and that's
what
you
need for relating to other tables!

What am I missing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sharon_wv" wrote in message
...
Thank you both for your quick responses. My new hurdle, based on
your
responses, has to do with the relationships I've set up with other
tables.
I've used the ID field as my field to relate the tables through.
But
that
will not work based now, will it? Both categories A & B (in the
main
table)
will have the same one-to-many relationship with the other tables
and
the
ID
field was the unique field I used to relate them. Any suggestions
of
what
I
can use instead? Can I still have the auto-number for that purpose
(Unique
Identifier) & then do the sequencing in other fields maybe in the
main
table?
I am very rusty since I haven't played with Access in a couple
years.
Plus
I now have 2007 so I'm getting used to the difference in looks...

Thanks again for your quick responses. I truly appreciate the help!

"Jeff Boyce" wrote:

As Arvin points out, the Access Autonumber is not what you're
looking
for.
Based on your description, you have two fields, not one.

One field is the "category" (A or B). The other field is a
sequence
number
(1-1800). Don't store those two "facts" in a single field --
instead,
use a
query to concantenate them as needed.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is",
with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Sharon_wv" wrote in message
...
I am designing a database to capture information that will be
classified
into
two separate categories. I would like to autonumber each record
by
category.
Is that possible? For example, I will likely have 2000 total
records
with
200 of those records in Category A and 1800 of the records in
Category
B.
The information will come to the data entry group in a random
format.
I
would like to prevent separating the information into the two
categories
manually or need to enter them in two separate forms dependent on
the
category.


Can I have MS Access autonumber them like this?:

A001 to A200 (for Category A) and then B001 to B1800 (for
Category
B)
on
the
same data entry form?

If so, how would I go about doing that? Any assistance will
certainly
be
appreciated!!

Sharon


.



.



.



.