In our previous article, we described the basics of creating a database using Microsoft Access to capture and safely store the data collected from investigator initiated trials (IIT). However, while Access excels in data organization and storage, you may want to use another software interface, such as Microsoft Excel, R, SAS, or SPSS for your data analysis. So as a follow-up, in this article, we’ll describe how to import and export data from Microsoft Access.
Understanding Fields and Records
Microsoft Access stores recorded data in records (rows) and fields (columns). A record contains specific data about an individual or a product. On the other hand, a field is a category of information, such as a name, phone number or e-mail address. You will need to specify the property of the field (number, date, text, etc.), as in what type of information it contains, in order to help you better organize and sort the database. If you’re familiar with Excel, then the formatting rules of tables in Microsoft Access is no different.
Importing Data
Previously, we covered how to create a database from scratch, but what if you wanted to access information from another database? In fact, most organizations will utilize multiple databases. For instance, there may be one database for recruiting subjects for a study and several other study-specific databases to store patient collected data. If you wanted to get information from another database, the simplest way would be to copy and paste the data into a table within the new database. However, this process creates a lot of duplicated data and does not automatically update the information if the original data were to change.
Importing data from another database or from an existing Excel file allows for much better control and flexibility. Using this method, the information in the imported database will automatically mirror the original database. This is especially useful where data in some tables, such as information on participants, need to be shared between various databases. There are several options to import data from other sources, which can be found under the External Data Tab. A particularly useful feature is to import only the structure of the database without copying the actual data.
Exporting Data
Most organizations will use Microsoft Access as a data storage tool, and another software, such as Excel, for data analysis. Exporting data from Microsoft Access is fairly straightforward and can the various export options can be found in the External Data tab. However, due to compatibility issues, keep in mind that there are some restrictions on the type of information you can export depending on the final file format. For instance, you can export a table, query, form, report, macro, or module to another Access database. In contrast, you can only export tables, queries, forms, and reports to Excel.
Exporting data into Excel, R, SAS, or SPSS allows you to easily analyze the data stored in your database and create useful graphs and figures. Before exporting from the database, first determine the type of information you need for your analysis. In most cases, you probably do not want to export all the information stored in the tables, as this would make data analysis a lot more confusing and prone to error. To simplify the tables, run a query on your desired table using Query -> Query Wizard. In this manner, you can select exactly the fields (columns) that you want to be exported. For instance, in a clinical study, you may only want to export the subject ID and their clinical results but omit their actual names or other potential identifiers. It’s also a good idea to sort the records from the query result until you are satisfied. Additionally, whenever you are exporting a table or a query, you can also select which specific records that you want to export.
As you can see, Microsoft Access is a powerful database tool that can improve your workflow when it comes to collecting data for a clinical trial. Data that is collected is safely stored in the database, which can then later be retrieved and exported into a user-friendly spreadsheet format for data analysis. If you still need more help with Microsoft Access or have any other questions regarding an IIT, please contact Sengi.
