If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
Non-unique AutoNumber
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. |
#13
|
|||
|
|||
Non-unique AutoNumber
"=?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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber not unique! | Seth Darr | General Discussion | 2 | August 22nd, 2005 05:05 PM |
How do I create a table of unique records from two or more tables | Rubble | Running & Setting Up Queries | 5 | June 23rd, 2005 12:05 PM |
Access 2000, autonumber fields | Zyberg74 | General Discussion | 3 | November 17th, 2004 04:24 PM |
Problem with AutoNumber | accessmonk | Database Design | 2 | September 30th, 2004 08:57 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |