A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Defining Relationships/Primary Keys between two tables



 
 
Thread Tools Display Modes
  #1  
Old November 2nd, 2009, 03:49 PM posted to microsoft.public.access.tablesdbdesign
ScottMSP
external usenet poster
 
Posts: 15
Default 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  
Old November 2nd, 2009, 05:01 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old November 2nd, 2009, 06:27 PM posted to microsoft.public.access.tablesdbdesign
ScottMSP via AccessMonster.com
external usenet poster
 
Posts: 1
Default 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  
Old November 3rd, 2009, 03:18 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old November 3rd, 2009, 08:11 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.