Patient List with relational tables (one-to-many relationship) in Excel?

[joeystraw]

I have a list of patients in a Excel table with various headers (admit date, length of stay, discharge date, demographic info, etc.) but have additional data for each patient that needs to be documented and related to each patient record (row). Each patient can have multiple records of specialized consultations. Each patient has a unique identifier but I need to be able to enter these consultations as they occur including date, type of consultation, and recommendations. This is easily done in a database such as Access but can Excel provide this function and if so, how to create the relationships and how to enter data as it occurs? I am using Excel 2016 on a Windows 10 machine.

Test Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Parent Table							Child Tables
2	ID	Last Name	First Name	Date of Birth	Admit Date	Discharge Date		ID	Type Consultation	Date		ID	Type Consultation	Date		ID	Type Consultation	Date		ID	Type Consultation	Date
3	12218	Delacruz	CLAUDIA	4/19/1995	8/23/2019	9/30/2021		12218	Neurology	9/1/2019		32548	Pulmonology	12/4/2020		98554	Cardiology	2/5/2021		98554	Pulmonology	3/2/2021
4	32548	Frye	ANDREA	9/8/1989	10/23/2020	1/30/2021		12218	Endocrinology	10/5/2019		32548	Surgery	1/6/2021		98554	Pulmonology	3/2/2021		98554	Cardiology	2/5/2021
5	35481	Hensley	AMBER	7/21/1984	10/23/2020	12/1/2021		12218	Neurology	9/15/2020		32548	Orthopedics	1/25/2021
6	98554	Nixon	SANDY	5/26/2024	12/3/2020	4/30/2021		12218	Podiatry	7/5/2020
7	12556	Shepard	MARTHA	11/14/1985	10/22/2020	2/1/2021
8	68451	Medina	ATIYA	9/17/1985	10/22/2020	3/31/2021
9	25117	Hines	DOMINIQUE	2/24/1989	8/13/2020	1/1/2021
10	95845	Mcfarland	MARILYN	6/20/1971	10/8/2020	10/31/2020
11	77478	Bray	JENNIFER	2/3/1982	1/13/2020	6/30/2021
12	36551	Gilbert	SHIRA	8/13/1984	12/24/2020	3/15/2021
13	46163	Franco	RENA	4/3/1972	10/15/2019	12/1/2020
Sheet1

 

[Toadstool]

Hi JoeyStraw,

I'm not sure how workable a table per patient would be so why not have a single table with all the patient keys?

The queries would be the tricky part which you'd need to build. Something like this for "Show me all consultations for a given patient Id".

JoeyStraw2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Parent Table							Child Table				Query
2	ID	Last Name	First Name	Date of Birth	Admit Date	Discharge Date		ID	Type Consultation	Date		ID	Name	Consultations	Date
3	12218	Delacruz	CLAUDIA	4/19/1995	8/23/2019	9/30/2021		12218	Neurology	9/1/2019		32548	Andrea Frye	Pulmonology	04-Dec-20
4	32548	Frye	ANDREA	9/8/1989	10/23/2020	1/30/2021		12218	Endocrinology	10/5/2019				Surgery	06-Jan-21
5	35481	Hensley	AMBER	7/21/1984	10/23/2020	12/1/2021		12218	Neurology	9/15/2020				Orthopedics	25-Jan-21
6	98554	Nixon	SANDY	5/26/2024	12/3/2020	4/30/2021		12218	Podiatry	7/5/2020
7	12556	Shepard	MARTHA	11/14/1985	10/22/2020	2/1/2021		32548	Pulmonology	12/4/2020
8	68451	Medina	ATIYA	9/17/1985	10/22/2020	3/31/2021		32548	Surgery	1/6/2021
9	25117	Hines	DOMINIQUE	2/24/1989	8/13/2020	1/1/2021		32548	Orthopedics	1/25/2021
10	95845	Mcfarland	MARILYN	6/20/1971	10/8/2020	10/31/2020		98554	Cardiology	2/5/2021
11	77478	Bray	JENNIFER	2/3/1982	1/13/2020	6/30/2021		98554	Pulmonology	3/2/2021
12	36551	Gilbert	SHIRA	8/13/1984	12/24/2020	3/15/2021		98554	Pulmonology	3/2/2021
13	46163	Franco	RENA	4/3/1972	10/15/2019	12/1/2020		98554	Cardiology	2/5/2021
14
15
16
17
18
19
Sample

Cell Formulas
Range	Formula
M3	M3	=PROPER(INDEX($C$3:$C$9999,MATCH($L$3,$A3:$A9999,0))&" "&INDEX($B$3:$B$9999,MATCH($L$3,$A$3:$A$9999,0)))
N3:N19	N3	=IFERROR(INDEX($I$3:$I$9999,AGGREGATE(15,6,ROW($I$3:$I$9999)-ROW($I$2)/($H$3:$H$9999=$L$3),ROW()-ROW($N$2))),"")
O3:O19	O3	=IFERROR(INDEX($J$3:$J$9999,AGGREGATE(15,6,ROW($I$3:$I$9999)-ROW($I$2)/($H$3:$H$9999=$L$3),ROW()-ROW($N$2))),"")