Is it doable?

[Alexis513]

Hello All,



Hope you're all doing well.

I've been searching and searching and I can't seem to find what I'm looking for so I decided to finally ask. Maybe I'm not looking for the right terms or maybe what I want is not doable, but this is what I have:

I have a table (called Trainee) that looks like this

LastName	FirstName	StartDate	GradDate
Doe	John	7/1/2013	6/30/2016
Smith	Jane	8/1/2013	7/31/2014

I have another table, called specialty, that looks like this (these two are connected)

Specialty	Category	YearsofTrng
Anesthesiology	Residency	3
Pain Management	Fellowship	1

So each trainee is considered to go through levels during their training. For residency, the first year they are here they are considered to be level 2. The second year will be level 3, so on and so forth until they graduate. In this case, John Doe, who is doing a residency in anesthesiology, will be a level 4 on his last year of training.

Jane Smith is in Pain Management Fellowship. With this one, we need to count the residency that fellowship belongs to to get the accurate level. For Pain Management fellowship, its residency is Anesthesiology. Anesthesiology is a 3 year residency, so we would count, 3 years, plus the one year in training for fellowship, that puts her in level 5 on her only year of training. If her fellowship was 2 years, then she would be a 6. Hope this made sense.

I want something to come out like this (Level table):

LastName	FirstName	Specialty	StartDate	GradDate	AY13-14	AY14-15	AY15-16	AY17-18
Doe	John	Anesthesiology	7/1/2013	6/30/2016	2	3	4
Smith	Jane	Pain Management	8/1/2013	7/31/2014	5

I was hoping there's something out there that will do the calculation for me. Right now I do everything mentally and it can be exhausting. So I want something that will start the level on 2 on their first year of residency (for ALL trainees) and count up each year, so it can look like the level table (it would be a query when I do it). And another calculation that will look for the fellowship level. For this, it will have to count the residency years of training first (it will need to go to the specialty table to find the training years) and then count the years of fellowship to get the levels right.

I've been looking for different functions, but I just can't figure it out. I was doing the Date Diff to get the years in training and trying to get that value to do the calculation. I'm not sure which one to use, if it is doable. Any idea which functions to use? If I can get the right function I can work with it and if I get stuck I can come back here. This task is driving me nuts.

Any advice is appreciated. Thanks!

[June7]

Anything can be calculated if the algorithm can be defined. However, I don't understand the logic.

Why does Jane have only 1 year of training and John has 3? Jane has already been through the Anesthesiology residency?

How does one 'know' that Pain Management is associated with Anesthesiology? Is there a table that defines this? How many other fellowship/residency associations are there?

[Alexis513]

Anything can be calculated if the algorithm can be defined. However, I don't understand the logic.

Why does Jane have only 1 year of training and John has 3? Jane has already been through the Anesthesiology residency?

How does one 'know' that Pain Management is associated with Anesthesiology? Is there a table that defines this? How many other fellowship/residency associations are there?

Thank you for responding.

The specialty table will have all the residencies and fellowships. And how they relate.

Jane only has one year of traning because her fellowship is only one year of training. If she were doing another fellowship, lets say a Cardiology fellowship, she would be doing a 3 year fellowship. But if that were the case, she would have done a different type of residency. But on this table, she went through the anesthesiology residency to get to the pain management residency. They always have to go through the same type of residency to get to the fellowship they are doing.

Hope that made sense?
Thanks!

[June7]

Calculating a trainee's level at any point in time should be simple enough. The difficult part I see is calculating the multiple AY fields because the number of years varies for each trainee and presumably each has a different start year. How many training years are possible?

This might be doable with expressions in a query if the tables can be properly joined.

Otherwise, I imagine a VBA custom function would be needed.

Really need a better understanding of data structure. If you want to provide db for analysis, follow instructions at bottom of my post.