Results 1 to 4 of 4
  1. #1
    quicova is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    53

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

    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?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by quicova View Post
    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.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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??

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 25
    Last Post: 03-19-2013, 10:08 PM
  2. Replies: 0
    Last Post: 09-17-2012, 03:35 PM
  3. Replies: 1
    Last Post: 07-11-2012, 01:42 PM
  4. Replies: 7
    Last Post: 07-02-2012, 10:50 PM
  5. Replies: 10
    Last Post: 05-08-2012, 09:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums