Here you will find a collection of tutorials for our in-house developed Comprehensive Data Management System for Protocol Patient and IRB Documentation Management (PORTS). You can get PORTS for free.
Written by Tomas Mackevicius.
How to change location of the DB back-end file and other paths
PORTS DB consists of two files: cancer_research_enc.mdb which represents data entry panel (front-end) and cancer_research_enc_data.mdb which contains all data (back-end).
Initially you will need to "connect" database front-end to the back-end data file.
Also you will need to do this for using PORTS in a multi-user environment where multiple users are using DB to query and enter data. You will have to put back-end data file on a file server or other computer with a shared folder accessible by other team members. Then you will have to "connect" front end to back-end. After it's done, you can put the copy of the front-end file to all other workstations.
These are steps you have to perform to "connect" front-end to a new back-end location:
- Open MS Access Linked Table Manager tool:
- "Select All" tables, check the "Always prompt for new location" check box and click "OK":
- Table address should be updated for each individual table, that's why I find it easier to copy entire path with DB name and copy-paste it right into the DB name field following with a click on "Open" (or just press the "Enter" key).
Local address would look like this: D:\Admin\Docs\Research\cancer_db\Clean DB v7.2\cancer_research_enc_data.mdb
Shared location address would look like this: \\Research\cancer_db\cancer_research_enc_ data.mdb
- After all tables were linked, uncheck the "Always prompt for new location" check box and click "OK":
- Now you can open front-end file (cancer_research_enc.mdb) and start working with the PORTS DB. If you are using PORTS in a multi-user environment, you can copy the front-end file to all other workstations that have access to the shared location.
- Next we have to change several paths that are hard-coded in the queries. They are use to build links for opening folders and files. All of them are used in the frmMainFU form:
- Paths should be changed in two queries: qrMainFU (field name: pts_jacket) and qrBPCsubfrm (field name: BPC_dt_link). You have to open the queries in edit mode and change the link part in the expression:
How to set your own values for the drop-down lists
PORTS uses a couple of tables to keep lists of CRAs, PAs, doctors, procedures and other useful things. These lists are used for data entry drop-down selections and they are dynamic - every time your click on drop-down button, database looks to a specific table to produce the list. This is very convenient feature, e.g., you can keep a list of doctors with the information which ones are active in a data table and display only active doctors for the data entry selection with a help of a query that would perform the filtering job.
In order to edit or append the values of lists, you have to manually open corresponding table. All tables that hold lists are named starting with tblLookup.... You can easily locate them and open with double click:
Once the table is open, you can edit any record or use new record line to add a new record.
Other drop-down lists are not produced by using the tables, they are hard-coded. To change them, first you will have to edit back-end database (cancer_research_enc_data.mdb) and change values over there:
Next, you will need to open corresponding data entry form on the front-end (cancer_research_enc.mdb) and paste the same values of the field here:
How to enter new patient
- Most of the demographic data is entered trough the "Main DB" form. To start, select it from the switchboard list:
- Click "New Record" button. New database entry for the patient will be created and ready for data entry. Our experience showed that while searching or entering new patients, users tend to accidentally overwrite data fields of existing patients. This is very important because one may accidentally overwrite patient's MRN (medical record number) which is the most important field that carries related information to all other data tables. In order to avoid this situation, main demographic fields of the main form are locked and cannot be edited. To edit them click on the "Add/Modify Patient Inf." tab:
- Once most important information is in place, return to "Demographics" tab to finish data entry. You may have noticed there are 3 fields for the MRN number. In some of the hospitals patient may be registered several times, so this is our way to keep track of all the accounts related to the patient.
How to search for a patient
- Searching is one of the most used database features. Basically, you can search any field of the database, but most likely you will search for MRN, Last Name or DOB. The rules are simple. First put your mouse cursor to the field you want to search. This is very important, because usually users click "Find" button and then cannot understand why they do not find anything You search the field where the mouse cursor is blinking.
- Once you selected the field, click the "Find" button.
- You will be presented with "Find and Replace" dialog box where you can enter your search criteria. Field "Look In" shows you what field you are searching (where your cursor was positioned):
- If you're using MRN2 and MRN3 fields, and want to search combined field MRN, MRN2, MRN3 - you will have to include asterisks before and after search phrase eg. *12345*. If patient has two MRNs on record but you will search without asterisks, you will not find that patient.
If your remember just part of the last name, you can search for Johns* (full last name could be Johnson).
- Search for date using the same format you're using for the data entry eg. 1/12/1947
What are False ID and Study ID used for
In the top left corner of Main Form you can see two fields named False ID and Study ID. What are those used for?
False ID is automatically generated unique number, representing one patient. It helps to maintain relationships within database, but also it is used for those cases, when you want to give analytical data to outsiders. In such case because of the HIPAA requirements, all personal patients' information should be removed. But what if you still need to have some kind of case identifier in order to track patient and later update the data set?
That's where we use False ID field. Because your database is the only place that maintains relationship between False ID and real patients' data, it is safe to use that number even in public data sets, because outsiders will never have access to your database and because of this reason, will never have means to track the identity of the patient. That's why we call it "false".
Study ID field will list all study IDs of a patient who's participating in clinical trials. Sometimes you just need a quick way to find the patient by his unique study number.