1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2010 Help Merging 4 Excel Files into One Access Database

Discussion in 'Business Applications' started by JacquiD, Nov 30, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. JacquiD

    JacquiD Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    I am working for a company that has four people (four different computers) entering data into four different Excel 2010 files on a shared drive. I would like to create a master file on my own computer for the purpose of generating reports. How do I create one Access database that includes all of the information from the four different excel files? Also, can this Access file be continuously updated as the four Excel files are updated each day? Thank you!!!
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,522
    JacquiD, welcome to the forum.
    You can probably Link to the Excel sheets to keep it updated, however it would be far better if once the data is access they used Access for their day to day business.
    Are the Excel sheets exactly the same layout?
     
  4. JacquiD

    JacquiD Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    Thank you for the response! Yes, they are in the same layout, the same rows/columns. Basically this is a system for my company to record complaints as consumers call in. I want to take the four different excel files (where they are plugging in the information as calls come in) to update one main access file (where I can generate my reports from). I can get them on Access if it works better. Right now, I have to take all four files, merge them, take out the formulas (explained a bit below), and use this to generate charts and counts, etc. They also have to be able to go back in weeks later and change information, as sometimes new info comes in after the initial data entry. So they have to have access to the data sheets in the end.

    I plan on updating them into access, but I want to create a form where they only see one box where they type in a code, and this fills in the information into the data sheet. For example, code 305 fills in the complaint data for foreign objects found in this brand from this manufacturer, etc.

    Right now they are working with an excel file that does this for them through many different plugged in formulas. It's all over the place, really, any help is much appreciated. I've thought of Macros for excel, but I really want to simplify this process through one connected system, and I think Access is the way to go.

    Thank you again, let me know if that made sense :)
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,522
    Yes that makes perfect sense and Access is much better for controlling data Input than Excel.
    You could import one Excel worksheet and then Create tables for the data that you will need.
    All repetitive data like the codes you mentioned should be in their own table which you can use for "selection" of the data, which is better than typing in the code etc.
    The main log table should have the same basic data as the Excel sheet. You can then set up an Append query to add the data from the imported table, which can then be used for the other Excel sheets when you import them.

    Or you could combine all the Worksheets in to one and import that, or import the 3 in to one table.
    Can you post some dummy fictitious data so that I can see what it looks like?
     
  6. JacquiD

    JacquiD Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    Ok, sorry for the late response! Here is the basic layout of the documents I'm talking about. The primary use is to record complaints that come in, right now they are in excel. The (CA Item #) column is where the workers can plug in a 3 digit code, and this populates the rest of the field. They can also go in and fill in things themselves. The codes for the CA Item #'s are on another excel file, there are formulas in place to handle that population. I am trying to make this into a form for the data entry employees to just enter the code into one box, then the information comes up on the screen (ie they don't see a spreadsheet, just a screen, kind of like how cash registers do it if you're familiar with that process). But we have four different documents/workers using this layout, with several hundred rows of information entered each month. At the end of the month, we combine the four documents (copy/paste), taking out the formulas and basically merging them. We delete some of the columns for the reports, so having the formulas still in the cells would mess this process up.
    Then, the data is sorted by brand, manufacturing location, and product description. Each brand is copied and pasted into its own document from there, and the reports are generated per brand/manufacturing location using pivot charts and graphs.

    There's more after this, but I'm thinking one step at a time, such as getting the four documents entered into one master sheet on my end without the copy/paste steps. An automatic updated master sheet (as they put in the info, I can see it) would be AWESOME. I just recently took the position of doing these reports, and after one look at them I KNOW there is a better way to take care of it, and eliminating all the copy/paste/nonesense.

    THANK YOU!! for your help. I really do appreciate it. :)

    All the best,
    Jacqui
     

    Attached Files:

  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,522
    Can you repost the Excel sheet as a .xls please as I do not Excel 2007/10.
    Access with queries and reports would remove all the copy & pasting.
     
  8. JacquiD

    JacquiD Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    Ok - Here is .xls. Thanks!
     

    Attached Files:

  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,522
    I have looked at your Excel sheet and the transfer in to Access should be straightforward.
    I would suggest importing the CA items Worksheet into Access as well as a separate table.

    To normalise the data I would suggest the following tables, which can be populated using the data from the 4 excel sheets after they have been imported.
    Brands Table - BrandID (type Autonumber), Brand (Type text)
    Products - PDID (type Autonumber), Product Description (Type text)
    Locations - LocationID (type Autonumber), Location (Type text)
    Complaint Categories - CCID (type Autonumber), Complaint category (Type text)
    Complaint Descriptions - CDID (type Autonumber), Complaint Description (Type text)
    Possibly Zip Codes with City ( City with Zip), which I can supply for most US Cities.

    The way to update these tables is to set the second fields as Indexed with No Duplicates, then create an Append query based on your Main table for each one that will add the relevant field data to the second field (The Autonumber will be created fro you).
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029095