Results 1 to 9 of 9
  1. #1
    starlev is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    6

    Inputting information from one table to another

    Hello.
    I am fairly new to Microsoft access however I have a lot of experience with Excel.



    I put together two tables.
    1.Export_no_vat_TBL
    2. Main_TBL


    I'm trying to build a method that if I put information to the "Export_no_vat_TBL" it will automatically input the related information to the Main_TBL
    However I'm not sure how to set this up.

    I am also trying to change the ID type on the Export_no_vat_TBL.
    However I am receiving the following message:
    " First delete its relationships in the relationships window'

    I'm not sure why I am receiving this message as I deleted all relationships between the two tables.

    What am I doing wrong?
    You can find my database at the following link:
    https://drive.google.com/file/d/1H0z...ew?usp=sharing

    Thank you in advance for your help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    queries.
    to add data to a table use an append query.
    select the items in 1 table to add to another.

  3. #3
    starlev is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    6

    More questions

    Quote Originally Posted by ranman256 View Post
    queries.
    to add data to a table use an append query.
    select the items in 1 table to add to another.
    Hi,
    thank you for your help.
    I just watched a video append query.

    However I was wondering if there is a way that automatically once information is
    inputted into one table it would appear on the other table with doing a " append query.
    "

    Also I will appreciate help with my second question.
    I am also trying to change the ID type on the Export_no_vat_TBL.
    However I am receiving the following message:
    " First delete its relationships in the relationships window'


    Thank you for your help.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. You shouldn't be editing data in the table itself. Use a form to do so
    You can run the append query or it's SQL equivalent in the after update event code for your field control on the form

    2. Click database tools ....relationships. Remove the link(s) between the table you want to change and other tables.
    Save the relationships window and close it.
    Change the datatype in your table(s).
    Go back and restore the table links in relationships window. Save
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    starlev is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    6
    Thank you for your help.
    Is there a way to make a save append query?
    Is there a way to only import information that was not imported yet?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What criteria determines data is not in table? A date/time field? What are you importing from? If the source has only new data then just import. If the source is cumulative, that does get a little complicated. Post example data.

    Review https://stackoverflow.com/questions/...g-in-ms-access
    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.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by starlev View Post
    Thank you for your help.
    Is there a way to make a save append query?
    Is there a way to only import information that was not imported yet?
    You can make an append query and save it for future use
    You can also design it so that records will not be appended if they already exist in the destination table
    The screenshot below shows how this can be done:

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	9 
Size:	43.2 KB 
ID:	33155

    In this example records are only appended to the PostcodeZones table if the PostcodeZone field is null
    In other words if that records does not exist in that table
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    starlev is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    6
    Hi,
    I listened to your recommendations however after disconnecting the relationships.
    Appears the + sing for the various fields on the table.
    And I can not understand why.
    Attached is link to a video explaining the issue
    https://drive.google.com/file/d/1a7s...ew?usp=sharinghttps://drive.google.com/file/d/1a7s...ew?usp=sharing
    Your advice would be appreciated

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry I don't understand what you mean and I did watch your video.
    The + sign to the left of records in a datasheet opens a Subdatasheet if there is a linked table.
    If you have broken the links, the Subdatasheet may need to be re-created

    However, forms and subforms provide a better method of viewing data in linked tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 1
    Last Post: 03-01-2018, 01:36 PM
  2. Replies: 4
    Last Post: 01-19-2016, 01:47 PM
  3. Replies: 3
    Last Post: 03-07-2014, 10:39 AM
  4. inputting a calculated value into a table
    By mike_980 in forum Access
    Replies: 1
    Last Post: 10-27-2013, 05:03 PM
  5. Replies: 15
    Last Post: 02-16-2010, 10:58 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