Difference between revisions of "Knguye66 Week 4"

From LMU BioDB 2019
Jump to navigation Jump to search
(add questions and format)
 
(add template and edit again)
 
(50 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== The FDA Drug Database ==
 
== The FDA Drug Database ==
−
The purpose: what was the purpose of your investigations?
+
The purpose of this week's assignment was to learn how to decompress and compress files, as well as, uploading, formatting, and creating tables and querys via Microsoft Access 2016.
−
Your methods: what did you actually do? Give a step by step account for Tasks 1-4 listed above.
 
−
There should be enough detail provided so that you or another person could re-do it based solely on your notebook.
 
−
You may copy protocol instructions on your page and modify them as to what you actually did, as long as you provide appropriate attribution.
 
−
Take advantage of the electronic nature of the notebook by providing screenshots, links to web pages, links to data, etc.
 
−
Your results: the answers to the questions below, plus any other results you gathered.
 
−
Usually it makes sense to embed your answers to the questions/results in the "methods" in the order in which you obtained them.
 
−
A scientific conclusion: what was your main finding for today's project? Did you fulfill the purpose? Why or why not?
 
  
−
== Questions ==
+
== LinkedIn Learning ==
−
# What are the names of the drug products that are administered in the form INJECTABLE;INTRAVENOUS, SUBCUTANEOUS?
+
 
−
# What are the names of the drug products whose active ingredient (activeingred) is ATROPINE?
+
[[File:Knguye66_CertificateOfCompletion_Learning Access 2016.jpg|600px|Learning Access 2016]]
−
# In what forms and dosages can the drug product named BENADRYL be administered?
+
 
−
# Which drug products have a name ending in ESTROL?
+
== Microsoft Access FDA database ==
−
# 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.)
+
 
−
# 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.)
+
FDA Database: [[File:Knguye66_FDA_Databases.zip]]
−
# What are the names of the drug products that are sponsored (sponsor applicant column) by MERCK?
+
 
−
# Which sponsor applicant companies have the text LABS in their names and have products whose active ingredients (activeingred) include both ASPIRIN and CAFFEINE?
+
== Methods to Create the Database ==
−
# Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database (Drugs@FDA?)
+
 
 +
=== How to add files (and tables) to database: ===
 +
# Change Settings in Google Chrome
 +
# Advanced --> Download (T:/) & (check) ask where to download
 +
# Download FDA database
 +
# Unzip file by right clicking (.7zip) --> extract here
 +
# Open Microsoft Access
 +
# Save file (make it personal)
 +
# Click text file
 +
# Import text wizard: delimited > tab (check: first row contains field names) > field options > have a primary key (our file already has a primary key: click no or choose primary key as field 1)
 +
#* Important to Note: for each file uploaded, the field options portion is different. Click the Data Type drop-down menu and choose the preferred option:
 +
#** Action Types_Lookup --> Long integer
 +
#** Applicationdocs --> Change "ApplicationDocs" field and last column from "Date and Time" to Short text
 +
#** Products --> N/A (use default setting)
 +
 
 +
=== How to Create a Query ===
 +
# To add query (called a "select query"), go under Design > Query Design
 +
# Click and drag all three Tables
 +
# Find what's similar in all three categories, then Click and drag to create an arrow
 +
#* To create a field/graph: drag submission files down
 +
 
 +
=== Different functions learned on Microsoft Access ===
 +
 
 +
* Design > View > SQL (view)
 +
* Click "!" to run data
 +
 
 +
=== How to Save and Compress files ===
 +
* Go to (T:/) drive and right click on Microsoft Access FDA database file
 +
* Drag mouse over .7ip > Click "Add to Archive..." > Change "Archive Format" to zip file
 +
 
 +
== Tables & Query ==
 +
 
 +
* Tables:
 +
** ActionTypes_Lookup [[File:Knguye66_FDA_ActionTypes_Lookup.txt]]
 +
** ApplicationDocs [[File:Knguye66_FDA_ApplicationDocs.txt]]
 +
** Products [[File:Knguye66_FDA_Products.txt]]
 +
 
 +
* Query:
 +
[[File:Knguye66_FDAdatabase_Query1.png|600px|]]
 +
 
 +
== References ==
 +
 
 +
* LinkedIn Learning: https://www.linkedin.com/learning/login-ent
 +
* Microsoft Access 2016
 +
* FDA Drugs Data Files: https://www.fda.gov/drugs/drug-approvals-and-databases/drugsfda-data-files
 +
 
 +
== Acknowledgements ==
 +
 
 +
This week I worked with Mihir Samdarshi [[User:Msamdars]] on the FDA Databases assignment, and received help from [[User:Ntesfaio]], as well as instructions from Dr. Dahlquist [[User:Kdahlquist]].
 +
 
 +
Except for what is noted above, this individual journal entry was completed by me and not copied from another source.
 +
[[User:Knguye66|Knguye66]] ([[User talk:Knguye66|talk]]) 20:17, 25 September 2019 (PDT)[[Template:knguye66]]

Latest revision as of 19:20, 25 September 2019

The FDA Drug Database

The purpose of this week's assignment was to learn how to decompress and compress files, as well as, uploading, formatting, and creating tables and querys via Microsoft Access 2016.

LinkedIn Learning

Learning Access 2016

Microsoft Access FDA database

FDA Database: File:Knguye66 FDA Databases.zip

Methods to Create the Database

How to add files (and tables) to database:

  1. Change Settings in Google Chrome
  2. Advanced --> Download (T:/) & (check) ask where to download
  3. Download FDA database
  4. Unzip file by right clicking (.7zip) --> extract here
  5. Open Microsoft Access
  6. Save file (make it personal)
  7. Click text file
  8. Import text wizard: delimited > tab (check: first row contains field names) > field options > have a primary key (our file already has a primary key: click no or choose primary key as field 1)
    • Important to Note: for each file uploaded, the field options portion is different. Click the Data Type drop-down menu and choose the preferred option:
      • Action Types_Lookup --> Long integer
      • Applicationdocs --> Change "ApplicationDocs" field and last column from "Date and Time" to Short text
      • Products --> N/A (use default setting)

How to Create a Query

  1. To add query (called a "select query"), go under Design > Query Design
  2. Click and drag all three Tables
  3. Find what's similar in all three categories, then Click and drag to create an arrow
    • To create a field/graph: drag submission files down

Different functions learned on Microsoft Access

  • Design > View > SQL (view)
  • Click "!" to run data

How to Save and Compress files

  • Go to (T:/) drive and right click on Microsoft Access FDA database file
  • Drag mouse over .7ip > Click "Add to Archive..." > Change "Archive Format" to zip file

Tables & Query

  • Query:

Knguye66 FDAdatabase Query1.png

References

Acknowledgements

This week I worked with Mihir Samdarshi User:Msamdars on the FDA Databases assignment, and received help from User:Ntesfaio, as well as instructions from Dr. Dahlquist User:Kdahlquist.

Except for what is noted above, this individual journal entry was completed by me and not copied from another source. Knguye66 (talk) 20:17, 25 September 2019 (PDT)Template:knguye66

Retrieved from "https://xmlpipedb.lmucs.io/biodb/fall2019/index.php?title=Knguye66_Week_4&oldid=3076"