In this class, we will be creating and using Access databases. You must have Microsoft Access on your computer to complete these assignments. There are four main objects that we will be working withi

Databases are typically organized using the principle of “One-To-Many”. In the HIM industry, this typically means that each patient is assigned a unique identifier known as a Medical Record Number, and each encounter that the patient has is assigned a unique identifier known as the Account Number.

Since both the Medical Record Number and the Account Number are subject to human intervention, we cannot allow the database to “auto-assign” these values or use them for linking purposes. Instead, we use the principle of an AutoNumber field that is assigned to both the MPI table and the Admits table. In this manner, the database will automatically link the patient’s identity in the MPI with all encounters in the Admits table. While this may seem redundant to the experienced HIM user, it is essential that each table within the database contain a field which automatically allows it to link with other tables.

The diagram that you see below is known as a “Schema”. It describes how the data that is held in the MPI table on the left is logically related to each patient’s individual encounters (Admits) table on the right. Please note that both tables (MPI and Admits) contain a single AutoNumber field. The AutoNumber field in the MPI is called the “PtId” and it is linked to a corresponding Numeric field in Admits called “PtId”. That these two fields share the same name is not an accident, as we will shortly see.

Gera, M. (2016) The 10 Step Guide to Microsoft Access for Health information Management Professionals. Healthcare PC Training.

How to Build a Relationship

1. Open the Access database Step01- Build-Relationships- Between- Tables

2. Double click on the MPI table

3. Click on the drop down arrow under view and select “Design View”

4. Click on “Insert row”

5. In the new row enter “PtId” under field name

6. From the drop down under data type select “AutoNumber”

7. Click on “Primary Key”

8. Close the MPI tab by click the “x”

9. Click “Yes” to save changes

10. Double click on the “Admits” table

11. Click on the drop down arrow under view and select “Design View”

12. Click on “insert row”. Insert two rows

13. Row one – Field name “AdmitId” and Data type is AutoNumber

14. Row two – Field name “PtId” and Data type is Number

15. Make “AdmitId” (Row one) the primary key

16. Close the Admits tab by click the “x”

17. Click “Yes” to save changes

18. Click on “Create”

19. Click on “Query Design”

20. Click on MPI, then click on add

21. Click on Admits, then click on add

22. Right click on the line between the MPI PtId and the Admits PtId and click “Delete”

23. Click on MPI – MRNo and drag to Admit MRNo

24. Click on “Update”

25. Take a screen shot of this screen for grading

26. Click on Admits PtId and drag it into the first column of query

27. On the line that says “Update To”, type MPI!PtId and close the query

28. Click “yes” save changes

29. Give the query the name “Update”, click “ok”

30. Using the down arrow by “Tables” and locate “Queries”

31. Double Click on “Update” query

32. Click “Yes” to run query

33. Take a screen shot of this screen to submit for grading

34. Click “Yes” to update records

35. Click “Database Tools”

36. Click “Relationships”

37. Click MPI, Click Add, Click Admits, Click Add, Click Close

38. Drag MPI PtId to Admits PtId

39. Click on “Join Type”

40. Click on “#2 Include all records from MPI and only those records from Admits where the jointed fields are equal

41. Click “Ok”

42. Click on “Create”

43. Take a screen shot of this screen

44. Click “Close”

45. Submit the three screen shots for grading