One last question and i apologise if it is off topic. Since we are talking about indexing and more specifically composite keys, what's the best method in your opinion to trap duplicates? Setting a composite key in table design and use form error event to trap duplication (in order to display a more user friendly message, rather than the Access built in message ) or alternatively setting no composite key and using a domain function , dcount for instance to count the duplicates?
You definitely want to stop the duplicate from being created in the first place. As you suggest, using a composite key with a form error to give a more pleasant message is one way to do it. If you used a surrogate key (autonumber), then you can add a unique index on one or more fields to catch duplicate entries, along with a form message.
Hello. Although i haven't been involved with Access for a long time, i remember that i once used composite keys to prevent duplicate insertion of customers IDs and product IDs at the same order in combination with Form Error event back then (trapping the appropriate error codes). I would like your opinion if composite keys are good for defining relationships between two or more tables. Personally i don't think of that as a good idea it seems too complicated I've never tried myself, i'd rather use an autonumber field or create my custom primary key field
Over the years, I worked with several big highly normalized systems that used composite keys for all relationships in the database. Usually these were older systems, but a few people still design this way. The primary problem with this method is speed. When you have to join not just two, but four or five fields between tables in queries that have many tables, it can not only slow things down greatly, but also can start to become unwieldy for analysts and developers as they look at giant join clauses and try to make sense of them. Generally, I prefer surrogate keys, but don’t mind working in either style.
Composite keys allow you to uniquely identify each record, using data in each row. So, this could be several fields. Some people like a synthetic key instead, which can be just a number on each row to uniquely identify the row. A switchboard is for navigation in the app that you build - a very different purpose. Good question!
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
Who is experiencing composite keys for the first time? Did it trip you up where you didn't expect it?
Man like for real Im binge watching your access videos.... :)
Glad you like them!
One last question and i apologise if it is off topic.
Since we are talking about indexing and more specifically composite keys, what's the best method in your opinion to trap duplicates? Setting a composite key in table design and use form error event to trap duplication (in order to display a more user friendly message, rather than the Access built in message ) or alternatively setting no composite key and using a domain function , dcount for instance to count the duplicates?
You definitely want to stop the duplicate from being created in the first place. As you suggest, using a composite key with a form error to give a more pleasant message is one way to do it. If you used a surrogate key (autonumber), then you can add a unique index on one or more fields to catch duplicate entries, along with a form message.
Hello. Although i haven't been involved with Access for a long time, i remember that i once used composite keys to prevent duplicate insertion of customers IDs and product IDs at the same order in combination with Form Error event back then (trapping the appropriate error codes).
I would like your opinion if composite keys are good for defining relationships between two or more tables. Personally i don't think of that as a good idea it seems too complicated I've never tried myself, i'd rather use an autonumber field or create my custom primary key field
Over the years, I worked with several big highly normalized systems that used composite keys for all relationships in the database. Usually these were older systems, but a few people still design this way. The primary problem with this method is speed. When you have to join not just two, but four or five fields between tables in queries that have many tables, it can not only slow things down greatly, but also can start to become unwieldy for analysts and developers as they look at giant join clauses and try to make sense of them. Generally, I prefer surrogate keys, but don’t mind working in either style.
Thx.. What can these composite keys achieve, by way of a practical example? Also how is it different than a switch board.. thx.. puzzling.
Composite keys allow you to uniquely identify each record, using data in each row. So, this could be several fields. Some people like a synthetic key instead, which can be just a number on each row to uniquely identify the row. A switchboard is for navigation in the app that you build - a very different purpose. Good question!
Hi! Is It possible to use composite key to bring data from that table to another new table?
Yes, by using your key, you can identify rows that you want to move.