Non-unique AutoNumber

"Vincent Johns" wrote:

Brian wrote:

[...]
One point to clarify, however. I don't think this is just about convenience,
but I can certainly stand to be corrected. Field1 is a base contract number,
and Field2 is the type of contract (there are several types), so that a group
of contracts is tied together by their master number. The master contract
number, however, has no other function.

Woopsie... you need to ignore something I said in a previous message. I
guess you do need to hold onto the [Field1] values. But could you give
your friend a hint that some more suggestive name, such as
[BaseContractNumber], would be more informative than calling it [Field1]?

I was just using Field1/Field2 on the forum to simlify the example; they are
actually called contract_no and doc_no.

We wanted Field1 to be AutoNumbered
to avoid conflicts between users concurrently creating new master contracts.

It should work as long as everyone goes through that one Table to create
new records. But that's apparently not what happened.

Now that there are duplicates in the list, have you been able to resolve
those, such as by giving new contract numbers to some of the records
that share one [Field1] number?

No live data yet, so no concerns there...

The existing table needs to have unique Field1/Field2 combinations; thus the
multi-key (not sure if that's the right term for a PK involving multiple
fields). I think I can see that the original developer was attempting to kill
two birds with one stone by:

1. Having an AutoNumber to ensure that each new record got a new base
contract number, but
2. Allow that base number to be used once with each contract type.

You were talking earlier about splitting the Table. Maybe this would be
a valid reason for doing that, assuming that the contracts sharing a
base contract number are more closely related (in the real world) than
those that don't. You could have a [BaseContracts] Table to which are
linked records in a [ContractPhase] Table that identify the contracts of
various types that share that base contract number. This assumes, as
Tim Ferguson said, that they really do have something in common.

One test that I would apply is to try to think of anything, besides the
number, that should be stored in the [BaseContracts] Table. If you
can't think of any other fields that would make sense there, then don't
split the Table.

I just failed that test, but I still think I need to split. I just need a
unique number, and it has no other relevant information. I wanted AutoNumber
to ensure unique numbers while at the same time avoiding the issue of two
users getting the same number at the same time from a DMax in VBA. The unique
number becomes part of the contract number that is seen by the end user AND
the customer. I'm not sure a timestamp would look so good as a contract
number on a contract or invoice...

As you can see, this is not workable. Should I just drop the AutoNumber &
manually increment Field1 using a DMax(Fields1)+1 on the current table, with
the associated potential conflict between two users who open a new record at
the same time? Or...is my plan to split the table less work & more reliable?

If you just need unique numbers, you could do that, or you could
construct a number based on today's date and time; there are many
possibilities.

[...]

-- Vincent Johns
Please feel free to quote anything I say here.

Brian wrote:

[...]

So far, the responses completely confirm my conclusion, and your concerns &
methods are exactly what I had in mind. Thankfully, there's no live data
yet, though, which simplifies the data integrity part immeasurable.

OK, I'm happy to hear that; you won't have to do anything like what I
suggested.

-- Vincent Johns
Please feel free to quote anything I say here.

"=?Utf-8?B?QnJpYW4=?=" wrote in
:

I was just using Field1/Field2 on the forum to simlify the example;
they are actually called contract_no and doc_no.

Thanks for this: it may be my problem but I always find it easier to talk
cases rather than abstracts.

One test that I would apply is to try to think of anything, besides
the number, that should be stored in the [BaseContracts] Table. If
you can't think of any other fields that would make sense there, then
don't split the Table.

I just failed that test, but I still think I need to split.

I am not doubting you, but I find it very slightly odd to have a Contract
without any attributes: FirstParty? SecondParty? SignedBy? SignedDate?
etc? If you really don't have any fields that are dependent on the
Contract_No without the Doc_No then there is no benefit in splitting the
Contract_No into a one-column table. Only hard work!

Having read through the other responses, I don't have a lot different to
add. One question though: about the numbering requirements for the
Doc_No. Are your users expecting Contract 57 to have Documents 1,2,3 and
4? Or will they be happy with 209(57), 228(57) and 313(57) (if you see
what I mean)? If it's the former, then you have no option (using Jet)
other than roll-your-own numbering of one kind or another, using DMax()+1
or something similar. As other posts point out, this means you have to
prevent or trap attempts at creating records without going through the
coded forms. If the users can put up with Doc numbers looking random,
then using an automatic Autonumber is fine and can be safe. Strictly
speaking, it's redundant to make the PK on (Contract_No, Doc_No) since
the Doc_no would be unique anyway, but there is no harm in it.


I
wanted AutoNumber to ensure unique numbers while at the same time
avoiding the issue of two users getting the same number at the same
time from a DMax in VBA. The unique number becomes part of the
contract number that is seen by the end user AND the customer. I'm not
sure a timestamp would look so good as a contract number on a contract
or invoice...

Fair enough: there are other ways of getting unique serial numbers that
are safe in multiuser situations. What I tend to do is:

1) guess a new number using DMax()+1
2) construct an SQL INSERT statement using it
3) attempt the INSERT
4) if the insert worked, then use the record you just created
5) if the insert did not work, increment the number and go
back to (3)

I can probably dig out the code if you need it but you can likely write
it yourself anyway.

Hope it works out
All the best


Tim F