To do this assignment you'll need: The project instruction sheetThe Access database file (file will download as a Zip) When you've completed the assignment, zip the Access database and submit to the d

To do this assignment you'll need:

• The project instruction sheet
• The Access database file (file will download as a Zip)

When you've completed the assignment, zip the Access database and submit to the dropbox with the query designs in place. Do not just present a printout.

Submit your completed assignment by following the directions linked below. Please check the Course Calendar for specific due dates.

Submit these files as a single zipped “.zip” file to the drop box below. For more information on zipped files please click the Technical Requirements link on the Resources tab.

Note: There is an error within your assignment directions that asks for the "DRG" and "DRG code" in Query 1. Please disregard the "DRG" as it is not listed as a data field. However, you will still add the "DRG code" data field to the query.

The Project Instruction Sheet

Module 05 Assignment Developing Data Queries to Produce Information from Data Sets

Please open the Microsoft Access file Assignment_Mod05.accdb. The data may look similar to that for Module 01, but they have been converted into a database instead of an Excel Table.

1. Open the Copy of Patient Information table. 2. List the data fields that you see. 3. Now, open the query named “query on tetanus time”. This query computed the days and years from last tetanus immunization to hospital admission date. First view the query in the datasheet view. Next, click on view in the upper left corner and click on SQL view. You will see the input language needed to pull these fields and compute the time. There is also a red exclamation mark called “run”. Each time we want to re-run the query, click on that exclamation mark.

Why re-run a query? Whenever the underlying data are changed or updated, the query can be re-run to get the current results. This becomes a rapid way of updating results when dealing with thousands of records instead of a couple dozen.

Fortunately, using Access we do not need to generate much SQL language from scratch.

4. Now, click on View—Design View in the same query. The run symbol mark will be present, still. The bottom of the query table space now shows the fields selected for analysis or printing. 5. Which data fields are in the original data table but not in the query? Which fields are new fields, in the query but not in the original data?

Now, design your own queries:

Query 1

1. Choose Create—Query Design. By double clicking on fields, they are added to the design view below. Use the copy of patient information table as the data source 2. Develop a query that includes Last name, first name, city, state, DRG, Admission Date, Discharge date, DRG code, bill amount, and patient birthdate. 3. Select for Discharge date prior to 12/31/2001 4. Sort by last name, Ascending. 5. Save the query as query 1.

Query 2

1. Now we create a query based on an earlier query. 2. Select Create- Query Design again. 3. This time, select the Query on Tetanus time as the input, instead of the original table. 4. In this new query please show Last name, First name, Admission date, Patient birthdate, and Years since Tetanus. 5. Sort results by birth-date with oldest patients at the top of the page. 6. There is an issue with the Years since Tetanus field. When a new query is formulated, the formatting may not copy over. Within design view, select the Years since Tetanus field. The right hand corner displays field properties. From the format area there, click to drop down options and choose fixed to show only two decimal places. 7. Observe the other options for formatting within the query window.

Whenever you run a “cascading” query such as this one, which is based on an earlier query, remember that to get current information it may be necessary to run the first query, then run your second query, again.

Be sure to “save as” after each query is run so that the queries save in the database.