On-Campus Health is the infirmary located on the campus of the University of Denver. Recordkeep- ing at the infirmary?s pharmacy, although meticulous and professional, is inefficient. Maintaining the recordkeeping using mostly manual systems is becoming more costly as additional people are hired to meet stricter industry regulations regarding the Health Insurance Portability and Account- ability Act (HIPAA) and because of state regulations that affect the sale, storage, and dispensing of prescription drugs. Although On-Campus Health succeeded in automating some of the data management for the pharmacy in an Excel spreadsheet, a more substantial change is needed to properly maintain and store data.

Students who use the infirmary can request prescriptions, either by presenting a written order from a doctor or by 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 student?s name, the student?s health plan, and the prescribing doctor. Use the data that On-Campus Health has provided you in the file T6_CampusHealth_Data.xls to complete the following:

1. Create a Microsoft Access database.

2. Create the tables, fields, data types, primary key(s), and the like for the database.

3. Create the relationship(s) needed between the tables.

4. Populate the database with the data provided in the T6_CampusHealth_Data.xls file.

5. 

6. 

7. 

Student ID First Name Last Name 1 Ted 4 Steven 8 Sonia 10 Jonathan 11 Paula 14 Mary 15 Gina 17 Maria 18 Anders 19 Dusty 20 Kevin 21 Cesar 22 Jose 23 Jennifer 24 Kimberley Phone (970) 644-1214 (435) 122-4545 (928) 551-5547 (928) 551-5547 (505) 744-1889 (435) 444-1233 (970) 514-3212 (970) 223-4157 (505) 499-6541 (435) 693-1212 (435) 477-8989 Romero (970) 631-2222 Hernandez (505) 498-6478 Holiday (928) 888-3545 Smith (928) 647-2477 Springfield Bacon Williams Nuggent Cardenas Cardenas Hargus Ambrose Mercado Gabel Kroll Date of Birth Gender 12/11/1984 M 10/12/1966 M 4/12/1988 F 8/22/2004 M 6/11/1970 F 4/15/1980 F 6/17/1979 F 8/12/1980 F 9/11/1984 M 4/5/1990 M 6/22/1990 M 1/14/1987 M 6/30/1987 F 11/21/1989 F 1/16/1989 F Health Plan WeCare Health Salt Lake Community Health Medicare Southern Rocky Mountains Health Plan Great American Southwest Health Santa Ana Health WeCare Health Salt Lake Community Health Medicare Southern Rocky Mountains Health Plan Great American Southwest Health Santa Ana Health WeCare Health Salt Lake Community Health Medicare Allergies Penicillin Penicillin Codeine derivatives, merperidine Peanuts Morphine, morphine derivatives Penicillin Penicillin, cephalosporins Peanuts Shellfish Morphine First Name Last Name David Patricia Gene Roxanna Robert Sara James Allen Freitag Gramann Jurski Trevino Westenberg Wilson Zamarron Antonio Joshua Warric Gunther Loke Phone (505) 884-0542 (928) 888-4178 (928) 488-1741 (435) 887-1818 429-7475 (970) (435) 321-4789 (435) 189-9874 (435) 887-1818 (505) 884-0542 (928) 449-4174 UPN 102 Ampicillin 121 Tolbutamide 224 Avatocin 247 Acebutolol hydrochloride 256 Dseurton Name 289 Levothyroxine 311 Dyotex 366 Phalastat 398 Clonazepam 412 Epronix 444 Syocil 452 Diazapam 467 Glimepiride 523 Xeroflarol 524 Cefixime 566 Quentix 587 Haloperidol 622 Tiron 642 Montelukast sodium 644 Hyometadol 654 Warfarin Sodium 711 Nvalax 732 Albuterol Sulfate 741 Almotriptan 828 Myobuterol 852 Oxaprozin 878 Didanosine 932 Tvalaxec 972 Rivastigmine tartrate 987 Rizatriptan Benzoate Generic? Description TRUE Antibiotic FALSE Bacterial infections FALSE Allergies FALSE Arthritis TRUE High blood pressure TRUE Thyroid disorders FALSE Tonsillitis FALSE Allergies FALSE Epiliepsy FALSE Pain TRUE Diabetes TRUE Anxiety TRUE Diabetes TRUE Acid reflux TRUE Antihistamine TRUE High blood pressure FALSE Diuretic FALSE Beta blocker TRUE Acne FALSE Asthma TRUE Bronchitis TRUE Depression TRUE Asthma TRUE Conjunctivitis TRUE Antibiotic TRUE Anti-inflammatory TRUE Sinus infection TRUE Antihistamine TRUE ADHD FALSE Pain Unit Pill Pill Pill Pill Pill Pill Bottle Bottle Pill Pill Pill Pill Pill Bottle Pill Pill Pill Pill Pill Bottle Pill Pill Pill Pill Bottle Pill Pill Bottle Pill Pill Dosage Dosage Form 250 mg 2 mcg 100 mg 400 mg 175 25 2 1 4 500 120 5 2 1 400 50 6 150 10 2 4 200 2 6.25 1 1200 200 2 4.4 6 mg mg tsp tsp mcg mg mg mg mg tsp mg mg mcg mcg mcg tsp mg mg mg mg tsp mg mg tsp mg mg Cost $0.75 $0.24 $0.65 $0.55 $0.60 $0.70 $0.25 $0.75 $0.65 $0.85 $0.45 $0.45 $0.25 $0.50 $0.95 $0.65 $0.65 $0.30 $0.30 $0.14 Price $1.45 $0.90 $1.40 $1.10 $0.65 $0.50 $0.40 $0.47 $1.20 $1.40 $1.05 $1.60 $1.20 $1.50 $1.10 $0.50 $0.70 $1.30 $0.75 $1.30 $0.32 $0.90 $1.12 $0.90 $1.05 $1.60 $1.25 $1.35 $1.40 $0.90 $0.95 $0.87 $0.55 $1.60 $0.60 $1.25 $1.12 $1.00 $1.05 $1.10 Plan Name WeCare Health Salt Lake Community Health Medicare Southern Rocky Mountains Health Plan Great American Southwest Health Santa Ana Health Address 4545 Supersaw Way 6565 North Sunnyside Ave. 7500 Security Blvd. 54412 Osceola Blvd. 422 North Hewitt Street 32111 East Tamaya Parkway City Phoenix Salt Lake City Baltimore Aurora Provo Santa Fe State Zip Phone |86547 (928) 477-9899 84534 (435) 741-4545 MD 21244 (866) 411-1414 81327 (970) 540-8742 84510 (435) 177-9184 NM 87499 (505) 654-2425 UT 25985 Prescription ID 1 2 2 3 4 5 5 6 6 7 8 B 9 9 10 10 11 12 13 14 15 15 16 16 17 17 18 19 19 20 21 21 21 22 23 23 24 25 25 26 26 27 Refill Date 12/11/2007 4/14/2008 6/1/2008 7/1/2008 6/21/2008 1/12/2008 3/11/2008 6/3/2008 8/25/2008 5/14/2008 3/18/2008 6/1/2006 4/14/2006 10/11/2008 3/11/2008 6/21/2006 11/1/2007 1/3/2006 9/29/2007 6/10/2007 4/22/2007 11/13/2007 11/16/2007 2/14/2006 8/1/2007 2/21/2006 12/23/2007 9/12/2007 12/14/2007 1/16/2006 12/23/2007 2/14/2006 5/16/2006 6/23/2007 6/11/2006 9/18/2008 2/15/2008 9/1/2007 11/30/2007 12/31/2007 2/1/2008 3/14/2006