Tech Support Guy banner
Status
Not open for further replies.

Access 2010 Help Merging 4 Excel Files into One Access Database

7K views 7 replies 2 participants last post by  OBP 
#1 ·
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 ·
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?
 
#3 ·
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 :)
 
#4 ·
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?
 
#5 ·
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
 

Attachments

#8 ·
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).
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top