By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

I know it can be done now!

P: n/a
Have been trying, in my spare time, to create an Access db for the
employees in my group for over a month. It surprised me when I read in
the Access Bible that a relationship between the Primary Keys in 2
tables had to be 1:1! I couldn't imagine why this restriction existed!
I even asked here, but didn't get satisfactory answers. My situation is
that I have a list of employees, each having a primary key of Employee
ID, and a list of computers, each having a primary key of Computer ID.
But some of the computers are laptops, and get loaned out to people
with desktops. So I need to create a one to many relationship between
employee ID and computer ID. The Access 2000 Bible says I can't do it
if they are both the primary key in their table, which they are.
Today, I loaded the sample files that come with the book. Guess what?
There is a relationship between 2 of the tables, between their primary
keys, that is 1:many! I knew it didn't make sense to prohibit such a
relatsionship entirely.
So now I want to know, how do I create such a relationship? I noticed
that the one table in the sample file had multiple primary keys. I
don't understand how that's possible, but I guess you can do it in
Access, so it doesn't have to make any sense. Is that part of the trick
to create a 1:many relationship between primary keys? If so, what is
the rest of it?
Thanks for your help!

Nov 13 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
Br
davegb <da****@safebrowse.com> wrote:
Have been trying, in my spare time, to create an Access db for the
employees in my group for over a month. It surprised me when I read in
the Access Bible that a relationship between the Primary Keys in 2
tables had to be 1:1!
You sure? Or were they saying that you can't have many-to-many
relationships?
I couldn't imagine why this restriction existed!
It doesn't.
I even asked here, but didn't get satisfactory answers. My situation
is that I have a list of employees, each having a primary key of
Employee ID, and a list of computers, each having a primary key of
Computer ID. But some of the computers are laptops, and get loaned
out to people with desktops. So I need to create a one to many
relationship between employee ID and computer ID. The Access 2000
Bible says I can't do it if they are both the primary key in their
table, which they are.
tblEmployee -< tblComputers

You can certainly create a one-to-many relationship between
tblEmployee.EmployeeID and tblComputers.EmployeeID. This allows each
employee to be associated with many computers, but each computer can
only be associated with one employee.

Note:

