Using foreign key to get related values from one table onto another table

[quicova]

Hello everyone,

I created 2 table that are linked to a link table so to get a many to many relationship.
I have a table for locations and a table for parts.
One location can have many parts and One part can belong to many locations, that is way the many to many relationship.

I'm struggling to find how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.
The values also have to be interactive, if I change the value in the part table it needs to update in the location table.

What can I put in the field of the location table to get a specific value from the parts table?

I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.

If I put the Dlookup function on the default value, those it update if a value is changed in the part table?

Anyone has any ideas on how I can go about this?

[ItsMe]

What can I put in the field of the location table to get a specific value from the parts table?

The only thing I would be placing in the location table is the primary key value from the relative parts table record.

As for a parts table storing prices, you may consider another table to hold the price value. This would make your original parts table closer to a list of parts that contains a PK and a literal description (Name). The parts literal description would be something that, over the years has remained unchanged. like "Brass Widget Economy Large". Then another table like tblPartsNum would contain data that periodicaly changes, i.e., part order number, retail price, wholesale price, contractor rate, etc.

You would constantly update the information within the tblpartsNum, all the time referencing the PK in the original parts table that now only contains the literal description. This new table will eventually have many duplicates of any given parts description. Some records will be old and dated while others current and active.

Then your location table would hold the primary key value from the relative field from the tblPartsNum.

BTW, where is your Inventory table? Location sounds like a field name within an inventory table.

[ItsMe]

After reading post #2 it seems I kinda went off on a rant.

I was just trying to say it seems like most of your struggles are caused by poor table design.

[ssanfu]

I have a table for locations and a table for parts.
One location can have many parts and One part can belong to many locations, that is way the many to many relationship.

I understand this.

I'm struggling to find how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.
The values also have to be interactive, if I change the value in the part table it needs to update in the location table.

What can I put in the field of the location table to get a specific value from the parts table?

I don't understand this..


OK, here is the Parts table design (tblParts):

Field Name	Data Type
PartID_PK	Autonumber - Primary key
PartNum	Text
PartDesc	Text

The Locations table (tblLocations):

Field Name	Data Type
LocationID_PK	Autonumber - Primary key
LocationDesc	Text

And the junction table (tblPartsLocations)

Field Name	Data Type
PartLocationID_PK	Autonumber - Primary key
PartID_FK	Long Integer
LocationID_FK	Long Integer

Image 1
Attachment 13880

The data in the parts table is

PartID_PK	PartNum	PartDesc
1	WH56	Wheels
2	CG742	Main Cogs
3	SP159	Small Sprocket
4	CH9546	Chain
5	MT1	Main Motor

The data for Locations table:

LocationID_PK	LocationDesc
10	Chicago Warehouse
11	Seattle Warehouse
12	Denver Warehouse

Lets say there are:
Wheels in Chicago and Seattle
Small Sprockets in Denver and Seattle
Main Motors in Denver, Chicago and Seattle

The junction table would look like

PartLocationID_PK	PartID_FK	LocationID_FK
1	1	10
2	1	11
3	3	11
4	3	12
5	5	10
6	5	11
7	5	12

If you create a query like Image 1 and drag down the fields tblParts.PartNum, tblParts.PartDesc, tblLocations.LocationDesc
you will see where the parts are located.



If you added a field to the junction table "Quanity", type INTEGER
you could have the quantity for each part in each warehouse.

Image 2
Attachment 13881
The modified junction table would look like:

PartLocationID_PK	PartID_FK	LocationID_FK	Quantity
1	1	10	40
2	1	11	20
3	3	11	100
4	3	12	50
5	5	10	1
6	5	11	2
7	5	12	1

If you create a query like Image 2 and drag down the fields tblParts.PartNum, tblParts.PartDesc, tblLocations.LocationDesc, tblPartsLocations.Quantity
you will see where the parts are located and the quantities.

Does this make sense??
Does this help??