Long Beach Create A Query for MS Access Data MGT And Information Systems Excel Task

1. Use the following files I provided – PatientData and ClaimsData – these have sample Patient Demographic Data and their Claims Data at a walk-in clinic/urgent care center.

2. Open MS Access. Go to External Data tab and click on Import Excel. Follow directions and import your Excel Files into 2 tables. For instruction see the slide set – Creating a Table in MS Access.pptx Provided to you just incase you need it

3. Create PatientData and ClaimsData tables following Step 2. Create a relationship between these two table using Pt_ID as the foreign key. Save your Access Database.

NOW WE WILL CREATE SOME QUERIES IN ACCESS.

1. SELECT QUERY: Create a query to show patient last name, first name, address, description of their claim and claim date.

• Create a query to show all patients from NM state.
• Create a query to select all patients (name & state) who have claims between 1/1/2015 and 6/30/2015.
• Create a query to select all patients (name & address) who DO NOT have a PCP Visit.

2. Use BUILT-IN FUNCTIONS:

• Create a query to get average claim amount per patient
• Create a query to get total claim per patient

3. Use a PARAMETER in a Query:

• Create a query to select Patients by State by using state as a parameter.

4. Build an EXPRESSION:

Create a query that shows patient last name, first name and the discount per claim.[You have claim amount and the actual amount paid to the practice. Build an expression to calculate the discount (difference between claim amount and actual amount)].

5. Create a query to select all claims who have incorrect service and claims dates. Figure out why they can be incorrect and select those claims and display claim ID, patient ID, patient name and address.