Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47

    Fill foreign key in child table from parent table

    I am fixing some design errors and I need help in correcting these mistakes.



    I have two tables that I have added a column to each containing a foreign key. Table oldtblMasterPartsList contains the primary key PN_ID. I added PN_ID to oldtblMasterSubComponentData (2000 records) and oldtblWOInspectionChecklist (2800 records).

    I need the PN_ID fields populated in these two tables based on the Parent table oldMasterPartsList.MasterPN matching. How do I perform this task?

    Click image for larger version. 

Name:	Capture1.JPG 
Views:	32 
Size:	20.4 KB 
ID:	48552
    Attached Thumbnails Attached Thumbnails Capture1.JPG  

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Maybe like this:

    Click image for larger version. 

Name:	Untitled.png 
Views:	32 
Size:	8.1 KB 
ID:	48553

    Code:
    UPDATE oldtblMasterPartsList 
           INNER JOIN oldtblMasterSubComponentData 
                   ON oldtblMasterPartsList.MasterPN = 
                      oldtblMasterSubComponentData.MasterPN 
    SET    oldtblMasterSubComponentData.PN_ID = [oldtblMasterPartsList].[PN_ID];  

  3. #3
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    This is the result.

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	30 
Size:	17.5 KB 
ID:	48554

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Shouldn' the update be the component table?
    However you said you added the ID field, to both tables, but did you populate that field in the partslist table?, else nothing to update?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    WGM's code assumes the new PN_ID in the Oldmasterpartslist table is an autonumber, as it should be.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Not my code, but still isn't that code updating the wrong table?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by Welshgasman View Post
    Not my code, but still isn't that code updating the wrong table?
    Sorry WGM for wrong attribution.

    The code looks right if oldtblMasterPartsList holds the primary key, both the old key 'MasterPN', and the new key 'PN_ID' (and PN_ID is an autonumber).

  8. #8
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    Yes, that is correct.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So why does it say update partslist and not componenttable, yet sets component table field to partslist field?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by Summit_IT View Post
    This is the result.

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	30 
Size:	17.5 KB 
ID:	48554
    Did you look at oldtblMasterSubComponentData to see if the PN_ID has been updated?

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Quote Originally Posted by Welshgasman View Post
    So why does it say update partslist and not componenttable, yet sets component table field to partslist field?
    Good point. The query grid appears to not agree with the SQL view.

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Summit_IT View Post
    This is the result.

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	30 
Size:	17.5 KB 
ID:	48554
    Quote Originally Posted by Welshgasman View Post
    Not my code, but still isn't that code updating the wrong table?
    Quote Originally Posted by davegri View Post
    Good point. The query grid appears to not agree with the SQL view.

    The sql code was generated by the query builder. I think what's important is the SET clause sets the child table's field equal to the parent table's, despite the order of the prior joining operations... at least it appears to work on my end.

    That said, I'm not sure why OPs preview is getting blanks. I also can duplicate that issue and get blanks in my 'preview'. BUT if I actually run/execute the update it does update the SubComponents table as expected.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I skimmed (gotta run). If that is what you get when putting the query from design or sql view to datasheet view, what you get is the number of records that will be affected. It's not necessarily the result - unless the action query/sql is going to put a whole lot of nulls or zls into the records (but the datasheet view won't tell you that). Run the query and the warning will tell you how many records will be affected, giving you an idea of whether or not that's right. You can always cancel. That assumes you have not disabled warnings.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by kd2017 View Post
    The sql code was generated by the query builder. I think what's important is the SET clause sets the child table's field equal to the parent table's, despite the order of the prior joining operations... at least it appears to work on my end.

    That said, I'm not sure why OPs preview is getting blanks. I also can duplicate that issue and get blanks in my 'preview'. BUT if I actually run/execute the update it does update the SubComponents table as expected.
    Hmm, I created same in my Access 2007 and get
    Code:
    UPDATE Transactions INNER JOIN Transactions AS Transactions_1 ON Transactions.ID = Transactions_1.ID SET Transactions.Amount = [trabsactions_1]![Amount];
    Yes, I know there is a typing error, but I was not going to run it.
    Attached Thumbnails Attached Thumbnails Capture.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    I tried this and I get the following.


    Click image for larger version. 

Name:	Capture3.JPG 
Views:	21 
Size:	33.5 KB 
ID:	48559
    I get this when I View. Which to me looks like the right answer. Which I found out it is correct.
    Click image for larger version. 

Name:	Capture4.JPG 
Views:	20 
Size:	20.8 KB 
ID:	48561

    Then I get this when it is Run.
    Click image for larger version. 

Name:	Capture5.JPG 
Views:	21 
Size:	16.5 KB 
ID:	48560

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parent and Child Table Relations
    By Saabra in forum Import/Export Data
    Replies: 3
    Last Post: 01-12-2020, 06:53 PM
  2. Replies: 2
    Last Post: 09-25-2018, 05:33 PM
  3. Replies: 2
    Last Post: 10-26-2015, 06:14 AM
  4. Replies: 2
    Last Post: 10-07-2013, 11:01 AM
  5. Replies: 10
    Last Post: 01-17-2011, 10:47 PM

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