Both the software Excel and Access have their own advantages. For storing, querying, capturing, and sharing data Access is better. For analyzing, calculating, and visualizing data Excel is better. Hence, you can get all of the benefits by combining them. You can move data from an Excel file to your Access database. The process moving data has been described here.
First Step: Importing data from an Excel file to an Access database
For smooth operation it is better to clean the Excel data before importing them into Access. If there is column that contains addresses (State, city, street address, postal code), then keep each value in separate columns. For removing multiple embedded spaces, trailing, and leading – use the command TRIM. Then non-printing characters should be removed. Next, find any kind of spelling or punctuation errors and fix them. If there are duplicate fields or duplicate rows, then remove them. Be sure that mixed formats are not there in the columns; such as dates as number format or numbers as date format.
When you import the data into Access, choose the perfect data type. After importing data into Access you may have to fix some conversion errors manually. By selecting the actual data type you can minimize this. As an example, Excel has number format “Text” which is similar to the Access data type “Text” and “Memo”. For best practice you should choose the “Memo” data type.
Second step: Use the Table-Analyzer-Wizard to normalize data
If you know about “Normalization”, then the task of this step will become clear to you. When you want to import a well-designed-relational-table into the Access database it does not appear automatically. A method is used to analyze the data and to rearrange them into several related tables. This process is named as “Normalization”. Following instructions will help you to perform this process.
In Access the Table-Analyzer-Wizard has made the normalizing task easier. Now take the selected columns, then drag them into a new table, then create relationships. For renaming a table use button commands, then add primary key. Choose an existing column for primary key; then undo the last-action if necessary. Now go to the third step. To know more about the Table-Analyzer-Wizard this Microsoft site can help you.
Third step: Connecting to the Access data from Excel
After following the above steps – normalizing data in Access, creating a table or query that helps to reconstruct the original data, the tasks become simple. Now the data is in Access. The data is there as an external source. A data connection is used to connect the data from Excel. The connection can be used for logging on, locating, or accessing the external source. Connection information can be stored as different files, such as DSN or Data Source Name, ODC or Office Data Connection file, as well as in the workbook. After updating the Access file you can refresh the Excel workbook from the Access database.
Microsoft also recommends that – to gain more benefits you should use both of the Excel and Access together. By using Access reports and forms with the Excel data, overall tasks can be made more user-friendly.
Image source: http://labibasoft.com/#jp-carousel-658