Results 1 to 4 of 4
  1. #1
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21

    Help importing excel to existing table and overwriting some records

    Hi,



    On a monthly basis, I get an excel file that I need to upload to access.

    My primary key is called "Work order". The two relevant fields I am concerned with relate to the work order creation date and work order closure date. The data will always be populated for the "Date created" field, but there will not be anything populated in the date closed field until the physical work is completed.

    So, from month to month, the excel file I receive will have a lot of the same work order numbers on it.

    What I am trying to do, is upload the excel file to my existing table and overwrite any records that have been modified (IE records that should now have the date closed).

    Simply using the upload from excel to existing table does not work, it treats the already created records with missing date closed fields

    I have seen other posts that suggest you need to upload the data to a new table, set a relationship between these tables, create a mismatch query, then an update query. This seems like a lot of work and I'll be honest it pushes the limits of my access knowledge. I am hoping a guru may have a simpler solution!

    Thanks a ton in advance for taking the time to read this.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    On a monthly basis, I get an excel file that I need to upload to access.
    Why do you use Access? Is there more to this application that you haven't told us?

    Stepping back and assuming Access is properly designed and houses a database that is key to your business/application:
    You could start with your Access tables and first import of Excel data.
    Then use the Excel data as a transaction file to update existing records in Access; and insert new records when appropriate.

    I would like to see more details of what you are doing in business(plain English-no jargon). Also what the Access database structure is and what data is available monthly that needs to be put into Access.

    Good luck with your project.

  3. #3
    carrod65 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    21
    Hi,

    Sorry I never saw this reply, I thought I was setup for email notifications for replies and I guess I was not.

    My company was purchased by another company and we are currently working out of 3+ databases for MRP systems and for things like work orders etc. We have a long term project that is expected to complete in 2018 that will consolidate these down to a single system. In the meantime, we are being asked to report on a combined view so we are struggling to condense the data from the 3 systems into a single place so it can be analyzed as an entire data set.

    This can be done in excel but requires many manual steps, vlookup, etc, all things that can be done better in a database. In addition, the volume of data is larger than I feel excel likes to handle, setting filters or even deleting records can cause the program to snag up and stop responding. I will have 200,000+ entries in the combined data set, each entry having 10+ fields, so doing this in excel is not optimal.

    Due to the quantity of data, any solution that requires manually modifying records one at a time will not work.

    I have created my database and it is working well! I just don't know a good way to update a single table (Two of the three systems I pull data from allow you to pull years worth at a time, so there is not a problem there, just with the system where you can only pull a single month at a time).

    I know access will do what I need it to do (It already is doing it for the original data upload), I am just not sure how I would be able to update my one table on a monthly basis in the correct manner.


    Thanks for your reply.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the software for your MRP systems?

    ...we are being asked to report on a combined view so we are struggling to condense the data from the 3 systems into a single place so it can be analyzed as an entire data set....
    What software/database is used in these 3 systems? You mention reporting, and I can see bringing the disparate data into a common reporting facility. But that could be (at least in theory) done through some union queries. The difficulty is the different naming and representation of "the same" data.

    I have been through the merger of government departments; the move to corporate systems and can empathize with you re the size off the task (and the "we haven't done that before..." and "..we have our own system...")
    My suggestion, and this is probably part of the plan for 2018, is to map out the ideal system(enterprise model) for the new
    organization. Get current models for each of the 3 existing systems (or additional if there are more). Work on the transformation from the original/current systems to the ideal. Identify priorities and projects and resources. Get a plan and communicate it.

    What exactly is the analysis that has to be done in the meantime?

    I would separate the longer term planning and details from the ongoing operations with the 3 existing systems.
    Communications and training will be critical to success.

    Good luck.

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

Similar Threads

  1. Replies: 8
    Last Post: 04-20-2016, 01:03 PM
  2. Importing records into an existing table
    By Jamescdawson in forum Import/Export Data
    Replies: 8
    Last Post: 05-18-2015, 04:05 PM
  3. Field F9 error when importing Excel file to existing table
    By dougdrex in forum Import/Export Data
    Replies: 2
    Last Post: 12-26-2014, 01:38 PM
  4. Importing Excel spreadsheet and appending it to an existing table
    By Access_Novice in forum Database Design
    Replies: 3
    Last Post: 08-21-2014, 07:01 AM
  5. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 AM

Tags for this Thread

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