Problem in "Auto Generate Customer Id"

[malik via AccessMonster.com]

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

 

[John W. Vinson]

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).

 

[malik via AccessMonster.com]

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.

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).

 

[John W. Vinson]

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.

 

[malik via AccessMonster.com]

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

 

[David W. Fenton]

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.

And for the customer who is also a vendor, you'll need to use a
separate join table between your Person table and your PersonType
lookup table so that you can have more than one PersonType.

This is much more common than most people would think. I've almost
never encountered a case where customers and vendors did not overlap
at least once or twice, and often quite a lot more often than that.

 

[David W. Fenton]

If still u can not understand, let me know,,, may be i would be
able to explain it more deeply

Your structure is still wrong. You're trying to do too many things
in a single table, and a string of Boolean fields is very often
evidence of a possible design error. Both your PersonType (which
you're trying to encode in the ID field) and your three Is...
Boolean fields cry out to be moved to another table, the PersonType
in an N:N join table between Person and the PersonType lookup table,
and the Is... Boolean fields to a 1:N table with 0 or more records
for each person.