Two autonumber fields in one table possible?

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
0
Utf
6/1/2010 3:47:01 PM
access 16762 articles. 2 followers. Follow

14 Replies
1022 Views

Similar Articles

[PageSpeed] 18

"Sharon_wv" <Sharonwv@discussions.microsoft.com> wrote in message 
news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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?

No. Only one autonumber field per table, although, you can create your owb 
autoincrement field.

Also, have a look at:

http://www.datastrat.com/Download/AlphaNumeric2K.zip

for some ideas.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley


> 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 


0
Arvin
6/1/2010 4:35:24 PM
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@discussions.microsoft.com> wrote in message 
news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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 


0
Jeff
6/1/2010 6:36:12 PM
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@discussions.microsoft.com> wrote in message 
> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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 
> 
> 
> .
> 
0
Utf
6/1/2010 7:21:01 PM
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@discussions.microsoft.com> wrote in message 
news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in message
>> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
>>
>>
>> .
>> 


0
Jeff
6/1/2010 7:48:41 PM
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@discussions.microsoft.com> wrote in message 
> news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in message
> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
6/2/2010 11:51:02 AM
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@discussions.microsoft.com> wrote in message 
news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713@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@discussions.microsoft.com> wrote in message
>> news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in message
>> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
Jeff
6/2/2010 4:02:38 PM
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@discussions.microsoft.com> wrote in message 
> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713@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@discussions.microsoft.com> wrote in message
> >> news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in message
> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
6/3/2010 12:31:40 PM
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@discussions.microsoft.com> wrote in message 
news:092C6A28-843F-421D-856D-D2F7E7A87883@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@discussions.microsoft.com> wrote in message
>> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713@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@discussions.microsoft.com> wrote in message
>> >> news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in message
>> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
Jeff
6/3/2010 4:12:19 PM
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

0
KenSheridan
6/3/2010 5:49:17 PM
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" <Sharonwv@discussions.microsoft.com> wrote in message 
> news:092C6A28-843F-421D-856D-D2F7E7A87883@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@discussions.microsoft.com> wrote in message
> >> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713@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@discussions.microsoft.com> wrote in message
> >> >> news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in message
> >> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
> >> >> >>
> >> >> >>
> >> >> >> .
> >> >> >>
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
6/3/2010 7:05:30 PM
Sharon

It sounds like you/researchers are trying to use the sequence number to 
"count" the number of records.  If so, stop now!

If you use an Access Autonumber, there's no guarantee that the numbers will 
be sequential -- that's not what they're designed to do.

If you use a "roll-your-own-'custom'-autonumber" procedure, there is STILL 
no guarantee that the largest sequence number (e.g., 1895) represents the 
"count" of records (because you could have 100 missing sequence numbers in 
the middle of the run!).

If what you REALLY need is to count the number of records, count them!  Use 
a Totals query, use the Count aggregation, and GroupBy Category.  Access 
will come up with the number of "A"s and the number of "B"s without any 
reference to (or need to use) the "id" numbers.

.... and if the ONLY reason why the design involved so-called sequence 
numbers, you could do away with them altogether!  Just make sure you have a 
unique row identifier for each record (here's where an Access Autonumber 
data type field would work just fine).  You'd still just use the Totals 
query.

