Kmill104 Week 8 - Revision history

Kmill104: /* Methods */ fixing typo again again

2024-03-14T06:50:39Z

‎Methods: fixing typo again again

Kmill104: /* Methods */ fixing typo again

2024-03-14T06:46:58Z

‎Methods: fixing typo again

Kmill104: /* Methods */ adding another detail

2024-03-14T06:42:49Z

‎Methods: adding another detail

Kmill104: /* Methods */ fixing typo

2024-03-14T06:32:06Z

‎Methods: fixing typo

Kmill104: moving sections

2024-03-14T06:00:45Z

moving sections

Kmill104: /* Acknowledgements */ adding talk signature

2024-03-14T05:59:54Z

‎Acknowledgements: adding talk signature

Kmill104: /* References */

2024-03-14T05:59:29Z

‎References

Kmill104: /* Results */ adding search function link

2024-03-14T05:58:53Z

‎Results: adding search function link

Kmill104: /* Methods */ fixing link names

2024-03-14T05:57:43Z

‎Methods: fixing link names

Kmill104: /* Data and Files */ fixing formatting

2024-03-14T05:56:13Z

‎Data and Files: fixing formatting

@@ Line 17: / Line 17: @@
 #**Submissions table as website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #**In Access, each column that has data type char on the website I then assigned the Short Text data type. For the others, varchar was assigned as Long Text, int as Integer or Long Integer, text as Long Text, and datetime as Date with Time.
-#***For the Submissions and ApplicationDocs files, I ended up with import errors when downloading the datetime columns. Because these columns were listed as NULL on the website, I then chose to not import them and I had no more import errors.
+#***For the Submissions and ApplicationDocs files, I ended up with import errors when downloading some of the columns, specifically the datetime columns. Because these columns were listed as NULL on the website, I then chose to not import them and I had no more import errors.
 #*I also attempted to match the table's primary keys to what is seen on the website, but Access often stated that the column could not be a primary key as it contained duplicates or was missing data in some rows. In these cases, I chose there to be no primary key.
 #After downloading each file and configuring the fields in its table, I navigated to the Database Tools tab and then clicked on Relationships.

@@ Line 17: / Line 17: @@
 #**Submissions table as website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #**In Access, each column that has data type char on the website I then assigned the Short Text data type. For the others, varchar was assigned as Long Text, int as Integer or Long Integer, text as Long Text, and datetime as Date with Time.
-#***For the Submissions and ApplicationDocs files, I ended up with import errors when downloading the datetime columns. Because both columns were listed as NULL on the website, I then chose to not import them and I had no more import errors.
+#***For the Submissions and ApplicationDocs files, I ended up with import errors when downloading the datetime columns. Because these columns were listed as NULL on the website, I then chose to not import them and I had no more import errors.
 #*I also attempted to match the table's primary keys to what is seen on the website, but Access often stated that the column could not be a primary key as it contained duplicates or was missing data in some rows. In these cases, I chose there to be no primary key.
 #After downloading each file and configuring the fields in its table, I navigated to the Database Tools tab and then clicked on Relationships.

@@ Line 17: / Line 17: @@
 #**Submissions table as website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #**In Access, each column that has data type char on the website I then assigned the Short Text data type. For the others, varchar was assigned as Long Text, int as Integer or Long Integer, text as Long Text, and datetime as Date with Time.
 #*I also attempted to match the table's primary keys to what is seen on the website, but Access often stated that the column could not be a primary key as it contained duplicates or was missing data in some rows. In these cases, I chose there to be no primary key.
 #After downloading each file and configuring the fields in its table, I navigated to the Database Tools tab and then clicked on Relationships.

@@ Line 15: / Line 15: @@
 #*For each field type, I kept the names the same but, if needed, I changed the data type to match what is seen on the FDA website.
 #**On the website, each table column is assigned some data type, with the five options for data types being char, varchar, int, text, and datetime.
-#**Submissions table for website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
+#**Submissions table as website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #**In Access, each column that has data type char on the website I then assigned the Short Text data type. For the others, varchar was assigned as Long Text, int as Integer or Long Integer, text as Long Text, and datetime as Date with Time.
 #*I also attempted to match the table's primary keys to what is seen on the website, but Access often stated that the column could not be a primary key as it contained duplicates or was missing data in some rows. In these cases, I chose there to be no primary key.

@@ Line 51: / Line 51: @@
 ==Scientific Conclusion==
 This project taught me how to download data that is stored in files, as well as how to convert it into tables for one's own database. I also learned how to define relationships between these tables, as well as how to use the query function in Microsoft Access. I did fulfill the purpose, as I learned how to download data sets and configure them, and I got some practice with performing queries.
 ==Acknowledgements==
@@ Line 67: / Line 82: @@
 {{Template:Kmill104}}

← Older revision		Revision as of 05:59, 14 March 2024
Line 56:		Line 56:

	Except for what is noted above, this individual journal entry was completed by me and not copied from another source.		Except for what is noted above, this individual journal entry was completed by me and not copied from another source.
		+
		+	[[User:Kmill104\|Kmill104]] ([[User talk:Kmill104\|talk]]) 22:59, 13 March 2024 (PDT)

	==References==		==References==

