Problem in "Auto Generate Customer Id"
in [Form]
|
From: malik via AccessMonster.com on 24 Dec 2009 14:16 Hi I have a form named Frmcustomers. in this i have a field called "PartyId" which was auto number generating field. I used the following code for this. Me!PartyId = Nz(DMax("[PartyId]", "[TblParties]")) + 1 Now I want that the code should check the field "PartyId" and make increment in the id which starts from 65.... Example 65001 65002 65003 65004(+1) and so on..... while this field also has some id's starting from 61, 62 and 67 for example 61001, 61002, 61003..... 62001, 62002,.......... 67001,67002,......... Thank You -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1
From: John W. Vinson on 24 Dec 2009 15:30 On Thu, 24 Dec 2009 19:16:09 GMT, "malik via AccessMonster.com" <u49026(a)uwe> wrote: >Hi >I have a form named Frmcustomers. in this i have a field called "PartyId" >which was auto number generating field. I used the following code for this. > >Me!PartyId = Nz(DMax("[PartyId]", "[TblParties]")) + 1 > >Now I want that the code should check the field "PartyId" and make increment >in the id which starts from 65.... >Example >65001 >65002 >65003 >65004(+1) >and so on..... > >while this field also has some id's starting from 61, 62 and 67 >for example >61001, 61002, 61003..... >62001, 62002,.......... >67001,67002,......... > >Thank You What specifically do you want to happen? Do you want separate incrementing groups in the 61, 62, 65 and 67 series; do you just want all numbers to start with 65? What do you want to happen when you reach 65999? As written (depending on where in the code you have your statement) this will assign new numbers based on the largest existing number in the table (67003 using the posted examples). -- John W. Vinson [MVP]
From: malik via AccessMonster.com on 24 Dec 2009 15:57 Actually i want to catagorize diffrent kind of parties in a single table e.g all customers id starts from 65 e.g 65009 all vendors id starts from 63 e.g 63143 so that the user can easily judge the party by id. Wat can be the alternate for this. Thank you. John W. Vinson wrote: >>Hi >>I have a form named Frmcustomers. in this i have a field called "PartyId" >[quoted text clipped - 18 lines] >> >>Thank You > >What specifically do you want to happen? Do you want separate incrementing >groups in the 61, 62, 65 and 67 series; do you just want all numbers to start >with 65? What do you want to happen when you reach 65999? > >As written (depending on where in the code you have your statement) this will >assign new numbers based on the largest existing number in the table (67003 >using the posted examples). -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1
From: John W. Vinson on 24 Dec 2009 18:39 On Thu, 24 Dec 2009 20:57:21 GMT, "malik via AccessMonster.com" <u49026(a)uwe> wrote: >Actually i want to catagorize diffrent kind of parties in a single table > >e.g >all customers id starts from 65 e.g 65009 >all vendors id starts from 63 e.g 63143 >so that the user can easily judge the party by id. That's a Very Bad Idea. You're using the ID for two different purposes. A basic principle of relational design is that fields should be "atomic" - storing only one piece of information in each field. You're trying to store two - a unique identifier AND a classification. What if a vendor (for one product) is also a customer (perhaps for a different product)? What if a customer becomes a vendor? You'll do much better to add a separate PersonType field and not try to apply "intelligence" to your numeric ID. >Wat can be the alternate for this. You have not yet explained what you WANT to happen, so I am baffled how I can answer your question. -- John W. Vinson [MVP]
From: malik via AccessMonster.com on 25 Dec 2009 03:33
Let me Explain my stuff in detail. I have a table name "TblParties" I Want to list all kind of parties in it , either it is a customer, vendor, Broker, or other kind of party. Now I want to keep them different by the Party Code. These all will come in a table but each party in a different row. Now if a customer is also a buyer, for that I have the following Yes/No Field. IsBuyer (Yes/No) IsCustomer (Yes/No) IsBroker (Yes/No) Now I want that if I could make a difference in parties by the initial 2 digits of the party as 65002 65003 65004 These are Customers (But Also can be a Buyers if the "IsBuyer" is True.) And 63002 63003 63004 These are Vendors. So On..... Now I will Have two or three or more forms to add customer,Vendor,Broker seperate for each Now I want that if i m on Customers Form, The Id must be the 65XXXX and Increament. As well If on Vendors , The Id would be 63XXXX and increament. But these all are going to add in a single Table Called "TblParties", But with a different Party code.. If still u can not understand, let me know,,, may be i would be able to explain it more deeply.. Brother thanks for ur response John W. Vinson wrote: >>Actually i want to catagorize diffrent kind of parties in a single table >> >>e.g >>all customers id starts from 65 e.g 65009 >>all vendors id starts from 63 e.g 63143 >>so that the user can easily judge the party by id. > >That's a Very Bad Idea. > >You're using the ID for two different purposes. A basic principle of >relational design is that fields should be "atomic" - storing only one piece >of information in each field. You're trying to store two - a unique identifier >AND a classification. > >What if a vendor (for one product) is also a customer (perhaps for a different >product)? > >What if a customer becomes a vendor? > >You'll do much better to add a separate PersonType field and not try to apply >"intelligence" to your numeric ID. > >>Wat can be the alternate for this. > >You have not yet explained what you WANT to happen, so I am baffled how I can >answer your question. -- Message posted via http://www.accessmonster.com |