Good luck!

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@discussions.microsoft.com> wrote in message 
news:05CF8BE0-E3A8-46BA-B743-1A3595C584BA@microsoft.com...
> 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" <Sharonwv@discussions.microsoft.com> wrote in message
>> news:092C6A28-843F-421D-856D-D2F7E7A87883@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@discussions.microsoft.com> wrote in message
>> >> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713@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@discussions.microsoft.com> wrote in message
>> >> >> news:E3AFD309-C3D6-45CF-9E02-920345268681@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@discussions.microsoft.com> wrote in 
>> >> >> >> message
>> >> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171@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
>> >> >> >>
>> >> >> >>
>> >> >> >> .
>> >> >> >>
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
Jeff
6/3/2010 7:16:04 PM
Ken & Jeff -
I totally understand your logic for wanting me to stop.  However, let me 
give you more details about the records that we will be capturing.  With each 
record, there will be a corresponding file (many binders, etc.) that go along 
with the record.  These binders, etc. will be filed in a large library 
according to their tracking numbers.  Each track of hard copy records, if my 
plan for numbering works, will be filed in different areas.  In order to make 
finding records easier, I was hoping to assign sequential tracking numbers 
for each track of records (Category A and Category B).  So it wasn't just for 
counting purposes, but for overall organizational purposes of both physical 
records and digital records.  BUT, I suppose that assigning the A-xxxx vs. 
B-xxxx will do just that as well, without having to violate the integrity of 
a database.  I can also just assign the numbers to each record after entering 
all the data as well, instead of having Access do it for me.  Once I have 
them all logged in Access, it will be simple to do something like that.

I really really really appreciate your expertise in leading me in the right 
direction, gentlemen!  So sorry to take up so much of your valuable time!!

Sharon 

"KenSheridan via AccessMonster.com" wrote:

> 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
> 
> .
> 
0
Utf
6/3/2010 11:22:09 PM
On Thu, 3 Jun 2010 16:22:09 -0700, Sharon_wv
<Sharonwv@discussions.microsoft.com> wrote:

>Ken & Jeff -
>I totally understand your logic for wanting me to stop.  However, let me 
>give you more details about the records that we will be capturing.  With each 
>record, there will be a corresponding file (many binders, etc.) that go along 
>with the record.  These binders, etc. will be filed in a large library 
>according to their tracking numbers.  Each track of hard copy records, if my 
>plan for numbering works, will be filed in different areas.  In order to make 
>finding records easier, I was hoping to assign sequential tracking numbers 
>for each track of records (Category A and Category B).  So it wasn't just for 
>counting purposes, but for overall organizational purposes of both physical 
>records and digital records.  BUT, I suppose that assigning the A-xxxx vs. 
>B-xxxx will do just that as well, without having to violate the integrity of 
>a database.  I can also just assign the numbers to each record after entering 
>all the data as well, instead of having Access do it for me.  Once I have 
>them all logged in Access, it will be simple to do something like that.
>
>I really really really appreciate your expertise in leading me in the right 
>direction, gentlemen!  So sorry to take up so much of your valuable time!!

Just to reiterate Ken and Jeff's point:

You do NOT need one field containing the category and the sequential number.

You need *two different fields* - one of them for the category, the other for
the number. Combining them into one field is neither necessary nor beneficial.
You can very easily *display* them together; just use a calculated field in a
Query or in the Control Source of a form or report textbox set to 

[Category] & Format([Seqno], "0000")

to display A0001, B3128 or the like.

To assign category-specific sequential numbers, you'll need to use VBA code in
the data entry form (and yes, you will need to use a Form). You could use a
combo box to select the category (this will allow more categories if that
should happen in the future); in the combo box's AfterUpdate event use code
like 

