Break your logic down into steps.
I'd probably do this in three steps to start with:
1) Build a query to select all the (subject, year, date) tripples, call it
Candidate back to your table. The important part here is that we're selecting out the years:
- SELECT name, Year(date) AS [year], date
-
FROM Data
Call this query Candidate...
2) Build a full-join from
Candidate against its self to find all possible candidate pairs - something like:
- SELECT c0.name,
-
c0.date AS [date0],
-
c1.date AS [date1]
-
FROM Candidate AS c0, Candidate AS c1
-
WHERE (c0.name = c1.name)
-
AND
-
(c0.year+1 = c1.year)
Call this query CandidatePair...
3) Test all identified candidate pairs - like so:
- SELECT cp.*
-
FROM CandidatePair AS cp, Data AS d0, Data AS d1
-
WHERE ((cp.subject = d0.subject) AND
-
(cp.date = d0.date))
-
AND
-
((cp.subject = d1.subject) AND
-
(cp.date = d1.date))
-
AND
-
(((d0.q1 = 2) AND (d0.q1 = d1.q1))
-
OR
-
((d0.q2 = 2) AND (d0.q2 = d1.q2))
-
OR
-
((d0.q3 = 2) AND (d0.q3 = d1.q3))
-
OR
-
((d0.q4 = 2) AND (d0.q4 = d1.q4))
-
OR
-
((d0.q5 = 2) AND (d0.q5 = d1.q5)))
Call this FoundCandidatePair....
Then you'll have to do a
UNION query to garner your desired rows:
- SELECT d0.*
-
FROM FoundCandidatePair AS fcp0,
-
Data AS d0
-
WHERE (fcp0.name=d0.name) AND (fcp0.date=d0.date)
-
UNION
-
SELECT d1.*
-
FROM FoundCandidatePair AS fcp1,
-
Data AS d1
-
WHERE (fcp1.name=d1.name) AND (fcp1.date=d1.date)
Probably there's an easier way, however I'm fairly sure this'll get the job done.
Good Luck!