Allocating primary and secondary numbers

[luluwai]

I want to give each new company a primary (ID) number such as 161, 162,... incrementing by one as each new company is added to the database. Now as contacts within the company are added to the database, I want to allocate them under the same primary number, but as a decimal number of the primary number, such as 161.1, 161.2, 161.3,....., etc.

Methinks this is not an easy task? Whereas in excel it is relatively easy starting with a seed number then subsequent numbers are =ROUND(IF(C5<>C4,INT(B4+1),B4+0.1),1), etc.

All ideas and/or solutions accepted.

[bcooney]

Don't do it. Key fields should be meaningless auto-enter serial numbers.

[tachyon]

how about you keep your primary company field inviolate and have a second field for the contact and then concatanate them into a third field ( the only one that appears on the layout). It would look like what you want but keep the serial numbers sacred.

[luluwai]

Yes, I had looked at this possibility. So, the question is how to get the system to automatically allocate the next (highest) primary number if the company is new to the database, but if it already exists and only a new contact is being added, to increment the secondary (decimal) field to the next highest number, while leaving the already allocated primary number untouched?

There is a "horse and cart" conundrum to be resolved in the process and the difficulty may be deciding which comes first, then dealing with it!

[tachyon]

I see the problem... you can't use either of these fields as a unique identifier / serial number. You would need to leave that one alone and make a new field that incremented via a new record script. It would be a "fake" company identifier and could be selected from a value list when the record was created - If the selection is an existing value then the contact field increments. If you make a new company number, the contact number would become "1". But you probably shouldn't pay too much heed to what I reckon

[comment]

As Barbara said - don't do it. Consider what would happen when a contact leaves. You wouldn't want the following contacts in the same company to change their numbers to close the gap, would you? So there goes your nice orderly scheme. And what if a contact leaves to join another company within your database?

[LaRetta]

I want to give each new company a primary (ID) number such as 161, 162,... Now as contacts within the company are added to the database, I want to allocate them under the same primary number

It also sounds like they are being added within the same table. I hope this isn't the case.

and the difficulty may be deciding which comes first, then dealing with it!

Not if Contacts are a separate table. They get their OWN serial and also inherit the company's serial - and THAT is what ties them together.