Results 1 to 7 of 7
  1. #1
    meisaka is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2018
    Posts
    4

    Post Need some help on automatically import records to a table

    Hi all, I am new here. Please pardon me if I am not posting my question clear enough. Thank you for your help in advance.



    We have an inventory database, a table that has live data, and there are some other tables for different pending status, we want to review these records before we process them to the live data pool.

    To make it clear, data entry team will enter records into the pending installation, receiving, relocation, de-installation...etc, tables for review and they they will be send to the live database after it.

    Now, the challenge is that if we want to look up a serial number to see what is the status, we will have to look it up from
    To save time, we are thinking to combine all these data into one single table, and we can just query that serial number in just one single table.

    Questions are, how can we do this automatically and how to delete all the old records before we import the new data?


    Have a nice weekend and thank you a lot.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you import the same format sheet everytime, then take 1 file and
    save as to the same file everytime: c:\temp\File2Import.xlsx

    attach this file as an external linked table: tFile2Import

    build an append query to add this data to the internal data table
    then run the macro

    then the steps will be:
    1. overwrite the file, c:\temp\File2Import.xlsx, with the new data
    2. run import macro
    done


    the macro would have 2 queries:

    Q1 to update existing records.
    join the tFile2Import table to the internal data table on the key field.
    for these matching records, update any fields you need.

    Q2 to add new records.
    (start with Q1) join the tFile2Import table to the internal data table on the key field.
    make an OUTER join: dbl-click the join line,
    set to: show ALL records in tFile2Import table, SOME records in tInternal table
    bring in the key from both tables onto the query fields.
    under tInternal.key , set the criteria = null. (this will show what is missing in tInternal, but new in tFile2Import table)
    make this query an append query and add the fields you want.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by meisaka View Post
    To save time, we are thinking to combine all these data into one single table, and we can just query that serial number in just one single table.

    Questions are, how can we do this automatically and how to delete all the old records before we import the new data?
    If I understand correctly, you want to merge all tables into 1 table? Really? IMHO, that would be a stupendously bad thing to do. You might as well copy everything to an Excel spreadsheet.

    If you are having problems and want to fix the search function (ie find a serial number to see what is the status), then post your dB and allow someone to help you fix the search function. Make a copy of the dB, change any sensitive info, do a "Compact and Repair", zip/compress the dB and post it.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Your description is not very clear to me

    if you are talking merging data horizontally - i.e. data from two records in two tables merged into one record on one table then I'm with Steve. If you are talking about merging data vertically - two records in two tables merged into two records in one table then consider a union query. This will give you your single view and could be the basis for making a new table

    data entry team will enter records into the pending installation, receiving, relocation, de-installation...etc, tables
    if these are in a database then they should be in one table with a flag/indication type field to specify the pending/receiving etc status anyway.

  5. #5
    meisaka is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2018
    Posts
    4
    Thank you all for helping me out.
    We have an inventory database, we usually scan the records and sync them to some pending tables in our database for review and modify before send them to live.
    My goal is to search a serial number in several tables, like pending receiving, pending relocation, pending installation...etc. For example, one serial number may sitting in the pending receiving and before it is updated to live, a installation operation occurred.
    So this serial number will appear in both pending receiving and pending installation.
    This is why I want to merge records from different tables and run a query to see if the selected serial number is appearing in different pending tables.
    I am seeking a easier way to do it other than exporting everything to a spreadsheet and run the search.
    Thank you again.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sounds like you need to use a union query - and from the sound of it you should only have one table, with a field to indicate pending/live etc i.e. effectively your table name

  7. #7
    meisaka is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2018
    Posts
    4
    Thank you SOOOO much. I am testing it and looks like it will be my solution.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-16-2017, 05:50 AM
  2. Replies: 1
    Last Post: 12-20-2015, 01:09 PM
  3. Replies: 11
    Last Post: 07-20-2015, 06:07 PM
  4. Replies: 16
    Last Post: 02-06-2013, 09:23 AM
  5. automatically add records to linked table
    By bcrozier in forum Database Design
    Replies: 3
    Last Post: 04-07-2011, 09:03 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