- If you already have data in the table it may prevent a relationship
being created (eg. if you have an tblComputers.EmployeeID value that
doesn't exist in tblEmployees).

- You aren't relating the primary keys. Your relating the primary key in
tblEmployee (EmployeeID) to a field in tblComputers (EmployeeID).
Today, I loaded the sample files that come
with the book. Guess what? There is a relationship between 2 of the
tables, between their primary keys, that is 1:many! I knew it didn't
make sense to prohibit such a relatsionship entirely.
So now I want to know, how do I create such a relationship? I noticed
that the one table in the sample file had multiple primary keys.
It isn't. You can't have mutliple primary keys, but you can have other
keys that aren't primary.
I
don't understand how that's possible, but I guess you can do it in
Access, so it doesn't have to make any sense. Is that part of the
trick to create a 1:many relationship between primary keys? If so,
what is the rest of it?
Thanks for your help!


Sounds liek you need some more study on database theory :0
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #2

P: n/a
Sounds like you need to create a 3rd table which holds these fields as
foreign keys EmployeeID, ComputerID, (optional - explanatory text -
explaining reason for the link). Now link your EmployeeID from
Employee table (primary key) to EmployeeID of 3rd table (1-many) AND
link ComputerID from Computer table (primary key) to ComputerID of 3rd
table (1-many). Further to this, within the 3rd table, make
EmployeeID and ComputerID a combine unique key. Effectively the 3rd
table is a linking table or joining table.

HTH

Steve.

Nov 13 '05 #3

P: n/a
Br
su*********@gmail.com wrote:
Sounds like you need to create a 3rd table which holds these fields as
foreign keys EmployeeID, ComputerID, (optional - explanatory text -
explaining reason for the link). Now link your EmployeeID from
Employee table (primary key) to EmployeeID of 3rd table (1-many) AND
link ComputerID from Computer table (primary key) to ComputerID of 3rd
table (1-many). Further to this, within the 3rd table, make
EmployeeID and ComputerID a combine unique key. Effectively the 3rd
table is a linking table or joining table.

HTH

Steve.

This is how to implement a many-to-many relationship. I don't think that
is necessary given that a computer should only be associated with one
employee at a time (that's how I understood what he was saying anyway:).
Therefore you'd only need to add EmployeeID to the Computer table and
have a one(employee)-to-many(computer) relationship.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #4

P: n/a
See comments in line.

--
Van T. Dinh

"davegb" <da****@safebrowse.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
Have been trying, in my spare time, to create an Access db for the
employees in my group for over a month. It surprised me when I read in
the Access Bible that a relationship between the Primary Keys in 2
tables had to be 1:1! I couldn't imagine why this restriction existed!
That's true. The reason is that each value of each PrimaryKey is Unique so
you cannot have 2 Records (either tblEmployee or tblComputer) of the same PK
value and therefore the relationship tblEmployee_PK - tblComputer_PK can
only have one Record at each end. Hence PK - PK relationship is always
One-to-One.
I even asked here, but didn't get satisfactory answers. My situation is
that I have a list of employees, each having a primary key of Employee
ID, and a list of computers, each having a primary key of Computer ID.
But some of the computers are laptops, and get loaned out to people
with desktops. So I need to create a one to many relationship between
employee ID and computer ID. The Access 2000 Bible says I can't do it
if they are both the primary key in their table, which they are.
The book is correct again (of course).

If you want to create a One-to-Many relationship from Employee to Computer,
you add a Field in the tblComputer which is used as the *ForeignKey*
("Foreign" here means coming from another Table). This ForeignKey Field
will hold the value of the PK value of the Employee who has the use of the
computer. You can put an Index on this ForeignKey Field but not unique
index (since you want 2 or more Records can have the same value)

To show the Field being used as a ForeignKey, I always use the prefix "frg_"
for the Field name, e.g. frg_EmployeeID meaning that this is a ForeignKey
holding the PK value EmployeeID of the tblEmployee.
Today, I loaded the sample files that come with the book. Guess what?
There is a relationship between 2 of the tables, between their primary
keys, that is 1:many! I knew it didn't make sense to prohibit such a
relatsionship entirely.
You misunderstood the book. Cary Prague couldn't be wrong and the book has
been through a number of different versions.
So now I want to know, how do I create such a relationship? I noticed
that the one table in the sample file had multiple primary keys.
Not PrimaryKeys. Each Table can have at most one PrimaryKey! Note that the
PrimaryKey can consist of multiple Fields.

It is more likely that you are looking at the "Many" Table (e.g. tblComputer
in your case) which has a PrimaryKey (e.g. ComputerID) and a ForeignKey
(e.g. frg_EmployeeID).
I don't understand how that's possible, but I guess you can do it in
Access, so it doesn't have to make any sense. Is that part of the trick
to create a 1:many relationship between primary keys? If so, what is
the rest of it?
As above. Try reading all 3 relationships (One-to-One, One-to-Many and
Many-to-Many) in the book and it will become clearer.

Thanks for your help!

Nov 13 '05 #5

P: n/a
If you are joining two tables based on their primary keys, then you are
saying that a relationship exists between the two tables where the
primary key for both is the same. Since there can only be on instance
of a value per table for a primary key, i.e. no duplicates, it only
stands to reason that there can only be one related table per record.

If you want to join tables with a one to many relationship, you need to
join to a field that allows duplicate values in the table on the "many"
side of the relationship.

Nov 13 '05 #6

P: n/a
Not PrimaryKeys. Each Table can have at most one PrimaryKey! Note that >the
PrimaryKey can consist of multiple Fields.


Exactly! the primary key of the second table in the book example is
made of two fields, and in this case it is possible to create a
one-to-many relationship to one of those fileds. This is because
duplicate values are allowed in the fields the comprise the primary key
- so long as the same combination of values is never repeated.

Nov 13 '05 #7

P: n/a
Employee Table
employee_ID Index No Duplicates, Primary Key

Computer Table
computer_ID Index No Duplicates, Primary Key

Loaned_Out Table
employee_ID Index Duplicates Allowed
computer_ID Index Duplicates Allowed

Make the combination of Employee_ID and Computer_ID
in the Loaned_Out table the primary key. You do this
by highlighting both fields in table design and then
select the primary key option.

This example only allows an employee to have a given computer
assigned to him or her one time. If you wanted to have an
employee have the same computer assigned more than once,
then the primary key for the loaned_Out table would be.

Loaned_Out Table
employee_ID Index Duplicates Allowed
computer_ID Index Duplicates Allowed
AutoNumber index No Duplicates

Primary Key is a combination of all three fields.

You could use this method if you wanted to reserve a computer
at a future date if the Loaned_Out Table had loaned_Out_Date
and Returned_Date in it.

Don't confuse primary keys with indexes. Most tables can have
an AutoNumber as the primary key, index no duplicates, and not
use it in any relationships in the database. A relationships
can be created on any indexed field without that field being a
primary key or part of a primary key.

Ron

davegb wrote:
Have been trying, in my spare time, to create an Access db for the
employees in my group for over a month. It surprised me when I read in
the Access Bible that a relationship between the Primary Keys in 2
tables had to be 1:1! I couldn't imagine why this restriction existed!
I even asked here, but didn't get satisfactory answers. My situation is
that I have a list of employees, each having a primary key of Employee
ID, and a list of computers, each having a primary key of Computer ID.
But some of the computers are laptops, and get loaned out to people
with desktops. So I need to create a one to many relationship between
employee ID and computer ID. The Access 2000 Bible says I can't do it
if they are both the primary key in their table, which they are.
Today, I loaded the sample files that come with the book. Guess what?
There is a relationship between 2 of the tables, between their primary
keys, that is 1:many! I knew it didn't make sense to prohibit such a
relatsionship entirely.
So now I want to know, how do I create such a relationship? I noticed
that the one table in the sample file had multiple primary keys. I
don't understand how that's possible, but I guess you can do it in
Access, so it doesn't have to make any sense. Is that part of the trick
to create a 1:many relationship between primary keys? If so, what is
the rest of it?
Thanks for your help!


Nov 13 '05 #8

P: n/a
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be misinterpreting
the book, others tell me my interpretation of the book is correct and
that the book is correct, a relationship between 2 primary keys in
separate tables must be 1:1.
I've looked again at the sample from the book. The 2 primary keys that
have a 1:many relationship both have the same primary key, and one
table has a second key field. In that exact situation, when I create a
link between the tables, Access creates a 1:many relationship between
the fields. So, normally, if I create a link between primary key fields
in 2 tables, Access decides that is a 1:1 relationship. If I create a
second key field in one of the tables, Access decides it's a 1:many
relationship.
As for needing to study my Db theory, Bradley, that's exactly what I'm
doing!
Thanks for all your help!

Nov 13 '05 #9

P: n/a
Unfortunately, Cary Prague's Access Bible is one of a few books I don't
have - I have John Viescas (2 books), Roger Jennings (2), Getz / Gilbert /
Litwin (3 versions), F. Scott Baker, Alison Balter, Rick Dobson, Stan
Leszynski, Chipman / Baron, Ian Blackburn et al, Virginia Andersen, Simpson
/ Robinson, Joseph Adamski, Lars Klander, etc... (to name a few!)

However, let me repeat in point form:

1. Each Table has *at most ONE* PrimaryKey.

2. A PrimaryKey in a Table can consist of up to 10 Fields from the same
Table. When you look in the DesignView of the Table, each of the Fields
used will have the key icon on the LHS. However, each of these icons
signifies that the Field is *part of the PK* and not a PK on its own.

Note that if a PK is a multiple-Field PK, when you refer to the PK, you are
referring to the *whole* set of Fields used in the PK. When you use / refer
to some of the Fields used in the PK, you are *not* using / referring to the
PK.

3. If the relationship is *truly from PK (all Fields used in PK) of a Table
to PK (all Fields used in PK) of another Table, the relationship is always
*one-to-One*

4. However, if the relationship is from a PK of one Table to some Fields
that are used as *part of the PK* in the second Table (and not the whole set
of Fields used in PK in the second Table), Access will default to One to
Many relationship. In this case, the relationship is PK to ForeignKey and
*not* from PK to PK.

From what Billco wrote, my guess is that in Cary Prague's Access book, the
second Table has a multiple-Field PrimaryKey and the relationship is created
between the PK of the first Table to some Field(s) used as part of the PK in
the second Table (which is the scenario I described in the preceeding
paragraph - point 4). My guess is that you thought each Field is a separate
PrimaryKey and therefore the relationship is from PK to PK.

Let me repeat, in this case, the relationship is from the whole PK of the
first Table to *only part* of the PK in the second Table (emphasis: part of
the PK IS NOT the PK) and Access will assume One-to-Many relationship. The
set of Fields from the second Table used in the relationship is called the
ForeignKey due to its use to establish the relationship.

I may not be an expert but I am certain that the above points are 100%
correct.

--
Van T. Dinh


"davegb" <da****@safebrowse.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be misinterpreting
the book, others tell me my interpretation of the book is correct and
that the book is correct, a relationship between 2 primary keys in
separate tables must be 1:1.
I've looked again at the sample from the book. The 2 primary keys that
have a 1:many relationship both have the same primary key, and one
table has a second key field. In that exact situation, when I create a
link between the tables, Access creates a 1:many relationship between
the fields. So, normally, if I create a link between primary key fields
in 2 tables, Access decides that is a 1:1 relationship. If I create a
second key field in one of the tables, Access decides it's a 1:many
relationship.
As for needing to study my Db theory, Bradley, that's exactly what I'm
doing!
Thanks for all your help!

Nov 13 '05 #10

P: n/a
Br
davegb <da****@safebrowse.com> wrote:
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be misinterpreting
the book, others tell me my interpretation of the book is correct and
that the book is correct, a relationship between 2 primary keys in
separate tables must be 1:1.
Yes, you can't relate two primary keys. You relate one primary jey to a
forgeign key (just a field of the same type, and good practice to use
the same name).
I've looked again at the sample from the book. The 2 primary keys that
have a 1:many relationship both have the same primary key, and one
table has a second key field. In that exact situation, when I create a
link between the tables, Access creates a 1:many relationship between
the fields. So, normally, if I create a link between primary key
fields in 2 tables, Access decides that is a 1:1 relationship. If I
create a second key field in one of the tables, Access decides it's a
1:many relationship.
For what you want to achieve you are not relating two primary keys!
As for needing to study my Db theory, Bradley, that's exactly what I'm
doing!
Thanks for all your help!


If a computer can only be loaned to one person at a time then you only
need a one-to-many relationship between the two tables. Employee ID in
the Employee Table (Primary Key) relates one-to-many to the Employee ID
(Foreign key, ie. just a field not in the primary key) in the Computer
table (not the ComputerID which is the primary key).

If a computer can be loaned by more than one person (not likely) then
that is a many-to-many relationshio which is not supported. So, to
handle it you need to create a third table and use two one-to -many
relationships.

Hope it's starting to make sense:)

If you are totally confused I will send you a sample database or
something.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #11

P: n/a
"Yes, you can't relate two primary keys."

Is the above sentence a bit confusing???

Isn't it normal in English to use either:

"Yes, you can ..."

or

"No, you can't ..."

and not:

"Yes, you can't ..."

?
Clearly, you can relate 2 Tables using the PK and "davegb" already described
that. The only problem is that if the relationship is created using the PK
from one Table to PK on the second Table, Access will default the
relationship to One-to-One (which is not what "davegb" wants.)

--
Van T. Dinh


"Br@dley" <n0****@4u.com> wrote in message
news:ZJ******************@news-server.bigpond.net.au...
davegb <da****@safebrowse.com> wrote:
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be misinterpreting
the book, others tell me my interpretation of the book is correct and
that the book is correct, a relationship between 2 primary keys in
separate tables must be 1:1.


Yes, you can't relate two primary keys. You relate one primary jey to a
forgeign key (just a field of the same type, and good practice to use the
same name).
I've looked again at the sample from the book. The 2 primary keys that
have a 1:many relationship both have the same primary key, and one
table has a second key field. In that exact situation, when I create a
link between the tables, Access creates a 1:many relationship between
the fields. So, normally, if I create a link between primary key
fields in 2 tables, Access decides that is a 1:1 relationship. If I
create a second key field in one of the tables, Access decides it's a
1:many relationship.


For what you want to achieve you are not relating two primary keys!
As for needing to study my Db theory, Bradley, that's exactly what I'm
doing!
Thanks for all your help!


If a computer can only be loaned to one person at a time then you only
need a one-to-many relationship between the two tables. Employee ID in the
Employee Table (Primary Key) relates one-to-many to the Employee ID
(Foreign key, ie. just a field not in the primary key) in the Computer
table (not the ComputerID which is the primary key).

If a computer can be loaned by more than one person (not likely) then that
is a many-to-many relationshio which is not supported. So, to handle it
you need to create a third table and use two one-to -many relationships.

Hope it's starting to make sense:)

If you are totally confused I will send you a sample database or
something.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

Nov 13 '05 #12

P: n/a
Br
Van T. Dinh <Va***********@discussions.microsoft.com> wrote:
<>
Clearly, you can relate 2 Tables using the PK and "davegb" already
described that. The only problem is that if the relationship is
created using the PK from one Table to PK on the second Table, Access
will default the relationship to One-to-One (which is not what
"davegb" wants.)
Let's try again.

You can't relate two primary keys one-to-many (invalid in database
theory) so Access makes it one-to-one. However the point is in this case
Employee and Computer are two different entities so you shouldn't be
trying to relate their primary keys at all.

It is simple as I stated before. An EmployeeID field must be created in
the Computer table (a foreign key) and then relate Employee.EmployeeID
to that (one employee to many computers).
"Br@dley" <n0****@4u.com> wrote in message
news:ZJ******************@news-server.bigpond.net.au...
davegb <da****@safebrowse.com> wrote:
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be
misinterpreting the book, others tell me my interpretation of the
book is correct and that the book is correct, a relationship
between 2 primary keys in separate tables must be 1:1.


Yes, you can't relate two primary keys. You relate one primary jey
to a forgeign key (just a field of the same type, and good practice
to use the same name).
I've looked again at the sample from the book. The 2 primary keys
that have a 1:many relationship both have the same primary key, and
one table has a second key field. In that exact situation, when I
create a link between the tables, Access creates a 1:many
relationship between the fields. So, normally, if I create a link
between primary key fields in 2 tables, Access decides that is a
1:1 relationship. If I create a second key field in one of the
tables, Access decides it's a 1:many relationship.


For what you want to achieve you are not relating two primary keys!
As for needing to study my Db theory, Bradley, that's exactly what
I'm doing!
Thanks for all your help!


If a computer can only be loaned to one person at a time then you
only need a one-to-many relationship between the two tables.
Employee ID in the Employee Table (Primary Key) relates one-to-many
to the Employee ID (Foreign key, ie. just a field not in the primary
key) in the Computer table (not the ComputerID which is the primary
key). If a computer can be loaned by more than one person (not
likely)
then that is a many-to-many relationshio which is not supported. So,
to handle it you need to create a third table and use two one-to
-many relationships. Hope it's starting to make sense:)

If you are totally confused I will send you a sample database or
something.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #13

P: n/a


davegb wrote:
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be misinterpreting
the book, others tell me my interpretation of the book is correct and
that the book is correct, a relationship between 2 primary keys in
separate tables must be 1:1. A primary key can only have a 1:1 relationship to another table with the
same CONFIGURATION for the primary keys. A primary key can be 1 or more
fields in combination can make up the primary key. Example: You have an
emplyee table with 500 fields. That is too many fields for a given
table. So yo put 250 fields in one table and 250 in another table. Both
tables use the employee id as the primary key. The relationship between
these two tables can only be 1:1.

If you have an employee table and an attendance table, your primary key
for the employee table is employee id and the INDEX is NO DUPLICATES.
The Attendance table has a primary key of Employee id AND an AutoNumber.
The combimation of each field, (Employee ID and AutoNumber) makes up the
primary key. The INDEX of the Employee ID in the Attendance is
Duplicates OK and the AutoNumber Index is Noe employee master and the
employee id in the Attendance table as 1:Many. This relationship IS NOT
the Primary Keys because you are only relating a single field to another
single field. Your data would look like this:
Emplyee Master - Employee ID Index no duplicates and set as primary key.
ID
1
2
3
4
5
Attendance Master - Employee ID Index DUPLICATES OK and AutoNumber index
NO duplicates, Primary Key is set as the combination of Employee ID and
the AutoNumber.

Emplyee_ID, AutoNumber, Combination of fields or primary key
1 1 11
1 2 12
1 3 13
2 4 24
3 5 35
3 6 36
4 7 47
5 8 58
5 9 59

The combination of the two numbers create a unique value or index for
the primary key in the Attendance table. BUT the relationship of the
employee ID in both table allows you to have a 1:many relation between
the two tables. In the Attendance table the employee ID is NOT a
primary key. It is called a foreign key. The combination of both
fields is the primary key in the Attendance table.

I've looked again at the sample from the book. The 2 primary keys that
have a 1:many relationship both have the same primary key, and one
table has a second key field. In that exact situation, when I create a
link between the tables, Access creates a 1:many relationship between
the fields. So, normally, if I create a link between primary key fields
in 2 tables, Access decides that is a 1:1 relationship. If I create a
second key field in one of the tables, Access decides it's a 1:many
relationship.
As for needing to study my Db theory, Bradley, that's exactly what I'm
doing!
Thanks for all your help!


Access did what I just said above. When you created the relationship,
you only created it between one field in one table to another single
field in another table. When you created a second field and made it
part of the primary key for the second table, Access understands this
and knows it as a 1:many relationship. The key for this being a 1:many
relationship is the index of the second employee ID field being
duplicates OK.

If you had a table with the following indexes and primary key, you could
not even create a second record in the table for a given employee.

Employee ID index no duplicates
Some_Field Index no duplicated
Primary key is combination of Employee ID and Some_Field.

Access would not allow the creation of a second record if the values were:

EmployeeID, Some_Field
1 1
1 2

This violated the index rule of no duplicates for the employee id in the
table.

Like I said before, Don't confuse Indexes and primary keys. You could
have a 1:many relationship between two table with indexed fields and
have NO primary key. All the primary key gives you is a guarantee that
the primary key of one field or the combination of several fields will
only allow the creation of a unique record for that combination as in
the above example.

You could have this as another example:
Employee ID index duplicates OK
Date_Field Index duplicated OK
Primary key is combination of Employee ID and Date_Field.

EmplyeeiD, Date_Field
1 7/13/2005
1 7/14/2005
2 7/13/2005
2 7/14/2005

The employee id and Date_Field do not violate the index rule because you
can have duplicate values in each of these fields. The combined value
of the employee id and date field do not violate the primary key rule
because the combined value of the two field are unique.

But, if you tried to add a second record like this,
1 7/13/2005
This would violate the primary key rule.
Hope this helps,

Ron

Nov 13 '05 #14

P: n/a
>Loaned_Out Table
employee_ID Index Duplicates Allowed
computer_ID Index Duplicates Allowed

Make the combination of Employee_ID and Computer_ID
in the Loaned_Out table the primary key.


Ronald, that would work fine - until the same computer is loaned to the
same employee a second time... Better off using a seperate primary key
for the Loaned_Out table methinks

Nov 13 '05 #15

P: n/a
No, because the index for the computer_ID is Duplicates OK.

BillCo wrote:
Loaned_Out Table
employee_ID Index Duplicates Allowed
computer_ID Index Duplicates Allowed

Make the combination of Employee_ID and Computer_ID
in the Loaned_Out table the primary key.

Ronald, that would work fine - until the same computer is loaned to the
same employee a second time... Better off using a seperate primary key
for the Loaned_Out table methinks


Nov 13 '05 #16

P: n/a
I misread your comment. Yes, your correct. In one of my
replies, I show an example with the same computer loaned out to
the same employee more than once.

Ron

BillCo wrote:
Loaned_Out Table
employee_ID Index Duplicates Allowed
computer_ID Index Duplicates Allowed

Make the combination of Employee_ID and Computer_ID
in the Loaned_Out table the primary key.

Ronald, that would work fine - until the same computer is loaned to the
same employee a second time... Better off using a seperate primary key
for the Loaned_Out table methinks


Nov 13 '05 #17

P: n/a
Thanks for all your replies!
I'm beginning to understand it, thanks to all the help and playing with
it on my own.
Br@dley wrote:
If you are totally confused I will send you a sample database or
something.
Thanks for the offer, but it's starting to make sense. Hearing it from
different points of view really helps.
Br@dley wrote: davegb <da****@safebrowse.com> wrote:
I'm still rather confused! Even the experts here can't agree on what
the rules are. Some of you say there can be a 1:many relationship
between primary keys in separate tables, and I must be misinterpreting
the book, others tell me my interpretation of the book is correct and
that the book is correct, a relationship between 2 primary keys in
separate tables must be 1:1.


Yes, you can't relate two primary keys. You relate one primary jey to a
forgeign key (just a field of the same type, and good practice to use
the same name).
I've looked again at the sample from the book. The 2 primary keys that
have a 1:many relationship both have the same primary key, and one
table has a second key field. In that exact situation, when I create a
link between the tables, Access creates a 1:many relationship between
the fields. So, normally, if I create a link between primary key
fields in 2 tables, Access decides that is a 1:1 relationship. If I
create a second key field in one of the tables, Access decides it's a
1:many relationship.


For what you want to achieve you are not relating two primary keys!
As for needing to study my Db theory, Bradley, that's exactly what I'm
doing!
Thanks for all your help!


If a computer can only be loaned to one person at a time then you only
need a one-to-many relationship between the two tables. Employee ID in
the Employee Table (Primary Key) relates one-to-many to the Employee ID
(Foreign key, ie. just a field not in the primary key) in the Computer
table (not the ComputerID which is the primary key).

If a computer can be loaned by more than one person (not likely) then
that is a many-to-many relationshio which is not supported. So, to
handle it you need to create a third table and use two one-to -many
relationships.

Hope it's starting to make sense:)

If you are totally confused I will send you a sample database or
something.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response


Nov 13 '05 #18

P: n/a
Sorry, should have read further :)

Nov 13 '05 #19

P: n/a
I've read and re-read this thread over the last few days. Each reading
gleans more understanding. I have a pretty good feel for how to set up
my employee/computer db now. I appreciate your time, patience and help!

Nov 13 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.