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 |
#1
|
|||
|
|||
Primary key to prevent duplicates
I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#2
|
|||
|
|||
Primary key to prevent duplicates
It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#3
|
|||
|
|||
Primary key to prevent duplicates
I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto number due to auditing concerns so this is what i needed. Thank you "Jeff Boyce" wrote: It sounds like you are describing a real-world situation in which [InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#4
|
|||
|
|||
Primary key to prevent duplicates
Just out of curiosity, what auditing concern prevents the use of autonumbers?
-- Dave Hargis, Microsoft Access MVP "acss" wrote: I just tried making both fields as primary and it worked fine since it allowed the same invoice with a different vendor number. I can not use auto number due to auditing concerns so this is what i needed. Thank you "Jeff Boyce" wrote: It sounds like you are describing a real-world situation in which [InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#5
|
|||
|
|||
Primary key to prevent duplicates
I have read many postings of the tendancy/possibility of skipping records and
for this reason i do not have any autonumber fields. It would have been better in my DB design to utilize the autonumber for one of my tables but i did not want to take that chance. "Klatuu" wrote: Just out of curiosity, what auditing concern prevents the use of autonumbers? -- Dave Hargis, Microsoft Access MVP "acss" wrote: I just tried making both fields as primary and it worked fine since it allowed the same invoice with a different vendor number. I can not use auto number due to auditing concerns so this is what i needed. Thank you "Jeff Boyce" wrote: It sounds like you are describing a real-world situation in which [InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#6
|
|||
|
|||
Primary key to prevent duplicates
I think you may have some misconceptions about autonumbers.
You don't skip any records, but records may or may not be sequentially numbered without gaps. For starters, autonumber fields do not have to be sequential by 1. You can set them as random, so that random numbers are used and you can set the increment to some value other than 1, so they may be numbered 10, 15, 20, 25, etc. You can guarantee there will almost always be gaps in the numbering. That is because the number is assigned as soon as the first character of a record is entered into the record, but if the record is undone before it is saved, the record is discarded, but the number is not reused. The same goes for deleting records. If you delete a record, its number is not reused. But, even with all that said, autonumbers should have no meaning in a relational database. They should never be exposed to a user. Their intended use is only as a surrogate primary key. It is also useful for using in child tables as a foreign key to the parent. As Jeff said, anytime someone says autonumber, there is a discussion on whether they should ever be used or not. I am in favor of them. My reasoning is that it easily makes a unique primary key. If you have to have multiple fields to create a unique value, particularly if they are not of the same data type, it takes more space in the database and to some degree degrades performance. I have seen one instance where a 5 field key was used that had a text field, a date field, and 3 long integer fields to make up a primary key. It wasn't too bad in Jet, but when we upsized that app to SQL Sever, updates and deletes got really slow. The extra time as in rebuilding indexes. It also means you have to have all those fields in any child records or build your own unique value to relate the child to the parent. But, that is just my opinion. So, I don't see how that is an audit issue. It would be no different than a natural key. How will you know if a record was deleted? If you need true record level auditing, you need to implement auditing in your application to track who added a record and when, every change to every record with who and when and if a record is deleted, who and when. Otherwise, there really is no way to audit a database other than comparing to a backup copy and that doesn't give you any real information on how the changes happened. This is of course considerable overhead, but for regulated or sensitive data applications, it is the better way to do it. So the short of it is, there is no more risk using Autonumbers than there is using natural keys. -- Dave Hargis, Microsoft Access MVP "acss" wrote: I have read many postings of the tendancy/possibility of skipping records and for this reason i do not have any autonumber fields. It would have been better in my DB design to utilize the autonumber for one of my tables but i did not want to take that chance. "Klatuu" wrote: Just out of curiosity, what auditing concern prevents the use of autonumbers? -- Dave Hargis, Microsoft Access MVP "acss" wrote: I just tried making both fields as primary and it worked fine since it allowed the same invoice with a different vendor number. I can not use auto number due to auditing concerns so this is what i needed. Thank you "Jeff Boyce" wrote: It sounds like you are describing a real-world situation in which [InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#7
|
|||
|
|||
Primary key to prevent duplicates
I would like to thank you for the highly detailed explaination and in future
designs , it will be considered since as stated it would have been preferred to use autonumber on at least one field. My error was to pass judgement on what is read in postings than from actual experience practiced. It is always an experience on this news groups and I am profoundly grateful for the professional support I receive on this site. "Klatuu" wrote: I think you may have some misconceptions about autonumbers. You don't skip any records, but records may or may not be sequentially numbered without gaps. For starters, autonumber fields do not have to be sequential by 1. You can set them as random, so that random numbers are used and you can set the increment to some value other than 1, so they may be numbered 10, 15, 20, 25, etc. You can guarantee there will almost always be gaps in the numbering. That is because the number is assigned as soon as the first character of a record is entered into the record, but if the record is undone before it is saved, the record is discarded, but the number is not reused. The same goes for deleting records. If you delete a record, its number is not reused. But, even with all that said, autonumbers should have no meaning in a relational database. They should never be exposed to a user. Their intended use is only as a surrogate primary key. It is also useful for using in child tables as a foreign key to the parent. As Jeff said, anytime someone says autonumber, there is a discussion on whether they should ever be used or not. I am in favor of them. My reasoning is that it easily makes a unique primary key. If you have to have multiple fields to create a unique value, particularly if they are not of the same data type, it takes more space in the database and to some degree degrades performance. I have seen one instance where a 5 field key was used that had a text field, a date field, and 3 long integer fields to make up a primary key. It wasn't too bad in Jet, but when we upsized that app to SQL Sever, updates and deletes got really slow. The extra time as in rebuilding indexes. It also means you have to have all those fields in any child records or build your own unique value to relate the child to the parent. But, that is just my opinion. So, I don't see how that is an audit issue. It would be no different than a natural key. How will you know if a record was deleted? If you need true record level auditing, you need to implement auditing in your application to track who added a record and when, every change to every record with who and when and if a record is deleted, who and when. Otherwise, there really is no way to audit a database other than comparing to a backup copy and that doesn't give you any real information on how the changes happened. This is of course considerable overhead, but for regulated or sensitive data applications, it is the better way to do it. So the short of it is, there is no more risk using Autonumbers than there is using natural keys. -- Dave Hargis, Microsoft Access MVP "acss" wrote: I have read many postings of the tendancy/possibility of skipping records and for this reason i do not have any autonumber fields. It would have been better in my DB design to utilize the autonumber for one of my tables but i did not want to take that chance. "Klatuu" wrote: Just out of curiosity, what auditing concern prevents the use of autonumbers? -- Dave Hargis, Microsoft Access MVP "acss" wrote: I just tried making both fields as primary and it worked fine since it allowed the same invoice with a different vendor number. I can not use auto number due to auditing concerns so this is what i needed. Thank you "Jeff Boyce" wrote: It sounds like you are describing a real-world situation in which [InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#8
|
|||
|
|||
Primary key to prevent duplicates
Thanks for the kudos. I, too, learn every day from these newsgroups.
-- Dave Hargis, Microsoft Access MVP "acss" wrote: I would like to thank you for the highly detailed explaination and in future designs , it will be considered since as stated it would have been preferred to use autonumber on at least one field. My error was to pass judgement on what is read in postings than from actual experience practiced. It is always an experience on this news groups and I am profoundly grateful for the professional support I receive on this site. "Klatuu" wrote: I think you may have some misconceptions about autonumbers. You don't skip any records, but records may or may not be sequentially numbered without gaps. For starters, autonumber fields do not have to be sequential by 1. You can set them as random, so that random numbers are used and you can set the increment to some value other than 1, so they may be numbered 10, 15, 20, 25, etc. You can guarantee there will almost always be gaps in the numbering. That is because the number is assigned as soon as the first character of a record is entered into the record, but if the record is undone before it is saved, the record is discarded, but the number is not reused. The same goes for deleting records. If you delete a record, its number is not reused. But, even with all that said, autonumbers should have no meaning in a relational database. They should never be exposed to a user. Their intended use is only as a surrogate primary key. It is also useful for using in child tables as a foreign key to the parent. As Jeff said, anytime someone says autonumber, there is a discussion on whether they should ever be used or not. I am in favor of them. My reasoning is that it easily makes a unique primary key. If you have to have multiple fields to create a unique value, particularly if they are not of the same data type, it takes more space in the database and to some degree degrades performance. I have seen one instance where a 5 field key was used that had a text field, a date field, and 3 long integer fields to make up a primary key. It wasn't too bad in Jet, but when we upsized that app to SQL Sever, updates and deletes got really slow. The extra time as in rebuilding indexes. It also means you have to have all those fields in any child records or build your own unique value to relate the child to the parent. But, that is just my opinion. So, I don't see how that is an audit issue. It would be no different than a natural key. How will you know if a record was deleted? If you need true record level auditing, you need to implement auditing in your application to track who added a record and when, every change to every record with who and when and if a record is deleted, who and when. Otherwise, there really is no way to audit a database other than comparing to a backup copy and that doesn't give you any real information on how the changes happened. This is of course considerable overhead, but for regulated or sensitive data applications, it is the better way to do it. So the short of it is, there is no more risk using Autonumbers than there is using natural keys. -- Dave Hargis, Microsoft Access MVP "acss" wrote: I have read many postings of the tendancy/possibility of skipping records and for this reason i do not have any autonumber fields. It would have been better in my DB design to utilize the autonumber for one of my tables but i did not want to take that chance. "Klatuu" wrote: Just out of curiosity, what auditing concern prevents the use of autonumbers? -- Dave Hargis, Microsoft Access MVP "acss" wrote: I just tried making both fields as primary and it worked fine since it allowed the same invoice with a different vendor number. I can not use auto number due to auditing concerns so this is what i needed. Thank you "Jeff Boyce" wrote: It sounds like you are describing a real-world situation in which [InvoiceNumber] is NOT unique. Primary keys MUST be unique. Therefore, you can't use [InvoiceNumber] as a primary key. But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a two-field primary key... (this is a starting point for the discussion/argument about using Autonumber primary keys -- "arbitrary" -- rather than "natural" keys). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "acss" wrote in message ... I have an invoice DB and the primary key field is for the invoice number which is unique and should not be duplicated. I have just received duplicate invoices from differnt vendors so how does one work around this scenario? invoiceID field=primary key unique vendor number field=unique |
#9
|
|||
|
|||
Primary key to prevent duplicates
The Autonumber field is used as the primary key field to assure that a
"unique" *record* exists. No two records in the same table will ever have the same primary key. You may also want to create another index in the same table that is not a primary key. This may be one non-duplicate field or multiple fields that are not duplicated in the same *unique* combination. This would solve your immediate problem. It would let you have Invoice 100 with any number of different vendor IDs but no specific vendorID could have more than one Invoice 100. Invoice number is *probably* a text field if you are tracking received invoices. If you are creating the invoices for multiple entities then you might have a key field for an invoice number but it would be better to make the invoice number a text field which would allow both alphabetic and numeric characters to be used in an invoice "number". A two field unique index would allow these combinations of data items in the two fields (four records) : A - A A - B B - A B - B but you would not be able to duplicate any of these combinations again in this indexed table. The index can be extended to include 3, 4, ... or more fields. Information contained in non-key fields can change. This might result in relinking with bad results if any meaningful field were also a key field. For instance autonumber record 36 might contain a single woman's name but if she marries and changes her name she is still the same "unique" person after the name change. Thus the use of separate key and data fields. Another thing to think about concerning key fields is that the key field itself should have absolutely no significance. The only significance it has is that it is "unique". Sometimes users try to force meaning by making a "smart" key field which never helps and may hurt SQL operations. Any other kind of significance than "unique" does not belong in the key field. It belongs in another field in the record. This fact is always implied but is not so often expressed. When you have any pair of parent-child relationships linked you need to keep in mind these parallel concepts: 1) A primary key in the parent table is used as a lookup source by a child table that picks up the primary key and stores it into the child table as a foreign key. 2) Using referential integrity a parent record must exist before a child record can be added. 3) When you are using forms to maintain your data the parent record is displayed on a main form (single record form) while the child record is embedded in a subform (continuous record form) displayed on the parent form. 4) With a single one-to-many (parent-child) relationship the "one" side has the primary key and is on the main form while the "many" side has the foreign key and is on the subform. 5) A many-to-many relationship requires use of an intermediate "resolver table" containing foreign keys from each of the primary keys in the other related tables. It is a compound relationship formed by joining a one-to-many relationship joined to a many-to-one relationship. 6) The combo-box control will handle linking of primary and foreign key fields so that this function is hidden from the user. You can display the key fields on your student forms so that you can see what is taking place when you naviaget through forms or add records.. Practice setting up a single parent-child relationship and creating the related forms to be able to add, change, or delete records in both tables. If you can do this you have successfully mastered an important phase of learning to develop Access relational databases. As you add more related tables to your database the same process will be repeated again and again. An autonumber key field makes a key field unique to each table. An advanced concept is the use of a function to return the next sequential integer to create a primary key numeric field rather than the autonumber datatype which makes a primary number unique to the database rather than a single table. A unique primary key would then exist for all records. No two records in any table would ever share a common primary key. This makes replication and database reorganization a lot easier. But it's not necessary for an Access beginner to understand this. George - HAL-PC - Houston |
Thread Tools | |
Display Modes | |
|
|