Results 1 to 7 of 7
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    Importing data for Linked Tables

    Hi Guys,



    I am working on a Billing Database. I have tblPurchase (Parent Table) & tblPurchaseDetail (Child Table). the tables are linked through tblPurchaseID (Autonumber field - in tblPurchase).

    Now I Have data for both the tables in excel format, except the Purchase ID, as it is assigned by Access upon inserting records. I have no issues importing data for tblPurchase, but when I will try to import data for tblPurchaseDetail (tbl PurchaseID field is not present), how can i link records to corresponding records in tblPurchase.

    In excel file (Data for tblPurchaseDetail) I have bill Numbers mentioned in front of every row to identify to which bill the details actually are linked.

    Awaiting your help and guidance.
    Regards
    Deepak Gupta

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Assuming this is a one off import to create the initial data, then set your tables ID fields to be normal numbers not Autonumbers.
    Import your data then change the data type to autonumber and it should seed correctly.

    If this is an ongoing task then you will have to do some clever lookup and looping to create the records.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Minty,

    Thanks for your reply. It is an ongoing process. What do you mean by lookup and looping????

    Thanks and REgards
    Deepak Gupta

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I expect he means VBA looping through records. Code could read data from the Excel sheets and write to appropriate tables. Are sheets simple enough can be linked? Provide sample for analysis.

    AFAIK, a field cannot be converted to autonumber type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Does your tblPurcahse table has a field for the bill numbers? If yes simply populate the tblPurchase with the new records to get the autonumbers then populate the second tblPurchaseDetail (which will have the PurchaseID missing) and finally run an update query to populate the PurchaseID in tblPurchaseDetail (the query would join the two tables on the bill number field).

    Cheers,
    Vlad

  6. #6
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear June & Gicu,

    Thanks for the quick response.

    Gicu, yes I have the bill number feild in both the tables. Thanks got the idea will try and come back to you. Thanks.

    June, I feel I will be able to get the desired results through Gicu's suggested method but still if unable to get the solution i would upload the sample data for your help. Thanks.

    Regards
    Deepak Gupta

  7. #7
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Gicu,

    Thanks for the solution and sorry for a delayed response.

    Regards
    Deepak Gupta

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

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 11
    Last Post: 09-07-2017, 09:20 AM
  3. Importing/Linked Excel Data into Access
    By daniboi in forum Import/Export Data
    Replies: 1
    Last Post: 03-21-2017, 08:23 AM
  4. Importing data into various tables
    By simba in forum Import/Export Data
    Replies: 1
    Last Post: 01-13-2011, 12:42 PM
  5. Importing and mapping data to various tables
    By rasticle in forum Import/Export Data
    Replies: 1
    Last Post: 05-17-2010, 04:22 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