Access is a relational database management program that allows you to import one or more Excel databases into it, in order to compare common fields between them. Since a single Access file can contain multiple Excel sheets, the program also offers an ideal platform for aggregating or analyzing large amounts of information. However, first you need to import Excel worksheets into Access; luckily it only takes a few steps.
Steps
Part 1 of 3: Preparing to Import
Step 1. Open both programs on your computer
You need to purchase and download a Microsoft Office package that offers both Excel and Access. Look for it on the internet on the Microsoft website.
- Once the programs are downloaded, click "Start" on Windows and select "All Programs".
- Click on "Microsoft Office", then select "Access" (or "Excel") from the drop-down menu. You may already have an Excel spreadsheet on hand, either sent to you by someone or downloaded. With Office you will be able to open it.
Step 2. Arrange your Excel spreadsheets before importing them into Access
You can make the process easier if you complete a few steps before importing. The trick is that the data must be consistent across all sheets.
- Make sure the first row of the Excel sheet contains the column headings (the field names) and that these are clear and easy to understand. For example, a column containing people's last names should have a "Last Name" header. Try to be clear and precise, because it will become easier to compare the headings of an Excel sheet with those of others.
- Access allows you to link common fields between two or more spreadsheets. Imagine you have an Excel spreadsheet that contains your payroll information. This includes people's first and last names, address and salary. If you wanted to combine that sheet in Access with a second one that contains information about contributions to the charity campaign, which contains names, addresses, and donations, you could match the headings of different columns. For example, you could link the name columns to see which people appear in both databases.
- Check the Excel sheets and make sure that each type of data is handled in the same way; make changes if necessary before importing them into Access. In fact, the method used by Access is known as "relational". For example, if the hypothetical payroll sheet contains first and last name in one column, while the second one has first and last name in two separate columns, Access would find no repeating results. The column headings must be identical.
Step 3. Split the information within a column in Excel
To solve the problem presented above, you may need to split information within Excel columns, otherwise Access will not find results for a formatting error.
- As an example, you could split your first and last name into two separate columns if you have another sheet structured that way. At the end of the modification, if you connect the two columns of the surnames in Access, the program will recognize all the entries that appear in both sheets.
- To split a column in Excel, highlight it, then click on "Data" in the program toolbar. Click on "Text in columns". Usually, you will select the "delimited" entry. Click Next.
Step 4. Continue following the wizard to split the merged columns
You are now ready to complete the operation of splitting a column that contains more information into two or more columns.
- Indicates how the data within the column is "delimited". This means that each piece of information in the column is separated by something. The most common options include a space, a comma, or a semicolon. Often the information is separated by a simple space, as in the following example "John Doe". The name Mario is separated from the surname Rossi by a space. In this case you should choose "space" in the wizard.
- Click Next, then click Finish. The program should divide Mario and Rossi into two columns. You can assign new headings to the newly created columns, in order to indicate what information they contain (name, surname, etc.). Before completing the operation it is a good idea to create a few empty columns to the right of the one you are splitting, so that the data is moved into columns that do not already contain information.
Part 2 of 3: Import the Excel Sheets into Access
Step 1. Open Access on your computer
Open the Start menu, select Microsoft Office and click Microsoft Access. You need to open a new blank database to import Excel sheets into.
- Select "blank desktop database" to create a new database in Access.
- Choose a name for the new database if you wish. Click "Create".
Step 2. Import an Excel spreadsheet into Access
The next step is to transfer the Excel sheet you are interested in (or more than one) to the Access database.
- Click on "External Data" in the toolbar once inside the Access database management window. Select "Excel". In some versions of Access, you will find this function by clicking on "File" in the toolbar, then on "Get external data".
- Where you see "Filename", click "Browse". This way you can search for the Excel sheet on your computer.
- Leave the "Import the data source into a new table in the current database" box checked. It will be checked by default.
- When you find the Excel sheet you want to import to your computer, click on it, then click "OK". This will open the Excel to Access Import Wizard.
Part 3 of 3: Use the Wizard to Complete the Import
Step 1. Complete the wizard steps that appear on Access
You need to do this to finish importing your spreadsheet.
- Choose the spreadsheet within the workbook you want to import. Sometimes this is very easy because the Excel file contains a single sheet. However, some users create multiple sheets within a single file, which you can view by clicking on the tabs at the bottom of the workbook. In this case, you need to tell the Access wizard which sheet you want to select. Click Next.
- In the following screen you can choose whether to check the box indicating that the Excel sheet contains the column headings, i.e. a row that identifies the data in each column (such as last name, address, salary, etc.). If you've arranged your Excel sheets before, it's a good idea to make sure the first row contains well-defined column headings. In this case, you can tick the box, using the simpler approach. Click Next.
- If the first row does not contain the column headings, on the next page you will be asked if you want to rename what are called "fields" in Access (similar entries to the column headings). If you haven't already assigned a clear, easy-to-recognize name to all fields (the recommended method), you can do so right now.
Step 2. Complete the import operation
Only a few passages remain. In the following wizard screen you will be asked if you want to specify a primary key.
- It is not mandatory to do so, but you have the option. With a primary key the program will assign a unique number to each line of information. This can be useful for sorting the data in the future. Click Next.
- On the final screen of the wizard you will see a space with a default name. You can change the name of the Excel sheet you are importing (it will become a "table" in Access, on the left side of the page when you complete the import).
- Click "Import", then click "Close". You will see the table on the left side of the screen; the sheet you have chosen has been successfully imported into Access.
- If you want to link more than one data series, repeat the operation with one or more Excel sheets. At that point, you will have the option to compare the data within the sheets in Access.
Warnings
- If the Excel file you want to import was created with a different version of Office than Access, there may be problems during the operation.
- It can't be stressed enough - you have to fix the Excel sheet before importing it. This means you need to analyze your data for problems.
- Always keep a copy of the original Excel sheet, so that if you make a mistake, you can start over from scratch.
- You can't import more than 255 fields into Access.
