Electronic Lab Notebook

Purpose:

The purpose for us was to use this first assignment with Access and the FDA Drug Database to see how raw data sets can be distributed and formatted.

The other purpose of this assignment was to get practice with database queries using Microsoft Access and its graphical interface.

Methods

Preliminary Tasks

Turned on Windows 10 File Extensions

Went to the search menu and typed File Explorer Options" in the search field.
Selected the result
Clicked on the view tab in the File Explorer Options
Unchecked the "Hide extensions for known file types" button.
Clicked OK
Despite warnings that the Seaver 120 computers were set to restore defaults once they have been restarted, the file extensions did not go away. Neither did any of my files. It is possible the computers were not restarted.

Downloading

Note: I did not follow the download options for Firefox and Chrome. When I read this part of the Week 8 assignment I misinterpreted it as an explanation on how to download firefox or Chrome in case the seaver computer we were working on did not include it and then thought it didn't apply to the computer I was working on because I had Chrome. I see now that it was about downloading the FDA Database and not about the actual browser.

To download the database I went to the FDA's official website in the section for the Data Files called Drugs@FDA Data Files
I downloaded the March 5th version of the data which had just been updated that day.
The file was compressed as a .zip file with the raw data sets inside.
I used Windows 10 to open the file and then selected the pink "Extract" button and saved the files in a new folder on the desktop.

Microsoft Access Tasks

My methods and processes are detailed in the below sections. First though, I had to open Microsoft Access by searching for it on the search bar that comes up in the bottom left of the computer. Then I clicked on the icon to open it. Once open, I selected File>New and then clicked on the icon labeled "Blank Database". I then pressed "Create".

Importing & Cleaning Data

First I imported MarketingStatus.
I deleted MarketingStatus because Dr. Dionisio wanted us to follow along in setting it up without importing so we could learn the skills needed to put it together rather than just importing everything.
We made the MarketingStatus table by following along step by step from Dr. Dionisio.
This meant changing the datatype of cells to match a format we could import the data into.
To make sure the data would work, we used a guide Dr. Dionisio wrote on the class whiteboard which I have included at the end of this section.
We then changed the default data types in each column to reflect a datatype that would correctly take the data.
Then we imported the data once more by selecting the External Data Ribbon > New Data Source > From File > Text File. Im not sure but I don't think the step where we previously changed the data types before importing actually did anything.
Next step was to locate the File. I just found it under the My PC section.
Then I opened it and went through the process of importing using the import wizard which was pretty clear.
The issue that I kept running into was that as soon as I tried to select a primary key based on the documentation on the FDA website everything would fail.
I kept asking Hailey so many questions about this and what I was doing wrong.
Hailey solved this issue and showed me how to get around it by just not selecting primary keys if the imports didn't work with them. They didn't seem to end up being very important.
I repeated the import for each file until I got to ApplicationDocs and kept running into an issue importing.
Opened the ApplicationDocs.txt file in notepad to see what the issue might be.
Found lines under the ApplicationDocTitle that were text instead of the numbers that the rest of the data was. The lines which are on 737 along with others say "This Former NDA Was Deemed To Be a BLA on March 23, 2020". The fix was just to change the field from integer to long text.
The rest of the files imported with no issues, other than products which imported with a table called Product_ImportErrors with single error from row 46648.

Dr. Dionisio's Whiteboard Guide

Char -> Short text and set the length in design mode

varchar -> Long Text

text -> Long Text

int -> number , set size in design mode

Not Null -> Required = yes

Null -> Required = no

SQL Queries

Using the SQL view in Access, I created queries to answer the questions for the assignment.
The first one that I answered was question 1).
I actually didn't see Dr. Dionisio's answer because I was importing so when I got home, I logged onto the LMU Virtual Desktop and created this by referencing an article from 2020 on freecodecamp
I followed the examples on the site and just switched the terms and number of terms in the examples to fit my data set.
I did this same process for each of the following examples until I reached 5.
Upon reaching 5 Hailey let me know that 5 wasn't possible for us as well as 6.
For #7 which I had trouble understanding because of the weird relationship thing. What matters here is following this point I tried to do it in a way Hailey showed me but I didn't understand it so I just copied Hailey's code for #7. It worked.
Number 8 was just a process of trying different combinations of the reference information in Microsoft Access SQL reference

Results

1) What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?

