If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Two autonumber fields in one table possible?
"Sharon_wv" wrote in message ... I am designing a database to capture information that will be classified into two separate categories. I would like to autonumber each record by category. Is that possible? 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 |
#3
|
|||
|
|||
Two autonumber fields in one table possible?
As Arvin points out, the Access Autonumber is not what you're looking for.
Based on your description, you have two fields, not one. One field is the "category" (A or B). The other field is a sequence number (1-1800). Don't store those two "facts" in a single field -- instead, use a query to concantenate them as needed. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... I am designing a database to capture information that will be classified into two separate categories. I would like to autonumber each record by category. Is that possible? For example, I will likely have 2000 total records with 200 of those records in Category A and 1800 of the records in Category B. The information will come to the data entry group in a random format. I would like to prevent separating the information into the two categories manually or need to enter them in two separate forms dependent on the category. Can I have MS Access autonumber them like this?: A001 to A200 (for Category A) and then B001 to B1800 (for Category B) on the same data entry form? If so, how would I go about doing that? Any assistance will certainly be appreciated!! Sharon |
#4
|
|||
|
|||
Two autonumber fields in one table possible?
Thank you both for your quick responses. My new hurdle, based on your
responses, has to do with the relationships I've set up with other tables. I've used the ID field as my field to relate the tables through. But that will not work based now, will it? Both categories A & B (in the main table) will have the same one-to-many relationship with the other tables and the ID field was the unique field I used to relate them. Any suggestions of what I can use instead? Can I still have the auto-number for that purpose (Unique Identifier) & then do the sequencing in other fields maybe in the main table? I am very rusty since I haven't played with Access in a couple years. Plus I now have 2007 so I'm getting used to the difference in looks... Thanks again for your quick responses. I truly appreciate the help! "Jeff Boyce" wrote: As Arvin points out, the Access Autonumber is not what you're looking for. Based on your description, you have two fields, not one. One field is the "category" (A or B). The other field is a sequence number (1-1800). Don't store those two "facts" in a single field -- instead, use a query to concantenate them as needed. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... I am designing a database to capture information that will be classified into two separate categories. I would like to autonumber each record by category. Is that possible? For example, I will likely have 2000 total records with 200 of those records in Category A and 1800 of the records in Category B. The information will come to the data entry group in a random format. I would like to prevent separating the information into the two categories manually or need to enter them in two separate forms dependent on the category. Can I have MS Access autonumber them like this?: A001 to A200 (for Category A) and then B001 to B1800 (for Category B) on the same data entry form? If so, how would I go about doing that? Any assistance will certainly be appreciated!! Sharon . |
#5
|
|||
|
|||
Two autonumber fields in one table possible?
Sharon
In your original post, it seemed like you were saying that sequence numbers 1-200 all belonged in category A. Then all the remaining sequence numbers (201-1800) belonged in category B. If that's true, the sequence numbers would be unique ... and that's what you need for relating to other tables! What am I missing? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... Thank you both for your quick responses. My new hurdle, based on your responses, has to do with the relationships I've set up with other tables. I've used the ID field as my field to relate the tables through. But that will not work based now, will it? Both categories A & B (in the main table) will have the same one-to-many relationship with the other tables and the ID field was the unique field I used to relate them. Any suggestions of what I can use instead? Can I still have the auto-number for that purpose (Unique Identifier) & then do the sequencing in other fields maybe in the main table? I am very rusty since I haven't played with Access in a couple years. Plus I now have 2007 so I'm getting used to the difference in looks... Thanks again for your quick responses. I truly appreciate the help! "Jeff Boyce" wrote: As Arvin points out, the Access Autonumber is not what you're looking for. Based on your description, you have two fields, not one. One field is the "category" (A or B). The other field is a sequence number (1-1800). Don't store those two "facts" in a single field -- instead, use a query to concantenate them as needed. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... I am designing a database to capture information that will be classified into two separate categories. I would like to autonumber each record by category. Is that possible? For example, I will likely have 2000 total records with 200 of those records in Category A and 1800 of the records in Category B. The information will come to the data entry group in a random format. I would like to prevent separating the information into the two categories manually or need to enter them in two separate forms dependent on the category. Can I have MS Access autonumber them like this?: A001 to A200 (for Category A) and then B001 to B1800 (for Category B) on the same data entry form? If so, how would I go about doing that? Any assistance will certainly be appreciated!! Sharon . |
#6
|
|||
|
|||
Two autonumber fields in one table possible?
Jeff -
I'm sorry I wasn't clear. The records to be entered would come to the group in a random order and fall into either Category A or Category B. I am trying to prevent us having to receive all records adn then have to manually separate the records by Category prior to beginning the data entry process into Access. I would like to have the ability to enter the records as they are received regardless of what category they fall into. Thanks again for the help. I am truly appreciative of your assistance! Sharon "Jeff Boyce" wrote: Sharon In your original post, it seemed like you were saying that sequence numbers 1-200 all belonged in category A. Then all the remaining sequence numbers (201-1800) belonged in category B. If that's true, the sequence numbers would be unique ... and that's what you need for relating to other tables! What am I missing? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... Thank you both for your quick responses. My new hurdle, based on your responses, has to do with the relationships I've set up with other tables. I've used the ID field as my field to relate the tables through. But that will not work based now, will it? Both categories A & B (in the main table) will have the same one-to-many relationship with the other tables and the ID field was the unique field I used to relate them. Any suggestions of what I can use instead? Can I still have the auto-number for that purpose (Unique Identifier) & then do the sequencing in other fields maybe in the main table? I am very rusty since I haven't played with Access in a couple years. Plus I now have 2007 so I'm getting used to the difference in looks... Thanks again for your quick responses. I truly appreciate the help! "Jeff Boyce" wrote: As Arvin points out, the Access Autonumber is not what you're looking for. Based on your description, you have two fields, not one. One field is the "category" (A or B). The other field is a sequence number (1-1800). Don't store those two "facts" in a single field -- instead, use a query to concantenate them as needed. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... I am designing a database to capture information that will be classified into two separate categories. I would like to autonumber each record by category. Is that possible? For example, I will likely have 2000 total records with 200 of those records in Category A and 1800 of the records in Category B. The information will come to the data entry group in a random format. I would like to prevent separating the information into the two categories manually or need to enter them in two separate forms dependent on the category. Can I have MS Access autonumber them like this?: A001 to A200 (for Category A) and then B001 to B1800 (for Category B) on the same data entry form? If so, how would I go about doing that? Any assistance will certainly be appreciated!! Sharon . . |
#7
|
|||
|
|||
Two autonumber fields in one table possible?
Sharon
If you or your data entry person(s) have to enter data and categorize it, you probably need a field in which to put the category. First add the field to the table. Then add the field to the form. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... Jeff - I'm sorry I wasn't clear. The records to be entered would come to the group in a random order and fall into either Category A or Category B. I am trying to prevent us having to receive all records adn then have to manually separate the records by Category prior to beginning the data entry process into Access. I would like to have the ability to enter the records as they are received regardless of what category they fall into. Thanks again for the help. I am truly appreciative of your assistance! Sharon "Jeff Boyce" wrote: Sharon In your original post, it seemed like you were saying that sequence numbers 1-200 all belonged in category A. Then all the remaining sequence numbers (201-1800) belonged in category B. If that's true, the sequence numbers would be unique ... and that's what you need for relating to other tables! What am I missing? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... Thank you both for your quick responses. My new hurdle, based on your responses, has to do with the relationships I've set up with other tables. I've used the ID field as my field to relate the tables through. But that will not work based now, will it? Both categories A & B (in the main table) will have the same one-to-many relationship with the other tables and the ID field was the unique field I used to relate them. Any suggestions of what I can use instead? Can I still have the auto-number for that purpose (Unique Identifier) & then do the sequencing in other fields maybe in the main table? I am very rusty since I haven't played with Access in a couple years. Plus I now have 2007 so I'm getting used to the difference in looks... Thanks again for your quick responses. I truly appreciate the help! "Jeff Boyce" wrote: As Arvin points out, the Access Autonumber is not what you're looking for. Based on your description, you have two fields, not one. One field is the "category" (A or B). The other field is a sequence number (1-1800). Don't store those two "facts" in a single field -- instead, use a query to concantenate them as needed. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sharon_wv" wrote in message ... I am designing a database to capture information that will be classified into two separate categories. I would like to autonumber each record by category. Is that possible? For example, I will likely have 2000 total records with 200 of those records in Category A and 1800 of the records in Category B. The information will come to the data entry group in a random format. I would like to prevent separating the information into the two categories manually or need to enter them in two separate forms dependent on the category. Can I have MS Access autonumber them like this?: A001 to A200 (for Category A) and then B001 to B1800 (for Category B) on the same data entry form? If so, how would I go about doing that? Any assistance will certainly be appreciated!! Sharon . . |
Thread Tools | |
Display Modes | |
|
|