Help regarding MS Access querying

[gar6920]

I have a large set of data in the below format in a single table in MS Access. For the entire dataset, I am trying to see what percentage of people had changes for each of several possibilities over specific time periods. For example, patient's with examinations >1 year and < 2 year apart, how many remained unchanged (1-39 for right and left bifurcations) vs. how many had one of the other possibilities (e.g. 1-39% in one of the bifurcations on the first examination, then 1.5 years later 'occlusion').



I was thinking I may need to update each record with data from the appropriate comparison record, then run another query to analyze the data. I have been trying to use >dateAdd("yyyy",1,[Exam Date]) And <dateAdd("yyyy",2,[Exam Date]) in the query criteria. Any advice would be helpful. Thanks.

patient		Exam Date	RIGHT BIFURCATION	LEFT BIFURCATION
1		09-Jun-05	1-39%	1-39%
1		26-Aug-09	1-39%	1-39%
2		16-Nov-02	1-39%	1-39%
2		24-Mar-06	1-39%	80-99%
2		18-May-07	1-39%	OCCLUSION
3		27-Jul-07	1-39%	1-39%
3		23-Mar-12	30-50%	1-39%
4		08-Dec-03	1-39%	1-39%
4		27-Sep-10	1-39%	1-39%
5		06-Apr-09	40-59%	1-39%
5		12-Jun-03	40-59%	1-39%
5		31-Mar-09	40-59%	1-39%
6		16-Jan-06	1-39%	1-39%
6		21-Oct-11	1-39%	1-39%
7		09-Sep-99	30-50%	50-70%
7		01-Mar-00	30-50%	50-70%
7		15-Feb-01	30-50%	60-79%
8		02-Nov-09	1-39%	1-39%
8		20-Aug-13	1-39%	1-39%
9		17-May-00	30-50%	30-50%
9		23-Apr-02	20-40%	30-50%
9		16-Aug-11	40-59%	40-59%
10		01-May-09	1-39%	1-39%
10		27-May-11	1-39%	1-39%
11		10-Jun-03	80-95%	1-39%
11		15-Feb-05	1-39%	40-59%
12		17-Mar-04	1-39%	1-39%
12		07-Jul-05	1-39%	1-39%

[June7]

Getting data from another record of same table is tricky. Often requires nested subquery or domain aggregate function. What determines which records to compare?

Review: http://allenbrowne.com/subquery-01.html#AnotherRecord

[gar6920]

Need to compare all records of same patient and then figure out which of those has a date between certain timeframe, then compare both bifurcation values. I will play around with nested queries and see if they work. Thanks.