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
|
|||
|
|||
Defining Relationships/Primary Keys between two tables
Hello,
I am having difficulty in designing a database and specifically creating the correct relationships and primary keys. Right now I have two tables that have several fields, but I have just listed the fields that I think are important and may be the link between the tables: tblInternal Jobs JobCode (primary key) Grade GradeCategory Other fields… tblPayRanges Grade GradeCategory Other fields… In tblPayRanges, the combination of Grade and GradeCategory would create a unique field that I thought I could link then to tblInternalJobs. When I try to define the relationships so that I can get one to many, it obviously does not work. What do I need to do to create the appropriate one-to-many relationship? Or better, what fields should I have and what links should I make to create the correct relationships? I am trying avoid creating autonumber fields as the primary key as I understand that can be problematic. If that is my only option, then I will do it, but based on my reading, I think I can do it without. Thanks in advance for your help. |
#2
|
|||
|
|||
Defining Relationships/Primary Keys between two tables
I would much rather use autonumbers as primary keys rather than a combination
of fields as a PK. Actually you can get this to work if your data is correctly set up. What makes you say that it doesn't work? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ScottMsp" wrote: Hello, I am having difficulty in designing a database and specifically creating the correct relationships and primary keys. Right now I have two tables that have several fields, but I have just listed the fields that I think are important and may be the link between the tables: tblInternal Jobs JobCode (primary key) Grade GradeCategory Other fields… tblPayRanges Grade GradeCategory Other fields… In tblPayRanges, the combination of Grade and GradeCategory would create a unique field that I thought I could link then to tblInternalJobs. When I try to define the relationships so that I can get one to many, it obviously does not work. What do I need to do to create the appropriate one-to-many relationship? Or better, what fields should I have and what links should I make to create the correct relationships? I am trying avoid creating autonumber fields as the primary key as I understand that can be problematic. If that is my only option, then I will do it, but based on my reading, I think I can do it without. Thanks in advance for your help. |
#3
|
|||
|
|||
Defining Relationships/Primary Keys between two tables
Jerry,
Thanks for responding. The reason I don't think it is working is when I try to cascade changes in the information. I am thinking that if these tables have the correct relationships, I should be able to have a query (or form) that would be able to make changes to all two (or four tables) within the query. When I attempt to define the relationships I can only define one relationship. I conclude that I need more tables and so I created four tables: tblInternalJobs JobCode (primary key text field) Grade GradeCategory tblPayRanges (not sure what would be a primary key because there can be more then one of the same grade. for instance, in the Grade field there can be two of the same grades (1 for example) and in the field GradeCategory, there can be two of the same GradeCategoies (Noncontract for example. However, there cannot be two Grade 1 and GradeCategories Noncontract. The combination of these two would be unique) Grade GradeCategory Minimum Rate Maximum Rate tblGrade Grade (primary key text field) tblGradeCategories GradeCategory (primary key text field) I may be missing something, so any help is greatly appreciated. -Scott Jerry Whittle wrote: I would much rather use autonumbers as primary keys rather than a combination of fields as a PK. Actually you can get this to work if your data is correctly set up. What makes you say that it doesn't work? Hello, [quoted text clipped - 28 lines] Thanks in advance for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
#4
|
|||
|
|||
Defining Relationships/Primary Keys between two tables
Are you receiving a "not updateable" error or message? It's very difficult
to update more than one table with the same query. The best way to do this is to create a relationship with Referential Integrity enabled. Then use a form and subform combination when wanting to change more than one table at a time. Below is more information: http://support.microsoft.com/?kbid=328828 This is a very complicated subject. I suggest that you open up Access Help, go to the Find tab, type in "updatable" (lower case u) or "About updating data", and then scroll down to "When can I update data from a query?" or "Why can't I edit data in my form?". There you will find a lot of, possibly too much, information on the subject. In a nutshell, if the query is based on one table or tables with a one-to-one relationship, you will be able to edit or delete records. If it is based on two or more tables with a one-to-many relationship, you 'should' be able to edit or delete records. If you have three or more tables based on many-to-one-to-many relationships, you will not be able to edit or delete records. This is just the highlights. Help has much more information. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ScottMSP via AccessMonster.com" wrote: Jerry, Thanks for responding. The reason I don't think it is working is when I try to cascade changes in the information. I am thinking that if these tables have the correct relationships, I should be able to have a query (or form) that would be able to make changes to all two (or four tables) within the query. When I attempt to define the relationships I can only define one relationship. I conclude that I need more tables and so I created four tables: tblInternalJobs JobCode (primary key text field) Grade GradeCategory tblPayRanges (not sure what would be a primary key because there can be more then one of the same grade. for instance, in the Grade field there can be two of the same grades (1 for example) and in the field GradeCategory, there can be two of the same GradeCategoies (Noncontract for example. However, there cannot be two Grade 1 and GradeCategories Noncontract. The combination of these two would be unique) Grade GradeCategory Minimum Rate Maximum Rate tblGrade Grade (primary key text field) tblGradeCategories GradeCategory (primary key text field) I may be missing something, so any help is greatly appreciated. -Scott Jerry Whittle wrote: I would much rather use autonumbers as primary keys rather than a combination of fields as a PK. Actually you can get this to work if your data is correctly set up. What makes you say that it doesn't work? Hello, [quoted text clipped - 28 lines] Thanks in advance for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 . |
#5
|
|||
|
|||
Defining Relationships/Primary Keys between two tables
In message , Jerry
Whittle writes Are you receiving a "not updateable" error or message? It's very difficult to update more than one table with the same query. The best way to do this is to create a relationship with Referential Integrity enabled. Then use a form and subform combination when wanting to change more than one table at a time. Below is more information: http://support.microsoft.com/?kbid=328828 This is a very complicated subject. I suggest that you open up Access Help, go to the Find tab, type in "updatable" (lower case u) or "About updating data", and then scroll down to "When can I update data from a query?" or "Why can't I edit data in my form?". There you will find a lot of, possibly too much, information on the subject. In a nutshell, if the query is based on one table or tables with a one-to-one relationship, you will be able to edit or delete records. If it is based on two or more tables with a one-to-many relationship, you 'should' be able to edit or delete records. If you have three or more tables based on many-to-one-to-many relationships, you will not be able to edit or delete records. This is just the highlights. Help has much more information. As an aside, some database systems are unduly pessimistic about which queries/views can be updated. If you come across this situation there is a workaround. First validate your data so that you can be completely certain that you have all of the data necessary to complete all of the updates to every component table. Then create a trigger activated when the query/view is updated. Depending on your database this would be in the database back-end or on a form. If your database permits it, start a transaction. Write separate pieces of code which update each table in turn. Close and commit the transaction Discard the changed data that activated the trigger. Requery. It's not a very elegant solution and if you don't have a good understanding of why your DBMS considers the query/view read-only then you probably shouldn't try it. I've used it to update tables in an SQL Server database with an Access front-end. -- Bernard Peek |
Thread Tools | |
Display Modes | |
|
|