Two autonumber fields in one table possible?
in [General]
|
From: Sharon_wv on 3 Jun 2010 15:05 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(a)discussions.microsoft.com> wrote in message > news:092C6A28-843F-421D-856D-D2F7E7A87883(a)microsoft.com... > > Jeff - > > > > I have a field to collect the category. The choices are in a separate > > table > > (Category Choice) w/ just two records (Category A and Category B). The > > person doing data entry will select one. That's the easy part for me. > > > > The difficulty comes in the next step -- if the user selects Category A in > > that record, I want it to the assign the next sequential number for that > > Category (e.g. A0121 or A0122). If the user selects Category B, I want it > > to > > assign the next sequential number that that category (e.g. B1825 or B1921, > > etc.). I will probably use the Category Choice table to capture the A and > > B > > by adding another column to that table. Then I will use that field in a > > query to concantenate to give me the Identifier. > > > > The problem I'm having is figuring out how to have Access give me a > > sequential numbers for both Category A and Category B (dependent on user > > selection on form). > > > > Does that make better sense of my scenario and what I am attempting to do? > > I find it difficult to capture all my thoughts and needs for you in a > > message. I appreciate your patience with me. > > > > Sharon > > > > "Jeff Boyce" wrote: > > > >> Sharon > >> > >> If you or your data entry person(s) have to enter data and categorize it, > >> you probably need a field in which to put the category. > >> > >> First add the field to the table. Then add the field to the form. > >> > >> Regards > >> > >> Jeff Boyce > >> Microsoft Access MVP > >> > >> -- > >> Disclaimer: This author may have received products and services mentioned > >> in this post. Mention and/or description of a product or service herein > >> does not constitute endorsement thereof. > >> > >> Any code or pseudocode included in this post is offered "as is", with no > >> guarantee as to suitability. > >> > >> You can thank the FTC of the USA for making this disclaimer > >> possible/necessary. > >> > >> "Sharon_wv" <Sharonwv(a)discussions.microsoft.com> wrote in message > >> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713(a)microsoft.com... > >> > Jeff - > >> > > >> > I'm sorry I wasn't clear. The records to be entered would come to the > >> > group > >> > in a random order and fall into either Category A or Category B. I am > >> > trying to prevent us having to receive all records adn then have to > >> > manually > >> > separate the records by Category prior to beginning the data entry > >> > process > >> > into Access. I would like to have the ability to enter the records as > >> > they > >> > are received regardless of what category they fall into. > >> > > >> > Thanks again for the help. I am truly appreciative of your assistance! > >> > > >> > Sharon > >> > > >> > > >> > "Jeff Boyce" wrote: > >> > > >> >> Sharon > >> >> > >> >> In your original post, it seemed like you were saying that sequence > >> >> numbers > >> >> 1-200 all belonged in category A. Then all the remaining sequence > >> >> numbers > >> >> (201-1800) belonged in category B. > >> >> > >> >> If that's true, the sequence numbers would be unique ... and that's > >> >> what > >> >> you > >> >> need for relating to other tables! > >> >> > >> >> What am I missing? > >> >> > >> >> Regards > >> >> > >> >> Jeff Boyce > >> >> Microsoft Access MVP > >> >> > >> >> -- > >> >> Disclaimer: This author may have received products and services > >> >> mentioned > >> >> in this post. Mention and/or description of a product or service > >> >> herein > >> >> does not constitute endorsement thereof. > >> >> > >> >> Any code or pseudocode included in this post is offered "as is", with > >> >> no > >> >> guarantee as to suitability. > >> >> > >> >> You can thank the FTC of the USA for making this disclaimer > >> >> possible/necessary. > >> >> > >> >> "Sharon_wv" <Sharonwv(a)discussions.microsoft.com> wrote in message > >> >> news:E3AFD309-C3D6-45CF-9E02-920345268681(a)microsoft.com... > >> >> > Thank you both for your quick responses. My new hurdle, based on > >> >> > your > >> >> > responses, has to do with the relationships I've set up with other > >> >> > tables. > >> >> > I've used the ID field as my field to relate the tables through. > >> >> > But > >> >> > that > >> >> > will not work based now, will it? Both categories A & B (in the > >> >> > main > >> >> > table) > >> >> > will have the same one-to-many relationship with the other tables > >> >> > and > >> >> > the > >> >> > ID > >> >> > field was the unique field I used to relate them. Any suggestions > >> >> > of > >> >> > what > >> >> > I > >> >> > can use instead? Can I still have the auto-number for that purpose > >> >> > (Unique > >> >> > Identifier) & then do the sequencing in other fields maybe in the > >> >> > main > >> >> > table? > >> >> > I am very rusty since I haven't played with Access in a couple > >> >> > years. > >> >> > Plus > >> >> > I now have 2007 so I'm getting used to the difference in looks... > >> >> > > >> >> > Thanks again for your quick responses. I truly appreciate the help! > >> >> > > >> >> > "Jeff Boyce" wrote: > >> >> > > >> >> >> As Arvin points out, the Access Autonumber is not what you're > >> >> >> looking > >> >> >> for. > >> >> >> Based on your description, you have two fields, not one. > >> >> >> > >> >> >> One field is the "category" (A or B). The other field is a > >> >> >> sequence > >> >> >> number > >> >> >> (1-1800). Don't store those two "facts" in a single field -- > >> >> >> instead, > >> >> >> use a > >> >> >> query to concantenate them as needed. > >> >> >> > >> >> >> Regards > >> >> >> > >> >> >> Jeff Boyce > >> >> >> Microsoft Access MVP > >> >> >> > >> >> >> -- > >> >> >> Disclaimer: This author may have received products and services > >> >> >> mentioned > >> >> >> in this post. Mention and/or description of a product or service > >> >> >> herein > >> >> >> does not constitute endorsement thereof. > >> >> >> > >> >> >> Any code or pseudocode included in this post is offered "as is", > >> >> >> with > >> >> >> no > >> >> >> guarantee as to suitability. > >> >> >> > >> >> >> You can thank the FTC of the USA for making this disclaimer > >> >> >> possible/necessary. > >> >> >> > >> >> >> "Sharon_wv" <Sharonwv(a)discussions.microsoft.com> wrote in message > >> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171(a)microsoft.com... > >> >> >> >I am designing a database to capture information that will be > >> >> >> >classified > >> >> >> >into > >> >> >> > two separate categories. I would like to autonumber each record > >> >> >> > by > >> >> >> > category. > >> >> >> > Is that possible? For example, I will likely have 2000 total > >> >> >> > records > >> >> >> > with > >> >> >> > 200 of those records in Category A and 1800 of the records in > >> >> >> > Category > >> >> >> > B. > >> >> >> > The information will come to the data entry group in a random > >> >> >> > format. > >> >> >> > I > >> >> >> > would like to prevent separating the information into the two > >> >> >> > categories > >> >> >> > manually or need to enter them in two separate forms dependent on > >> >> >> > the > >> >> >> > category. > >> >> >> > > >> >> >> > > >> >> >> > Can I have MS Access autonumber them like this?: > >> >> >> > > >> >> >> > A001 to A200 (for Category A) and then B001 to B1800 (for > >> >> >> > Category > >> >> >> > B) > >> >> >> > on > >> >> >> > the > >> >> >> > same data entry form? > >> >> >> > > >> >> >> > If so, how would I go about doing that? Any assistance will > >> >> >> > certainly > >> >> >> > be > >> >> >> > appreciated!! > >> >> >> > > >> >> >> > Sharon > >> >> >> > >> >> >> > >> >> >> . > >> >> >> > >> >> > >> >> > >> >> . > >> >> > >> > >> > >> . > >> > > > . >
From: Jeff Boyce on 3 Jun 2010 15:16 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(a)discussions.microsoft.com> wrote in message news:05CF8BE0-E3A8-46BA-B743-1A3595C584BA(a)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(a)discussions.microsoft.com> wrote in message >> news:092C6A28-843F-421D-856D-D2F7E7A87883(a)microsoft.com... >> > Jeff - >> > >> > I have a field to collect the category. The choices are in a separate >> > table >> > (Category Choice) w/ just two records (Category A and Category B). The >> > person doing data entry will select one. That's the easy part for me. >> > >> > The difficulty comes in the next step -- if the user selects Category A >> > in >> > that record, I want it to the assign the next sequential number for >> > that >> > Category (e.g. A0121 or A0122). If the user selects Category B, I want >> > it >> > to >> > assign the next sequential number that that category (e.g. B1825 or >> > B1921, >> > etc.). I will probably use the Category Choice table to capture the A >> > and >> > B >> > by adding another column to that table. Then I will use that field in >> > a >> > query to concantenate to give me the Identifier. >> > >> > The problem I'm having is figuring out how to have Access give me a >> > sequential numbers for both Category A and Category B (dependent on >> > user >> > selection on form). >> > >> > Does that make better sense of my scenario and what I am attempting to >> > do? >> > I find it difficult to capture all my thoughts and needs for you in a >> > message. I appreciate your patience with me. >> > >> > Sharon >> > >> > "Jeff Boyce" wrote: >> > >> >> Sharon >> >> >> >> If you or your data entry person(s) have to enter data and categorize >> >> it, >> >> you probably need a field in which to put the category. >> >> >> >> First add the field to the table. Then add the field to the form. >> >> >> >> Regards >> >> >> >> Jeff Boyce >> >> Microsoft Access MVP >> >> >> >> -- >> >> Disclaimer: This author may have received products and services >> >> mentioned >> >> in this post. Mention and/or description of a product or service >> >> herein >> >> does not constitute endorsement thereof. >> >> >> >> Any code or pseudocode included in this post is offered "as is", with >> >> no >> >> guarantee as to suitability. >> >> >> >> You can thank the FTC of the USA for making this disclaimer >> >> possible/necessary. >> >> >> >> "Sharon_wv" <Sharonwv(a)discussions.microsoft.com> wrote in message >> >> news:2B2AC370-8C05-471E-8BA3-2CEBB0C07713(a)microsoft.com... >> >> > Jeff - >> >> > >> >> > I'm sorry I wasn't clear. The records to be entered would come to >> >> > the >> >> > group >> >> > in a random order and fall into either Category A or Category B. I >> >> > am >> >> > trying to prevent us having to receive all records adn then have to >> >> > manually >> >> > separate the records by Category prior to beginning the data entry >> >> > process >> >> > into Access. I would like to have the ability to enter the records >> >> > as >> >> > they >> >> > are received regardless of what category they fall into. >> >> > >> >> > Thanks again for the help. I am truly appreciative of your >> >> > assistance! >> >> > >> >> > Sharon >> >> > >> >> > >> >> > "Jeff Boyce" wrote: >> >> > >> >> >> Sharon >> >> >> >> >> >> In your original post, it seemed like you were saying that sequence >> >> >> numbers >> >> >> 1-200 all belonged in category A. Then all the remaining sequence >> >> >> numbers >> >> >> (201-1800) belonged in category B. >> >> >> >> >> >> If that's true, the sequence numbers would be unique ... and that's >> >> >> what >> >> >> you >> >> >> need for relating to other tables! >> >> >> >> >> >> What am I missing? >> >> >> >> >> >> Regards >> >> >> >> >> >> Jeff Boyce >> >> >> Microsoft Access MVP >> >> >> >> >> >> -- >> >> >> Disclaimer: This author may have received products and services >> >> >> mentioned >> >> >> in this post. Mention and/or description of a product or service >> >> >> herein >> >> >> does not constitute endorsement thereof. >> >> >> >> >> >> Any code or pseudocode included in this post is offered "as is", >> >> >> with >> >> >> no >> >> >> guarantee as to suitability. >> >> >> >> >> >> You can thank the FTC of the USA for making this disclaimer >> >> >> possible/necessary. >> >> >> >> >> >> "Sharon_wv" <Sharonwv(a)discussions.microsoft.com> wrote in message >> >> >> news:E3AFD309-C3D6-45CF-9E02-920345268681(a)microsoft.com... >> >> >> > Thank you both for your quick responses. My new hurdle, based on >> >> >> > your >> >> >> > responses, has to do with the relationships I've set up with >> >> >> > other >> >> >> > tables. >> >> >> > I've used the ID field as my field to relate the tables through. >> >> >> > But >> >> >> > that >> >> >> > will not work based now, will it? Both categories A & B (in the >> >> >> > main >> >> >> > table) >> >> >> > will have the same one-to-many relationship with the other tables >> >> >> > and >> >> >> > the >> >> >> > ID >> >> >> > field was the unique field I used to relate them. Any >> >> >> > suggestions >> >> >> > of >> >> >> > what >> >> >> > I >> >> >> > can use instead? Can I still have the auto-number for that >> >> >> > purpose >> >> >> > (Unique >> >> >> > Identifier) & then do the sequencing in other fields maybe in the >> >> >> > main >> >> >> > table? >> >> >> > I am very rusty since I haven't played with Access in a couple >> >> >> > years. >> >> >> > Plus >> >> >> > I now have 2007 so I'm getting used to the difference in looks... >> >> >> > >> >> >> > Thanks again for your quick responses. I truly appreciate the >> >> >> > help! >> >> >> > >> >> >> > "Jeff Boyce" wrote: >> >> >> > >> >> >> >> As Arvin points out, the Access Autonumber is not what you're >> >> >> >> looking >> >> >> >> for. >> >> >> >> Based on your description, you have two fields, not one. >> >> >> >> >> >> >> >> One field is the "category" (A or B). The other field is a >> >> >> >> sequence >> >> >> >> number >> >> >> >> (1-1800). Don't store those two "facts" in a single field -- >> >> >> >> instead, >> >> >> >> use a >> >> >> >> query to concantenate them as needed. >> >> >> >> >> >> >> >> Regards >> >> >> >> >> >> >> >> Jeff Boyce >> >> >> >> Microsoft Access MVP >> >> >> >> >> >> >> >> -- >> >> >> >> Disclaimer: This author may have received products and services >> >> >> >> mentioned >> >> >> >> in this post. Mention and/or description of a product or service >> >> >> >> herein >> >> >> >> does not constitute endorsement thereof. >> >> >> >> >> >> >> >> Any code or pseudocode included in this post is offered "as is", >> >> >> >> with >> >> >> >> no >> >> >> >> guarantee as to suitability. >> >> >> >> >> >> >> >> You can thank the FTC of the USA for making this disclaimer >> >> >> >> possible/necessary. >> >> >> >> >> >> >> >> "Sharon_wv" <Sharonwv(a)discussions.microsoft.com> wrote in >> >> >> >> message >> >> >> >> news:4690619D-FEEF-46F1-B8BD-51394AEBD171(a)microsoft.com... >> >> >> >> >I am designing a database to capture information that will be >> >> >> >> >classified >> >> >> >> >into >> >> >> >> > two separate categories. I would like to autonumber each >> >> >> >> > record >> >> >> >> > by >> >> >> >> > category. >> >> >> >> > Is that possible? For example, I will likely have 2000 total >> >> >> >> > records >> >> >> >> > with >> >> >> >> > 200 of those records in Category A and 1800 of the records in >> >> >> >> > Category >> >> >> >> > B. >> >> >> >> > The information will come to the data entry group in a random >> >> >> >> > format. >> >> >> >> > I >> >> >> >> > would like to prevent separating the information into the two >> >> >> >> > categories >> >> >> >> > manually or need to enter them in two separate forms dependent >> >> >> >> > on >> >> >> >> > the >> >> >> >> > category. >> >> >> >> > >> >> >> >> > >> >> >> >> > Can I have MS Access autonumber them like this?: >> >> >> >> > >> >> >> >> > A001 to A200 (for Category A) and then B001 to B1800 (for >> >> >> >> > Category >> >> >> >> > B) >> >> >> >> > on >> >> >> >> > the >> >> >> >> > same data entry form? >> >> >> >> > >> >> >> >> > If so, how would I go about doing that? Any assistance will >> >> >> >> > certainly >> >> >> >> > be >> >> >> >> > appreciated!! >> >> >> >> > >> >> >> >> > Sharon >> >> >> >> >> >> >> >> >> >> >> >> . >> >> >> >> >> >> >> >> >> >> >> >> >> . >> >> >> >> >> >> >> >> >> . >> >> >> >> >> . >>
From: Sharon_wv on 3 Jun 2010 19:22 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 > > . >
From: John W. Vinson on 3 Jun 2010 20:19 On Thu, 3 Jun 2010 16:22:09 -0700, Sharon_wv <Sharonwv(a)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]
From: KenSheridan via AccessMonster.com on 4 Jun 2010 11:24 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
First
|
Prev
|
Pages: 1 2 3 Prev: Wanting to grab group of files and PkZip them. Next: VB code to Access 2000 code |