Private Sub cboCategory_AfterUpdate()
'Only assign a new number if it's the new record; one has not yet 
'been assigned already; and the user did select a category
If Me.NewRecord AND IsNull(me![Seqno]) And Not IsNull(Me!cboCategory) Then
   Me![Seqno] = NZ(DMax("[Seqno]", "[yourtablename]", _
        "[Category] = '" & Me!cboCategory & "'")
End If
End Sub

-- 

             John W. Vinson [MVP]
0
John
6/4/2010 12:19:13 AM
Sharon:

One thing worth adding to John's reply is that if the database is in a multi-
user environment, and two or more users could be adding a new record of the
same category simultaneously, this would give rise to a conflict as both
would get the same new number.  If the category and serial number columns are
the composite primary key of the table, or otherwise indexed uniquely
(together not individually), which they should be, an error would occur when
the second user trying to save the record does so as this would violate the
index.

Roger Carlson has a simple solution to this at:

http://www.rogersaccesslibrary.com/forum/topic395.html

and there's a more complex one of mine, which also allows the next number to
be used to be 'seeded' at any time, at:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Both of these are for a single sequence of numbers, however, so for two
sequences some modification would be needed.  If you need help to implement
either of these in the context of your database post back.

Ken Sheridan
Stafford, England

Sharon_wv wrote:
>Ken & Jeff -
>I totally understand your logic for wanting me to stop.  However, let me 
>give you more details about the records that we will be capturing.  With each 
>record, there will be a corresponding file (many binders, etc.) that go along 
>with the record.  These binders, etc. will be filed in a large library 
>according to their tracking numbers.  Each track of hard copy records, if my 
>plan for numbering works, will be filed in different areas.  In order to make 
>finding records easier, I was hoping to assign sequential tracking numbers 
>for each track of records (Category A and Category B).  So it wasn't just for 
>counting purposes, but for overall organizational purposes of both physical 
>records and digital records.  BUT, I suppose that assigning the A-xxxx vs. 
>B-xxxx will do just that as well, without having to violate the integrity of 
>a database.  I can also just assign the numbers to each record after entering 
>all the data as well, instead of having Access do it for me.  Once I have 
>them all logged in Access, it will be simple to do something like that.
>
>I really really really appreciate your expertise in leading me in the right 
>direction, gentlemen!  So sorry to take up so much of your valuable time!!
>
>Sharon 
>
>> 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
>[quoted text clipped - 57 lines]
>> >> 
>> >> .

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

0
KenSheridan
6/4/2010 3:24:09 PM
Reply:

Similar Artilces:

How to split a table heading?
I would like to split a top left cell of a table diagonally so that the top triangle of the cell is the heading for the columns and the bottom triangle is the heading for the rows. Any Ideas? --- Message posted from http://www.ExcelForum.com/ Use Alt+Enter to split into two lines within the cell you will have to add your own spaces. -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "ykontou" <ykontou.yu4ha@excelforum-...

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

calculate field
hi i have 3 tables (jb;jb_ver;jb_vd) jb_vd= jb_id&"."&jb_ver all are primary keys when i'm using a form i want jb_vd to be calculated automaticlly . even when changing any of the used fields How is this a .forms newsgroup question? How can a "table" be a "primary key"? If you want a third table to have a primary key that consists of two fields (e.g., foreign keys from two other tables), you can have multiple fields as a primary key. Don't bother inserting that "." or "calculating" a primary key. Just set the two fields as a...

Pivot table in Excel 2007
Hello experts This pivot table has been created automatically in Excel 2007 : see the following link http://dl-1.free.fr/52616e646f6d495687b5f6522854b42d0ab1c5c335e8e7d2b3d67f4e6bf6721d/Pivottable2007template.xlsx In this pivot table, how, you experts, will you replace the row "Grand total" by a row "Difference" with a formula like Receipts - Expenses. Thanks for your help! Hi One way would be to have another column in the source data, called Expense2 with a formula =Expenses*-1 (replacing Expenses with the relevant cell reference) and copying down. Replace Expens...

Creating Multiple Records from Single Delimited Field
I am working with some data that lists Supervisor Hierarchy in a Pipe Delimited format and I want to be able to have it show in a query based on each level of the supervisor. Example: Name SupervisorHierarchy John Smith |White|Lee|Martinez|Scott| Jim Jones |Clark|Wright|Jackson|Johnson|Hill|Lewis| Ideally I would like to see Name Supervisor Level John Smith White 1 John Smith Lee 2 John Smith Martinez 3 John Smith Scott 4 Jim Jones C...

Forcing field to recalculate
Hi I have a calculated field on a form which calculates based on values form two sub forms. How can I force the field to recalculate when data in any of the sub forms is changed? I have tried myfield.recalc and myfield.refresh but neither is supported vba. Thanks Regards try: myfield.requery -- Maurice Ausum "John" wrote: > Hi > > I have a calculated field on a form which calculates based on values form > two sub forms. How can I force the field to recalculate when data in any of > the sub forms is changed? I have tried myfield.recalc and myfield.refre...

Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact mat...

Configuration Table not fully update when exporting new store data
After I create a new store database in HQ Manager, export the store database from HQ Admin, and then import the database into the Store Database on the POS I have noticed that only the StoreID is updated correctly. The Store Name, Address, Zip, State, and phone numbers are still set to my master database that I use as a template. This is getting annoying as I am setting up 50 stores. I tried to create a SQL Script to run on the POS in SSMS, which works EXCEPT for the zip code will not update and I do not understand why. The only thing I noticed is ZIP is varchar(15) not null in the S...

Excel 2007 : cannot shift cells in a filtered range or table
I have a user who has a filtered list. When she selected a particular value to filter she is unable to delete a row. She can do it fine when she deselects the value in the filter and selects anything else or completly removes the filter. The error she gets is cannot shift cells in a filtered range or table ...

Finding Numerics in a field
What would be the best formula to use to find out if a field has numbers in it. example: JOHN SMITH2 JOHN 9 SMITH JOHN 201 SMITH Thanks On Thu, 22 Apr 2010 09:26:23 -0700 (PDT), Sam wrote: > What would be the best formula to use to find out if a field has > numbers in it. > > example: > > JOHN SMITH2 > JOHN 9 SMITH > JOHN 201 SMITH > > Thanks As the criteria on that field in a query: Like "*[0-9]*" -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail On Thu, 22 Apr 2010 09:26:23 -070...

Great Plains Suggestion: Need to increase the lenght of inventory item serial number field
Currently the inventory item serial number field is 20 characters lenght. Some computer products currently have up to 35 digits at serial number. It could be nice to increase the field to 40 or 50 characters. Regards, Takashi Arikuma ...

Pivot table for two different data variables
I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for ...

Automating field population on a form
I have a form with a subform inserted, and would like to add a button that the user would push, and it would automatically fill in some of the fields in the subform from the main form. Main form has company name, address, city, state, zip. The subform has people associated with that company, and I want the user to be able to click on a button if the person's direct mailing address is the same as the main company address, city, state, etc. I am pretty sure that I need to create a macro, but I haven't been able to find a good step by step guide on how to do it. I wouldn't c...

COUNTIF (?) on two criteria
I have a spreadsheet that logs work activities and records team members' completion of project units. Column B records team member names. Column C has project unit status - 'pending', 'beta' or 'completed'. It's easy to set up a formula to count how many units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is set up formulas to count how many units Joe has completed, or has pending or at beta, and this means assessing data in both columns - which COUNTIF won't do. This doesn't strike me as an especially...

Mora than one Domain Exchange 2003
Hello! i have instaled an exchange 2003 and i need to use two domain, so the first domain works fine but now i need to use one of the user with another domain. Before this exchange 2003 i was running exchange 5.5 with both domains without any problems. Pls can anybody help me? Many thanks!!! Hi! Yes, the user that use the second domain needs that domain (the second) to be their primary (in fact the only) email adress. Thanks!! Martin. "PhilE" <anonymous@discussions.microsoft.com> escribi� en el mensaje news:F5FF4465-9DF5-4094-B679-776DEBCC8391@microsoft.com... > Does...

Two IF Statements with two answers
In cell J9 I need a formula that would do the following: If G9="Yes" then F9, If G9="No" then -f9 Note: G9 has a drop down list with Yes, No answers. If there are no other alternatives that yes and no =IF(G9="Yes",F9,-F9) otherwise =IF(G9="Yes",F9,IF(G9="No",-F9,"") Regards, Peo Sjoblom "heater" wrote: > In cell J9 I need a formula that would do the following: > > If G9="Yes" then F9, If G9="No" then -f9 > > Note: G9 has a drop down list with Yes, No answers. > =IF(G9=&qu...

Two axis
Positioning the Vertical axes: I have a chart where there are two axes. The first implementation produced the two scales on the right side, one inside, the other outside the chart area. Unusual, but luckily it fits well the chart. I tried to duplicate the feat with another dataset (similar in structure) and did not even get the second scale to show right, left or center (data plotted with two different scales allright). Where is the info to control these functionalities? Tx, Pierre On Wed, 29 Oct 2003 17:48:13 +0100, pl.carry <pl.carry@wanadoo.fr> wrote= : > Positioning the Vertic...

Select cheques
When you are building a batch of cheques for one creditor and you add to the batch several payments the payments print out on seperate remittances even though you have specified to print one cheque per creditor. You have to go to edit payments to put all the payments on the one remittance advice. Can this additional funtionality be added to this screen. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

All Replies from One Account Regardless of Which Received
I have two accounts set up in Outlook 2007. I want to receive mail via one but when I hit reply have OL automatically use the other. I have already set the other as the default account in TOOLS-- ACCOUNT SETTINGS. Is this possible and if so how? No it is not possible automatically. Outlook used the receiving account = to send by default. You would need to change it manually for each item = you send (use the Accounts button). --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, BigK asked: | I have two acco...

plot two groups of numbers on one series of xy coordinates.
how to create a plot from separate x and y values. i.e. y values do not correspond to the same set of x values. example x1= 5, y1=10; x2= 8, y2=15 I can read your question 2 ways; A) you data looks like this x y 5 10 8 15 etc. Select the data and make an XY chart B) you have two sets of data x1 y1 5 10 10 12 17 15 21 20 and x2 y2 3 8 7 14 12 19 18 24 Make an XY chart of the first data series Select all the data (including headers) for second set, and use Copy Click the chart to activate it Use Edit | Paste Special .... indicate New Ser...

Copying Formulas from One Worksheet to Another -- Very Easy Questi
I have a worksheet which has a bunch of formulas in rows (i.e. data entered in columns C through F result in formulas results in columns G through whatever). All I want to do is to copy a row and move the formulas into another worksheet while keeping the references to the original worksheet intact. So: =COUNTIF(G$3:G301,G301) becomes... =COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down. I'm copying from row 300 in the original to Row 2, to make things a little more complicated, but isn't there a way to do this? Thanks in advance! "Rothman"...

Creating Calculations Within a Field
Hello, I have an Activity field with various activities and I would like to calculate the time values between these activities. For example: Activity (Field) Time (Avg) Stand 12:00 Run 13:00 Stop 13:30 I would like to add a calculation within Activity that is Stop - Stand. I greatly appreciate any help. Thanks! Minh Hi assume your example is in cells A1:B4 the formula you're looking for is B4-B2 Cheers JulieD "MinhPhan" <MinhPhan@discussions.microsoft.com> wrote in message news:22E8B9D6-0C72-400F-ACEF-D5...

two variables
I have a query that asks for collcode, collcode2 and booth. On any given day an employee may be collcode or collcode2. I would like to know how many times a certain emp has been paired with another employee. So how do I ask if an employee has been collcode/collcode2 and who the other person was? I hope this question makes sense. Thanks for any help Query one: SELECT CollCode, CollCode2, Booth FROM YourTable UNION ALL SELECT CollCode2, CollCode, Booth FROM YourTable Query Two SELECT CollCode, CollCode2, Count(CollCode) AS TIMESPAIRED FROM QueryOne GROUP BY CollCode, CollCode2 IF y...

Find two PCI instance but load Init() one time
Hi all, I'm developing a stream interface driver for my PCI device under WinCE 6.0 and this driver works very well currently. Now, I got a problem when testing two the same PCI devices with my stream driver. The registry can find two PCI instances (i.e. PCI\Instance\mydrv1, PCI\ Instance\mydrv2) but system only call my XXX_init() one time. Does anyone know what the problem is? The following information is my registry setting: ================================================================== [HKEY_LOCAL_MACHINE\Drivers\BuiltIn\PCI\Template\mydrv] "Dll"="myd...