Whether you need to record data from a case report form or recruit participants for a new study, you likely have come across a database. Databases are systems to collect, organize, and manipulate large amounts of data. Being able to create and manage them is essential to ensure the success of your investigator-initiated trial (IIT).
When it comes to clinical studies, there are numerous types of software that you can use to help you create and manage your clinical database. However, many of these commercial tools that specialize in clinical data management, such as Oracle Clinical (Oracle), Clintrial (Clinsoft), or MACRO (Elsevier), may be too sophisticated and expensive for your needs. Luckily, you may already have an excellent database creation tool on your computer. If you have Office 365, or even previous versions of Microsoft Office, then you already have a database management program called Microsoft Access. Unlike Word or Excel, however, which you likely already know how to use, Access is a tool that is often less employed. So, in this article, we’ll teach you all the basics of utilizing this powerful asset to help you build a database for your IIT.
The Basics
Microsoft’s Access database system operates on four distinctive principles: tables, queries, forms, and reports.
- Tables are the heart of the database, where all information is stored. Think of tables as lists containing a particular set of data. In Microsoft Access, you can have numerous different tables that are linked together using unique keys to produce an enormous database.
- Queries are questions you’d like to ask the database. For instance, you might want to retrieve specific data from a table and display only the subjects’ ID, their prescription information (Sphere, Cylinder, Axis), as well as their uncorrected and best corrected visual acuity.
- Forms are used to enter new records into the database or update changes to the existing database. They are more user-friendly to use compared to entering data directly into the tables. Numerous users can simultaneously enter data into the same table using forms.
- Reports output the data in a format that’s easy to read and evaluate.
Figure 1. Image showing an example Microsoft Access database with key elements: 1) Tables 2) Queries 3) Forms 4) Reports 5) Tell me what you want to do feature 6) Fields and 7) Records
Creating your database
If you’re new to the database creation process, we recommend that you first choose a database template from the list of available templates on Access. This will allow you to get a flavour of how the software works. However, you can also select a blank database to start. Unlike other Microsoft software like Word and Excel, the moment you create your database, you will have to specify a save location on your computer. Each time you make structural changes to your database, such as changing the table layout or changing the properties of fields, the software will prompt you to save. Note that any data entered into the database is saved automatically.
In the create tab, you will find options to create tables, queries, forms, and reports. In Office 365, there’s also a very useful feature called “Tell me what you want to do,” where you can simply type in what task you want to achieve, and it will give you a list of recommended functions.
Tables
Similar to Microsoft Excel, tables in Access consists of fields (columns) and records (row). Each field represents a category, such as a person’s name, address, and age. A record contains the information for a specific individual. Whenever you add a new field to your table, you first have to specify the type of data (short text, number, date, time, etc.) it will contain. The first field is usually the ID field, and the datatype by default is set to an auto number. We recommend you leave this as the default so that the software automatically generates a unique number each time you enter a new record.
If you want more advanced controls of your fields, you can access the design view under Home View Design view. In this mode, you can adjust and customize the properties of each field to your specific needs. Each time you make a change to the table, Access will prompt you to save the database. It’s important that you spend a lot of time designing your tables because you should not make changes to them after data has been entered.
Forms
Forms are an intuitive way to enter data into your tables. You should never directly enter information in your tables because you may accidentally delete some information. Additionally, using forms allows multiple users to work on the same database. In the create menu, there are a number of options to help you get started to create your data entry form. We recommend that you use the Form Wizard, which will guide you through creating a user-friendly form. For IITs, these forms should be similar to the design of your case report forms (CRFs). At this point, feel free to customize the look and design of the forms to best suits your needs.
Linking Tables
The tables within Microsoft Access can be linked together by primary and foreign keys to create a relational database. This allows you to combine and manipulate data from various tables. For example, in one table, you may have the information containing your patient ID, name, age, and e-mail address. In another table, you have data containing the test number, patient ID, date of visits, and the treatment outcome. In order to link these tables together, you would assign the primary key to the patient ID in the first table and a foreign key to the patient ID in the second table. Now these tables are linked together, and thereby also their information.
When choosing which field you want to assign as your primary key, make sure that the data in that field is always unique for each row. In other words, there should be no duplicates or similar information in that particular column. For example, assigning the primary key to a name field is not recommended because you may have people with the same first name. Additionally, the field assigned with the primary key should always contain data and never be left blank. The relationships between tables using keys can be defined under Database Tool Relationships.
Need More Help?
Hopefully, this article will help you get started on creating a database for your IIT study. If you want more help, here’s a link to an excellent video tutorial on how to use Microsoft Access (https://www.youtube.com/watch?v=xRYSP-yFgb0&ab_channel=SaliKaceli). For help on creating databases with more advanced features or other clinical study-related problems, please contact Sengi.