Lovenox
Velcade
APIDRA
ENOXIPARIN SODIUM
ACTEMRA
BORTEZOMIB
WEZLANA

SELECT DrugName FROM Products WHERE Form = 'INJECTABLE;INTRAVENOUS, SUBCUTANEOUS';

2) What are the names of the drug products whose active ingredient (activeingredient) is ATROPINE?

ATROPEN
ATROPINE

SELECT DrugName FROM Products WHERE ActiveIngredient LIKE 'ATROPINE';

3) In what forms and strengths can the drug product named BENADRYL be administered?

SELECT Products.Form, Products.Strength FROM Products WHERE (((Products.[DrugName])='BENADRYL'));

4) Which drug products have a name ending in ESTROL?

DIETHYLSTILBESTROL
DIENESTROL
QUINESTROL

SELECT ActiveIngredient FROM Products WHERE ActiveIngredient LIKE "*ESTROL";

5) Produce a table listing all of the known values for the therapeutic_potential column in the application table and how many application records there are of each. (Side note: The therapeutic_potential codes are explained in the ReviewClass_Lookup.txt file, in case you’re interested.)

This question was not possible to answer.

6) Produce a table listing all of the known values for the chemical_type column in the application table and how many application records there are of each. (Side note: The chemical_type codes are explained in the ChemTypeLookup.txt file, in case you’re interested.)

This question was not possible to answer.

7) What are the names of the drug products that are sponsored (sponsorname column) by MERCK?

ALDOCLOR-150
ALDOCLOR-250
ALDOMET
ALDORIL 15
ALDORIL 25
ALDORIL D30
ALDORIL D50
ALPHAREDISOL
AMINOHIPPURATE SODIUM
ARAMINE
BENEMID
BLOCADREN
CANCIDAS
CHIBROXIN
CLINORIL
COGENTIN
COLBENEMID
CORTONE
CYCLAINE
DECADERM
DECADRON
DECADRON W/ XYLOCAINE
DECADRON-LA
DECASPRAY
DIUPRES-250
DIUPRES-500
DOLOBID
ELSPAR
EMEND
FLOROPRYL
FOSAMAX HUMORSOL
HYDELTRASOL
HYDELTRA-TBA
HYDROCORTONE HYDRODIURIL
HYDROPRES 25
HYDROPRES 50
LERITINE
MANNITOL 25%
MEFOXIN IN DEXTROSE 5% IN PLASTIC CONTAINER MEFOXIN IN SODIUM CHLORIDE 0.99 IN PLASTIC CONTAINER
MEVACOR
MODURETIC 5-50
NEODECADRON
NEO-HYDELTRASOL
NOROXIN
PEPCID
PEPCID PRESERVATIVE FREE
PEPCID RPD
PERIACTIN
PRIMAXIN
PRINIVIL
PRINZIDE
REDISOL
TIAMATE
TIMOLIDE 10-25
VIONK

8) Which sponsorname companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE?

WATSON LABS
DR REDDYS LABS SA
NOSTRUM LABS

SELECT DISTINCT Applications.SponsorName FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE Products.ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' AND Applications.SponsorName LIKE '*Labs*';

As a note, DISTINCT did not do anything different but I read it was supposed to only show single copies

9) Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database (Drugs@FDA?)

I would not have been able to, it would have taken months and multiple people to go through all the thousands of entries and compare.

Scientific Conclusion

Creating and searching through the data from the FDA gave me a lot of new knowledge. It opened my eyes to the process of searching, compiling, and transferring large sets of data using Access. There were some challenges through the process, but I was able to overcome them with a little bit of patience and a lot of Hailey. In the end I was able to extract the data I needed from the FDA Drug Database to answer the questions. It helped me build both experience in this and my technical skills. It also helped me realize how monumental data is in our world and just why people are always talking about data like it is a living breathing thing. Being able to analyze data like this when there are so many entries is both amazing and really important to making discoveries that we would otherwise not be able to make.

Data and Files

Access File for the Drug Database. Andrew's Access Database

References

Hailey was indispensable to this project. She helped explain so many things. Especially the importing, #7 & #8, and let me know about 5 and 6 being impossible.
Dr. Dionisio also provided the guide for importing that I shared earlier.
I also used the Week 8 page to follow as I did this assignment
I added to Hailey's bibliography since we had the same base blueprint.
Unless otherwise stated, all work here is my own. Asandle1 (talk)