Solved calculating gaps or missing periods

[follower2020]

Hello everyone.

hope that there is a solution for this in Ms Access.

Suppose I have one Patient who has been treated during a period say from 01/01/2021 to 31/01/2021 ( I register these days in my PATIENTS Table

in some days during that period of time he has been admitted , other dayshe has been treated as an outpatient.

e.g from 05/01/2021 to 10/01/2021 he was inpatient.
15/01/2021 to 18/01/2021 he was inpatient
22/01/2021 to 25/01/2021 he was inpatient.


I register inpatient days/periods in a table called admissions


if I want to calculate the missing days (i.e. the days the patient was treated as outpatient )

could this be done ?

The results would be the following dates / periods :

from 01/01/2021 to 04/01/2021 he was outpatient
from 11/01/2021 to 14/01/2021 he was outpatient
from 19/01/2021 to 21/01/2021 he was outpatient
from 26/01/2021 to 31/01/2021 he was outpatient

Thanks.

 

[arnelgp]

see Query1.
see also the structure of Patient table and Admission table.