MS Access — Entity Relationship Diagram (ERD) — Conceptual and Physical Format

5 min readJan 5, 2021

Registration Form from a Family Medicine Clinic

This article consists of some extracts from my MS Access project I have completed in 2020 at George Brown Institute.

I had to analyze any unique form from Internet and produce and ERD (Entity Relationship Diagram) document which comprised of Entities, Attributes, Primary and Foreign Keys.

Hence, I chose a registration form from Family Medicine clinic. It consisted of —

· Patient Information

· Responsible Party (this section is completed only if a patient is minor)

· Insurance and Subscriber Information

· Health Information and Emergency Contact(s)

· Consent and Financial Responsibility

· Authorization and Disclosure of Confidentiality

Conceptual Format

I take you on a tour of what I have accomplished in this project. My database consists of Tables, Queries, Forms and Reports.

Here are some excerpts from each of elements.

Tables

These are some excerpts from tables which consists of —

· Entity — In simple meaning it means name of a table in which the data is captured and stored

· Primary Key (PK) — it uniquely identifies each record in the table. For example, for entity Patient — its primary key is Patient ID. Interestingly, when a primary key is used in another table, it becomes ‘Foreign Key’ — it is used to connect details from the parent entity (i.e. Patient).

· Attributes — fields from the ERD. For example, for entity Patient — its attributes are Patient#, First Name (FName), Middle Name (MName), Last Name (LName), Address1, Address2, City, State, ZipCode etc..

Design view of a table is shown below. It exhibits Data Type i.e. if it is a short text, long text or number. Notice that Patient ID is described as PK (Primary Key), hence, this element is used as a connection to other entities.

I also would like to show a conceptual ERD representation of how one entity is connected to another —

If you are wondering what does 1 to ∞ mean? — let us consider the case of Patient to Responsible Party. 1 to ∞ implies that 1 Patient can have multiple responsible parties.

One entity linked into another —

Entity Responsible Party is linked into Patient Entity

Queries

We use queries for data results to perform –

· calculations (average, sum, maximum, minimum)

· conditional operations(such as greater than >, less than <, less than equal to ≤, greater than equal to ≥, equal to =)

· Aggregated/grouped data — combination of data from different tables.

Some extracts of Queries are shown below along with the SQL code Access automatically creates in the system.

Example: to calculate the average age of patients visiting the clinic and the maximum age of responsible party to patient who is a minor.

SQL Code:

SELECT Patient.[Patient ID], Patient.Fname, Patient.LName, Avg(Patient.Age) AS [Average Age], [Responsible Party].Fname, [Responsible Party].LName, Max([Responsible Party].Age) AS [Max Age]
FROM Patient INNER JOIN [Responsible Party] ON Patient.[Patient ID] = [Responsible Party].PatientID
GROUP BY Patient.[Patient ID], Patient.Fname, Patient.LName, [Responsible Party].Fname, [Responsible Party].LName
ORDER BY Avg(Patient.Age);

Note that Age is arranged in the ascending order.

Also, note from the above query — Patient ID# 2 -Jaison George has two responsible parties Jou Li and Dennis Thomas, which implies 1 (one) to ∞ (many) relationships in an entity. 1(from Patient entity) to ∞ (Responsible entity) shown below —

The mechanism of how we obtain this formation is shown below —

Another example of query is select Policy ID starting with “45” -

SQL Code:

SELECT [Policy#].IDNumber, [Policy#].SName, [Policy#].SEmployer
FROM [Policy#]
WHERE ((([Policy#].IDNumber) Like “45*”));

Yet another example, select patients living in Toronto —

SQL Code:

SELECT Patient.Fname, Patient.LName, Patient.City
FROM Patient
WHERE (((Patient.City)=”Toronto”));

Forms

· Form is created using Form Wizard.

· A theme is applied to a form and a picture is added to it.

· We can change the colour of the text as well as of the form.

Here are some extracts of the forms —

Form displaying the insurance company’s information — picture and colour of text and form.

Above is the form that represents the combination of Patient and Emergency forms, hence, if we view the details of patient Rita Abrah, we have her corresponding emergency contact Scot’s details displaying on the same screen.

Now let’s move to Reports.