From: Sharon_wv on
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" <Sharonwv(a)discussions.microsoft.com> wrote in message
> news:E3AFD309-C3D6-45CF-9E02-920345268681(a)microsoft.com...
> > 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171(a)microsoft.com...
> >> >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
> >>
> >>
> >> .
> >>
>
>
> .
>
From: Jeff Boyce on
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" <Sharonwv(a)discussions.microsoft.com> wrote in message
news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713(a)microsoft.com...
> 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
>> news:E3AFD309-C3D6-45CF-9E02-920345268681(a)microsoft.com...
>> > 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
>> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171(a)microsoft.com...
>> >> >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
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


From: Sharon_wv on
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" <Sharonwv(a)discussions.microsoft.com> wrote in message
> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713(a)microsoft.com...
> > 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
> >> news:E3AFD309-C3D6-45CF-9E02-920345268681(a)microsoft.com...
> >> > 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171(a)microsoft.com...
> >> >> >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
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>
From: Jeff Boyce on
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" <Sharonwv(a)discussions.microsoft.com> wrote in message
news:092C6A28-843F-421D-856D-D2F7E7A87883(a)microsoft.com...
> 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
>> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713(a)microsoft.com...
>> > 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
>> >> news:E3AFD309-C3D6-45CF-9E02-920345268681(a)microsoft.com...
>> >> > 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" <Sharonwv(a)discussions.microsoft.com> wrote in message
>> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171(a)microsoft.com...
>> >> >> >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
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


From: KenSheridan via AccessMonster.com on
As Jeff says the key question here is why? As the numbers are, like a true
autonumber, arbitrary, it's difficult to see what purpose they fulfil. The
column cannot be the primary key obviously as there will be duplication
between categories. The primary key would have to be a composite one of the
category and serial number columns, it being a bad idea, as Jeff has
explained, to encode the category in the 'number' as this introduces
redundancy and the possibility of inconsistent data.

If there is a real reason why you need to serially number each row per
category in the order in which they are inserted into the table then I'd
suggest an alternative approach, which is to include a column DateTimeStamp
with a DefaultValue property of Now(). This will automatically contain the
date and time when each row is inserted. You can then number the subsets of
each category in date/time order in a query, e.g.

SELECT COUNT(*) AS SerialNumber, T1.Category, T1.SomeField
FROM YourTable AS T1 INNER JOIN YourTable AS T2
ON T2.DateTimeStamp <= T1.DateTimeStamp
AND T2.Category = T1.Category
GROUP BY T1.Category, T1.SomeField;

You can of course include other columns from the table, but they must be
included in the GROUP BY clause as well as the SELECT clause.

Note that the numbering is dynamic; if a row is deleted from the table the
SerialNumber values computed by the query will reflect the number of existing
rows following the deletion rather than leaving a gap as would be the case if
you assign values to a column in the table.

Ken Sheridan
Stafford, England

Sharon_wv wrote:
>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
>
>> Sharon
>>
>[quoted text clipped - 121 lines]
>>
>> .

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201006/1