Create microsoft access database for doc-n-the-box pharmacy

Assignment Help Database Management System

Reference no: EM13751131

"Doc-n-the-Box" Pharmacy

In central Fairfax, VA, is a relatively new small medical practice lovingly known as the "Doc-n-the-Box." There is also a pharmacy affiliated with the medical practice. Although meticulous and professional, recordkeeping at the pharmacyis a bit inefficientsince it is done manually. Recordkeeping costs have been rising in recent months as additional people have been hired to meet stricter industry regulations regarding the Health Insurance Portability and Accountability Act (HIPAA) and because of state regulations that affect the sale, storage, and dispensing of prescription drugs. Although the Doc-n-the-Box has succeeded in automating some of the data management for the pharmacy in an Excel spreadsheet, a more substantial change, i.e., a move to Microsoft Access, is needed to properly maintain and store data. That is your job!

Individuals who use the Doc-n-the-Box can request prescriptions at the pharmacy, either by presenting a written order from a doctor or asking for a refill of an existing prescription. The pharmacist adds this request to the system by getting the required information to fill it, including information about the drug, the individual's name, the individual's health plan, and the prescribing doctor.Use the data that the Doc-n-the-Box has provided for you in the file "HW 5-Spring 2014 - 005-Doc-n-the-Box- Data.xls" (which can be found in Blackboard) to complete the following tasks/requirements. (Note that all of these must be successfully accomplished to be able to receive full credit on the assignment.)

Requirements:
1. Create a Microsoft Access database for the Doc-n-the-Box pharmacy.
2. Create the table(s), field(s), data type(s), primary key(s), etc. for the database.
3. Create the relationship(s) between/among the tables, as appropriate.
4. Populate the database with the data provided in the Excel file.

Once this is done, perform the following data-analysis tasks (i.e., queries) using Access. (Use the generic name for the queries when you save them, e.g., Query 1, Query 2, etc.):

Query 1 - Create an alphabetized list of the female patients who have used the Doc-n-the-Box. Your output should include the Last Name, First Name, Patient ID#, Phone, Date of Birth, and allergies, if any, for those individuals.

Query 2 - Create a list of patients and the drug(s) that has/have been prescribed for them. Your output should include the Patient's Last Name,Patient's First Name, Patient ID #, Drug Name, UPN, Instructions, Number of Refills Authorized, and Rx Expiration Date. Sort it by Rx Expiration date with the expiration furthest out on top (descending)

Query 3 - Create a list of the Health Plans with a list of individuals for each plan. Your output should include the Health Plan Name, Health Plan ID#, Patient Last Name, Patient First Name, and Patient ID#. This list should be alphabetized by Health Plan Name, and alphabetized by Patient Last Name within each Health Plan category.

Query 4 - Create a list of Prescriptions with 0 (zero) authorized refills. Your output should include UPN, Drug Name, # Refills Authorized, Patient Last Name, Patient First Name, Doctor's (Last) Name, Doctor ID#, and Doctor's Phone Number.

Query 5 - Create a list of Patients who received prescriptions between May 1, 2012 and December 31, 2012. Your output should be organized by Patient's Last Name, Patient's First Name, Prescription Date (in ascending order), Expiration Date, Patient ID#, Doctor's Last Name, Doctor ID#, Drug Name, and UPN.

Query 6 - Create a list of drugs that have been prescribed between January 1, 2012 and December 31, 2012. Your output should be organized by UPN (ascending), Plan ID#, Plan Name (ascending) and date of prescription.

Attachment:- data.xlsx

Reference no: EM13751131

Questions Cloud

Create a system wide clinical documentation program : Write a 2 page report (paper) on your plan to create and implement a system wide clinical documentation program at your organization

What is the rate of return : Suppose you purchase a coupon bond with 20 years to maturity for $1,000. Assume the bondpays coupons of $70 per year.a. Suppose after one year you must sell the bond to help pay for tuition. Assume further thatinterest rates have risen so that the pr..

Examine the current work system design : Examine the current work system design, and determine your organization's selected feasibility in the job design (i.e., technical, economic, behavioral). Assess key elements of the rationale in the work design competitive advantage.

How is interest rate that prevails in bond market determined : The demand curve for bonds would be shifted to the left by an increase in wealth - How is the interest rate that prevails in the bond market determined?

Create microsoft access database for doc-n-the-box pharmacy : Create a Microsoft Access database for the Doc-n-the-Box pharmacy. Create the table(s), field(s), data type(s), primary key(s), etc. for the database

Create a unique strategic plan for riordan manufacturing : Create a unique strategic plan for Riordan Manufacturing applying what you learned in the course discussions, readings, and your research

Bond enthalpy data to calculate dh of the reaction : A. Using the following mean bond enthalpy data to calculate dH of the reaction:.C3H8(g) + 5O2(g) = 3CO2(g) + 4H2O(g)Bond Mean enthalpy(kJ mol-1)C-C 348O=O 496

Effective financial reporting with arguments : Prepare a fact sheet convincing the board of the need for effective financial reporting with arguments about why it helps the organization's fundraising goals.

Determine if it was likely to create economies of scale : What question would you ask the developers of the Pee Power technology in order to determine if it was likely to create economies of scale?

User Account

All Pages