Foreign Key - kind of

[Kelly]

Hi, I have a database that I'm re-doing. The table contains many records. I
split up the data into a more organized structure and all looks good.
The main table points to 4 other table PrimaryID's and I need to get these
put in the main table.

example;

Main Table (tblProd) Includes;
tblProd

MachID
PeelID
TrackPtnID

tblMach includes;
MachID

tblPeel includes;
PeelID

etc...

How do I get the PrimaryID's into the table tblProd? I've tried with a few
queries, like I did with the rest of the data, but it's not working.

Thank you!

 

[Jeff Boyce]

Kelly

"not working" isn't very descriptive.

How does Access (or you) know which MachID to put in which tblProd record?
Whick PeelID? Which ...?

And yes, using tblMach's MachID value in a field in tblProd IS using it as a
foreign key.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clif McIrvin]

Kelly

"not working" isn't very descriptive.

Just a 'wag' here ....

are your primary keys autonumber fields?

If so, then the foreign key fields need to be long integer fields, *NOT*
autonumber.

 

[Kelly]

Hi Jeff,

Yes, you're correct, it is pointing to the foreign key value. The records in
the other tables, tblMach or tblPeel,etc. all have a Primary key value,
identical to the ProdID.

What I need to do is have those same numbers in the same order placed in the
tblProd.MachID or tblProd.PeelID. They are in the correct order. I tried a
paste and it would only allow me to paste appros. 9480 ID numbers. I have
24,000 records.

Thank you,

 

[Kelly]

Hi,

Yes they are autonumber for the (foreign) primary key values. They are in
the correct order, but I need them in the tblProd.MachID, etc.

thanks,

 

[Clif McIrvin]

Hi Jeff,

Yes, you're correct, it is pointing to the foreign key value. The
records in
the other tables, tblMach or tblPeel,etc. all have a Primary key
value,
identical to the ProdID.

What I need to do is have those same numbers in the same order placed
in the
tblProd.MachID or tblProd.PeelID. They are in the correct order. I
tried a
paste and it would only allow me to paste appros. 9480 ID numbers. I
have
24,000 records.

Just how did you separate the data into separate tables? From your OP I
gather that you had one table which for the sake of structure you have
broken up into a main table and four child tables (in 1 to 1
relationships?)

Just now you mentioned pasting key values -- that scares me!

Is it possible to write a query that will properly match up the records
in one of the child tables with the main table? If not, do you still
have a copy of the original table? If so, you can write a select query
that defines a join bewteen the child table and the original table. Do
this for each child table.

Now, make a new copy of the original table and add your four foreign key
fields to the table design of the copy. For each child table select
query, write an update query to write the child_ID value into the
corresponding foreign key field in the main table copy.

When you have successfully written all four foreign key values into the
main table copy and verified the accuracy delete the fields that belong
to the child tables and you will have your new main table complete with
all the foreign key values.



HTH

 

[John W. Vinson/MVP]

Hi Jeff,

Yes, you're correct, it is pointing to the foreign key value. The records in
the other tables, tblMach or tblPeel,etc. all have a Primary key value,
identical to the ProdID.

What I need to do is have those same numbers in the same order placed in the
tblProd.MachID or tblProd.PeelID. They are in the correct order. I tried a
paste and it would only allow me to paste appros. 9480 ID numbers. I have
24,000 records.

If you are trying to link an Autonumber in one table to an Autonumber
in another table... STOP. It will *not work*.

If you are trying to control the value inserted into an Autonumber
field... STOP. It will *not work*.

That is not what autonumbers are intended to do. A Primary Key may be
an autonumber; a foreign key linked to that number must be a Long
Integer number field, not an autonumber.