Results 1 to 5 of 5
  1. #1
    studor63 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    4

    Angry Linking multiple Excel files - PLEASE help!

    Hi!


    So I've just started with a company where my job is to manage a ton of data. I am new to Access (2010) and am completely impressed by all the awesome things it can do. However, I am running into some issues with knowing the best way to get my current data (which is all in Excel spreadsheets) into Access.

    We are working with about 68 customers in 6 different focus areas (or areas of improvement). To make it easier for them, my company has not required them to enter all their data for each area into a centralized database. Instead, we are pulling reports (into Excel) from 3 different databases. The customers who are not currently submitting data to these databases are sending us Excel spreadsheets with their data (they send a seperate spreadsheet for each focus area). So you can see where my problem lies ... NOTHING is the in the same format. Even then the Excel spreadsheets look different from month to month (and customers submit a new, updated form every month with the current month and previous months) because the people managing the data before I came along changed the format a few different times and the customer has the ability to change them too.

    Basically, I would really really really like to be able to just link the Excel files to my database, but I am struggling knowing how to do that with my data in so many different places and forms. Does anyone have any suggestions - can I even do what I want to do or is my only real option manual entry?? Again, I'm new to Access, so I'm not real familiar with all the ways to go about things, but I want to learn!! Thank you!

    Sincerely,
    one very frustrated human being

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why would it be a hardship on these customers to use a format you prescribe? Standardization and consistency is essential to any relational structure. If you don't enforce this in the data exchange then you are basically up the creek...
    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.

  3. #3
    studor63 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    4
    I completely agree with you and I obviously was not involved in the implementation of my company's data collection . . . unfortunately, even if I could go ahead and implement a new, centralized way of collecting data, I'd still have a HUGE mess on my hands. We're already 3 months into this project and I'm still going to have to figure out a way to move all of the information into my DB. :-/

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is this a one-time exercise or will this data continue to be regularly submitted in this hodge-podge collection of formats?

    You will just have to deal with each file individually. Set link to Excel file, query the data and INSERT/UPDATE records in table, remove link, repeat with next file. This will be tedious, labor intensive, repetitive but without standardization, automation is highly improbable.
    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
    studor63 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    4
    I have data coming in monthly and/or quarterly (depending on how each client is submitting) until at least December 2013. I'm working with some of the project coordinators on at least getting the spreadsheet standardized and cells locked so customers cannot change anything they're not supposed to.

    Thanks for your advice and help though ...

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

Similar Threads

  1. Replies: 9
    Last Post: 10-31-2013, 06:51 AM
  2. Linking to csv files
    By alc in forum Queries
    Replies: 1
    Last Post: 02-07-2012, 07:43 PM
  3. Replies: 4
    Last Post: 06-14-2011, 07:19 PM
  4. Replies: 1
    Last Post: 02-21-2011, 09:55 PM
  5. Linking up two files
    By heidiffg in forum Access
    Replies: 0
    Last Post: 02-14-2011, 09:39 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