Duplicating Foreign Key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been unsuccessful in creating a "data entry" form for a database I
have been working on. Is it incorrect to use the same Field as your Foreign
Key in several tables.

Ex. 3 tables - Company, Company Locations, Company Contacts. Both the
locations and contacts need to be linked to the Company table in a
Many-to-One relationship. The FK is the Company ID. Something just isn't
working. Any suggestions?
 
Collette

"use the same field...in several tables" could be interpreted to mean that
you've created multiple tables, each with an Autonumber primary key field,
and are attempting to connect them together by that Autonumber field. If
so, don't!

A bit more information about what you've specifically done (say, a few table
descriptions) would help clarifiy what you're facing.

"something just isn't working" doesn't give us much to go on. Are you
getting error messages?

More info, please...

Jeff Boyce
<Access MVP>
 
No, I am not getting error messages. I am pretty sure you have provided a
solution. The original plan I had was to create a code for the Company Name
to use as the primary key as opposed to Autonumber, a few "teachers" thought
it was an unneccessary and wasteful endeavor.
So what I am understanding is - if I go back and create a code name as the
Primary Key (i.e. Widgets, Inc. = Widgt) this will allow me to use this field
to become a Foreign key in any number of tables?

Thank you for your quick reply.
 
Collette

I am not sure if you and I mean the same thing when you say "allow me to use
this field to become a Foreign key in any number of tables"...

If I designate a field as a Primary Key in Table1, and I add a compatible
field (data type must match) in Table2 but do NOT make it Table2's Primary
Key, I can put a value from Table1's Primary Key into that Foreign Key field
and use it to join the two tables together.

Does that make sense?

Good luck

Jeff Boyce
<Access MVP>
 
I think what Jeff meant is that the foreign keys cannot be
Autonumbers. Change them to Long Integers.

No, I am not getting error messages. I am pretty sure you have provided a
solution. The original plan I had was to create a code for the Company Name
to use as the primary key as opposed to Autonumber, a few "teachers" thought
it was an unneccessary and wasteful endeavor.
So what I am understanding is - if I go back and create a code name as the
Primary Key (i.e. Widgets, Inc. = Widgt) this will allow me to use this field
to become a Foreign key in any number of tables?

Thank you for your quick reply.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Thanks, Jack. Yes, I did mean that you cannot join Autonumbers to
Autonumbers ...

OK, OK, you CAN join two tables by their Autonumbers ... but it means about
as much as trying to take the average of "gender" or street addresses or
[PetName].

Jeff Boyce
<Access MVP>
 
Thanks, Jack. Yes, I did mean that you cannot join Autonumbers to
Autonumbers ...

OK, OK, you CAN join two tables by their Autonumbers ... but it means about
as much as trying to take the average of "gender" or street addresses or
[PetName].
Click to expand...

Hmmmm.... scary thought. But then (at the risk of being politically
incorrect) there are probably a number of people on Davie Street in
Vancouver who are M 1/2 or F 1/2

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Thank you both. I apologize for the delay in responding but I've been busy
with other work issues.

I just took the time today to change the Primary Key in the "main" table(s)
and the Foreign Key in related tables and what do you know...I'm a happy
camper again! It's irritating when something so simple stumps me. (I'm good
for now, but I'm not done with this whole thing yet.)

I really do appreciate your time and humor!!! Have a great day.
--
Collette


Jack MacDonald said:
Thanks, Jack. Yes, I did mean that you cannot join Autonumbers to
Autonumbers ...

OK, OK, you CAN join two tables by their Autonumbers ... but it means about
as much as trying to take the average of "gender" or street addresses or
[PetName].
Click to expand...

Hmmmm.... scary thought. But then (at the risk of being politically
incorrect) there are probably a number of people on Davie Street in
Vancouver who are M 1/2 or F 1/2

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Click to expand...
 
Back
Top