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. Source: Week 8 Assignment Page

The other purpose of this assignment was to get practice with database queries using Microsoft Access and its graphical interface. Source: Week 8 Assignment Page

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 [1]
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 except 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.