HCA 416 Building Queries Using Healthcare Data Worksheet

California State University HCA 416 Building Queries Using Healthcare Data Worksheet

HCA 416 Building Queries Using Healthcare Data Worksheet

Import the excel spreadsheet HCA 416_LAB#1 into access, it is provided down below.

Use the query builder tool to create queries to answer the following questions. HCA 416 Building Queries Using Healthcare Data Worksheet

ORDER CUSTOM HCA 416 PAPERS HERE

Once you create all the queries, save the database and submit to dropbox folder Lab #1 along with a word document answering the following questions.*****MAKE SURE TO COMPLETE THE ANSWERS THOROUGHLY USING THE QUERIES YOU CREATED FROM MICROSOFT ACCESS.

  1. What is the TOTAL membership, TOTAL discharge days, and TOTAL discharges for COMMERCIAL and MEDICARE PLANS?
  2. What is the total membership, total discharge days, and total discharges for Commercial and Medicare Plans in CALIFORNIA?
  3. What is the average ‘Average Length of Stay (ALOS)’ for Commercial Plans and for Medicare Plans?
  4. What is the average ‘Average Length of Stay (ALOS)’ for California Commercial Plans and for Medicare Plans in California?
    **Note: ALOS = (total discharge days)/(total discharges)
  5. Which provider group has the lowest Medicare membership and which provider group has the highest Commercial membership? [Note: Same provider groups are in more than one state. So you will have to add those up before you can find out who has the highest/lowest memberships.]
  6. Clear answers to the above questions in a word document and the access database with CLEARLY named queries and table.
.
Plan State Provider Group Membership Discharges Discharge Days
Commercial CA Provider Group  A                     140,000           11,200                   35,840
Medicare CA Provider Group  A                       40,000             4,000                   20,800
Commercial OR Provider Group  A                       55,000             3,850                     8,470
Medicare OR Provider Group  A                       40,000             3,600                   15,120
Commercial WA Provider Group  A                     100,000             9,500                   39,900
Medicare WA Provider Group  A                       80,000             9,200                   57,040
Commercial CA Provider Group  B                     200,000           15,000                   49,500
Medicare CA Provider Group  B                     100,000           12,000                   63,600
Commercial OR Provider Group  B                       45,000             2,925                     6,728
Medicare OR Provider Group  B                       30,000             3,300                   14,190
Commercial WA Provider Group  B                       80,000             7,200                   30,960
Medicare WA Provider Group  B                       60,000             8,100                   51,030
Commercial CA Provider Group  C                     150,000           10,500                   21,000
Medicare CA Provider Group  C                       50,000             7,000                   28,000
Commercial OR Provider Group  C                       40,000             2,400                     2,400
Medicare OR Provider Group  C                       25,000             3,250                     9,750
Commercial WA Provider Group  C                     110,000             9,350                   28,050
Medicare WA Provider Group  C                       90,000           13,950                   69,750
Commercial CA Provider Group  D                     250,000           21,500                   64,500
Medicare CA Provider Group  D                     150,000           19,500                   97,500
Commercial OR Provider Group  D                       35,000             2,660                     5,320
Medicare OR Provider Group  D                       20,000             2,400                     9,600
Commercial WA Provider Group  D                       70,000             7,070                   28,280
Medicare WA Provider Group  D                       50,000             7,250                   43,500
Commercial CA Provider Group  E                     145,000           16,200                   40,840
Medicare CA Provider Group  E                       45,000             9,000                   25,800
Commercial OR Provider Group  E                       60,000             8,850                   13,470
Medicare OR Provider Group  E                       45,000             8,600                   20,120
Commercial WA Provider Group  E                     105,000           14,500                   44,900
Medicare WA Provider Group  E                       85,000           14,200                   62,040
Commercial CA Provider Group  F                     205,000           20,000                   54,500
Medicare CA Provider Group  F                     105,000           17,000                   68,600
Commercial OR Provider Group  F                       50,000             7,925                   11,728
Medicare OR Provider Group  F                       35,000             8,300                   19,190
Commercial WA Provider Group  F                       85,000           12,200                   35,960
Medicare WA Provider Group  F                       65,000           13,100                   56,030
Commercial CA Provider Group  G                     155,000           15,500                   26,000
Medicare CA Provider Group  G                       55,000           12,000                   33,000
Commercial OR Provider Group  G                       45,000             7,400                     7,400
Medicare OR Provider Group  G                       30,000             8,250                   14,750
Commercial WA Provider Group  G                     115,000           14,350                   33,050
Medicare WA Provider Group  G                       95,000           18,950                   74,750
Commercial CA Provider Group  H                     255,000           26,500                   69,500
Medicare CA Provider Group  H                     155,000           24,500                 102,500
Commercial OR Provider Group  H                       40,000             7,660                   10,320
Medicare OR Provider Group  H                       25,000             7,400                   14,600
Commercial WA Provider Group  H                       75,000           12,070                   33,280
Medicare WA Provider Group  H                       55,000           12,250                   48,500
Commercial CA Provider Group  I                     150,000           21,200                   45,840
Medicare CA Provider Group  I                       50,000           14,000                   30,800
Commercial OR Provider Group  I                       65,000           13,850                   18,470
Medicare OR Provider Group  I                       50,000           13,600                   25,120
Commercial WA Provider Group  I                     110,000           19,500                   49,900
Medicare WA Provider Group  I                       90,000           19,200                   67,040
Commercial CA Provider Group  J                     210,000           25,000                   59,500
Medicare CA Provider Group  J                     110,000           22,000                   73,600
Commercial OR Provider Group  J                       55,000           12,925                   16,728
Medicare OR Provider Group  J                       40,000           13,300                   24,190
Commercial WA Provider Group  J                       90,000           17,200                   40,960
Medicare WA Provider Group  J                       70,000           18,100                   61,030
Commercial CA Provider Group  K                     160,000           20,500                   31,000
Medicare CA Provider Group  K                       60,000           17,000                   38,000
Commercial OR Provider Group  K                       50,000           12,400                   12,400
Medicare OR Provider Group  K                       35,000           13,250                   19,750
Commercial WA Provider Group  K                     120,000           19,350                   38,050
Medicare WA Provider Group  K                     100,000           23,950                   79,750
Commercial CA Provider Group  L                     260,000           31,500                   74,500
Medicare CA Provider Group  L                     160,000           29,500                 107,500
Commercial OR Provider Group  L                       45,000           12,660                   15,320
Medicare OR Provider Group  L                       30,000           12,400                   19,600
Commercial WA Provider Group  L                       80,000           17,070                   38,280
Medicare WA Provider Group  L                       60,000           17,250                   53,500