← Older revision		Revision as of 05:59, 14 March 2024
Line 58:		Line 58:

	==References==		==References==
		+	*Access SQL: basic concepts, vocabulary, and syntax—Microsoft Support. (n.d.). Retrieved March 13, 2024, from https://support.microsoft.com/en-us/office/access-sql-basic-concepts-vocabulary-and-syntax-444d0303-cde1-424e-9a74-e8dc3e460671?ui=en-us&rs=en-us&ad=us
		+	*Drugs@FDA Data Files. (n.d.). FDA; FDA. Retrieved March 13, 2024, from https://www.fda.gov/drugs/drug-approvals-and-databases/drugsfda-data-files
		+	*Drugs@FDA: FDA-Approved Drugs. (n.d.). Retrieved March 13, 2024, from https://www.accessdata.fda.gov/scripts/cder/daf/index.cfm
		+	*Ford, P. (2015, June 11). What Is Code? Bloomberg. https://www.bloomberg.com/graphics/2015-paul-ford-what-is-code/
		+	*LMU BioDB 2024. (2024). Week 8. Retrieved March 13, 2024, from https://xmlpipedb.cs.lmu.edu/biodb/spring2024/index.php/Week_8
		+
		+	{{Template:Kmill104}}

	==Data and Files==		==Data and Files==

@@ Line 47: / Line 47: @@
 #*Sponsor Applicant Companies: [[Media:MillerQuery8.xlsx | Question 8 Answers]]
 #**SELECT SponsorName, ActiveIngredient FROM Applications RIGHT JOIN Products ON Applications.ApplNo = Products.ApplNo WHERE ActiveIngredient LIKE '*ASPIRIN*CAFFEINE*' AND SponsorName LIKE '*LABS*';
-#Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database (Drugs@FDA?) No, the web interface only allows the user to search by drug name, active ingredient, or application number. For many of the questions, we would not be able to find the specific answers for what we are searching for. For example, in Question 1, the FDA website does not have an option to search by form. Another example is in Question 9, where we have two requirements for our answers, which is that the name contains LABS and the active ingredients include both aspirin and caffeine. The FDA website cannot search by company name, and it can only search by one active ingredient at a time, so finding the answers for Question 9 is not possible.
+#Also answer, would you have been able to answer these questions using the web interface that the FDA provides to this database (Drugs@FDA?) No, the web interface [https://www.accessdata.fda.gov/scripts/cder/daf/index.cfm search function] only allows the user to search by drug name, active ingredient, or application number. For many of the questions, we would not be able to find the specific answers for what we are searching for. For example, in Question 1, the FDA website does not have an option to search by form. Another example is in Question 9, where we have two requirements for our answers, which is that the name contains LABS and the active ingredients include both aspirin and caffeine. The FDA website cannot search by company name, and it can only search by one active ingredient at a time, so finding the answers for Question 9 is not possible.
 ==Scientific Conclusion==

@@ Line 3: / Line 3: @@
 ==Methods==
-#My first step was to click on the [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database] link on the Week 8 page.
+#My first step was to click on the [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files] link on the Week 8 page.
 #*On the database website, there is a file containing the FDA Drug Database's data sets.
-#**Screenshot of website: [[File:DBScreenshot.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database]
+#**Screenshot of website: [[File:DBScreenshot.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #*Clicking this file link then downloaded a zip folder, which I had to decompress in FileExplorer.
-#*Within the folder, there were 11 text documents for each table in this image: [[File:DataBTables.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database]
+#*Within the folder, there were 11 text documents for each table in this image: [[File:DataBTables.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #I then created my own Microsoft Access database, and navigated to the External Data tab in the toolbar.
 #*From there, I was able to click on New Data Source, which gave me the option to choose a text file.
@@ Line 15: / Line 15: @@
 #*For each field type, I kept the names the same but, if needed, I changed the data type to match what is seen on the FDA website.
 #**On the website, each table column is assigned some data type, with the five options for data types being char, varchar, int, text, and datetime.
-#**Submissions table for website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm FDA Drug Database]
+#**Submissions table for website example: [[File:DBSubmissions.png]] [http://www.fda.gov/Drugs/InformationOnDrugs/ucm079750.htm Drugs@FDA Data Files]
 #**In Access, each column that has data type char on the website I then assigned the Short Text data type. For the others, varchar was assigned as Long Text, int as Integer or Long Integer, text as Long Text, and datetime as Date with Time.
 #*I also attempted to match the table's primary keys to what is seen on the website, but Access often stated that the column could not be a primary key as it contained duplicates or was missing data in some rows. In these cases, I chose there to be no primary key.

@@ Line 61: / Line 61: @@
 ==Data and Files==
 [[Media:KmillerDataBase.zip | Katie Miller's Database]]
 [[Media:MillerQuery1.xlsx | Question 1 Answers]]
 [[Media:MillerQuery2.xlsx | Question 2 Answers]]
 [[Media:MillerQuery3.xlsx | Question 3 Answers]]
 [[Media:MillerQuery4.xlsx | Question 4 Answers]]
 [[Media:MillerQuery7.xlsx | Question 7 Answers]]
 [[Media:MillerQuery8.xlsx | Question 8 Answers]]