Defining Relationships/Primary Keys between two tables

ScottMsp · External Since: Nov 02, 2009 Posts: 1

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 · External Since: Mar 03, 2006 Posts: 1240

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.
>

ScottMSP via AccessMonste · External Since: Nov 02, 2009 Posts: 1

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/Forums.aspx/access-tablesdbdesign/200911/1

Jerry Whittle · External Since: Mar 03, 2006 Posts: 1240

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/Forums.aspx/access-tablesdbdesign/200911/1
>
> .
>

Bernard Peek · External Since: Jul 09, 2009 Posts: 3

In message <71088CC0-0740-4BCB-94F8-6F822993819C DeleteThis @microsoft.com>, Jerry
Whittle <JerryWhittle DeleteThis @discussions.microsoft.com> 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