Defining Relationships/Primary Keys between two tables

[ScottMsp]

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.

 

[Jerry Whittle]

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?

 

[ScottMSP via AccessMonster.com]

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

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.

 

[Jerry Whittle]

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.

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

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.

--



.

 

[Bernard Peek]

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.