HCA 416 – Lab 1 (30pts)

Building Queries Using Healthcare Data

Import the excel spreadsheet HCA 416_Week 7 Lab1_Data.xls from BeachBoard. Use the query builder tool to create queries to answer the following questions. HCA 416 Building Queries Using Healthcare Data Worksheet

Once you create all the queries, save the database and submit to Dropbox along with a word document answering the following questions.

Question Answer Value Grade
1 What is the TOTAL membership, TOTAL discharge days, and TOTAL discharges for Commercial and Medicare Plans? ·     Total membership Commercial =

·     Total membership Medicare =

·     Total discharge days Commercial =

·     Total discharge days Medicare 1=

·     Total Discharges Commercial =

Total Discharges Medicare =

7 pts
2 What is the total membership, total discharge days, and total discharges for Commercial and Medicare Plans in CALIFORNIA? ·     Total CA membership Commercial =

·     Total CA membership Medicare =

·     Total CA discharge days Commercial =

·     Total CA discharge days Medicare =

·     Total CA Discharges Commercial =

Total CA Discharges Medicare =

3 pts
3 What is the average ‘Average Length of Stay (ALOS)’ for Commercial Plans and for Medicare Plans?  Note: ALOS = (total discharge days)/(total discharges) ·     Commercial=

·     Medicare=

7 pts
4 What is the average ‘Average Length of Stay (ALOS)’ for California Commercial Plans and for Medicare Plans in California? ·     Commercial=

·     Medicare=

3 pts
5 Which provider group has the lowest Medicare membership and which provider group has the highest Commercial membership? [Note: Same provider groups are in more than one state.  So you will have to add those up before you can find out who has the highest/lowest memberships.] ·     Lowest Medicare membership=

·     Highest Commercial membership=

10 pts
TOTAL 30  

 SAVE YOUR DATABASE AND WORD FILE AND SUBMIT TO DROPBOX BY DUE DATE.  HCA 416 Building Queries Using Healthcare Data Worksheet