feed query into second query

[tuxalot]

I have a query that produces data like this:

name		date	q1	q2	q3	q4	q5
Subject A	3/1/08	1	2	1	2	1
Subject A	5/3/09	1	2	1	1	2
Subject A	6/4/10	2	1	2	2	2
Subject B	4/5/08	1	1	2	1	2
Subject C	2/7/09	1	2	2	2	1
Subject C	3/8/10	1	2	1	2	1

q1-100 represent responses to questions. I am only interested in responses = 2 for consecutive years. So I would like to feed the above into a second query to produce this result:

name		date	q1	q2	q3	q4	q5
Subject A	3/1/08		2				
Subject A	5/3/09		2		        2	
Subject A	6/4/10				         2	
Subject B	4/5/08						
Subject C	2/7/09		2		2		
Subject C	3/8/10		2		2

Any ideas how I can achieve this? Thanks for any assistance.

[Oralloy]

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 FoundCandidateP air....

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!

[tuxalot]

WOW! Thanks Oralloy for your insight. I am getting an error at your FoundCandidateP air step. When I run my query qxtbRspnsIDxQst nLvls I get these results:

Response	HName	SurveyStartDate	1	2	3	4	5	6	7	8	9	10	11	12	29	30	33
1	Test A	11/1/2010	1	2	2	1									1	2	1
2	Test A	10/1/2009	2	2	1	1									1	1	1

Some of the questions are not answered, so as you can see above there is no data to below some of the questions. In my sample data the only matching question (both responses = 2) is question 2.

So using your code (I hope I modified it correctly), this is what I get:

Step 1

SELECT Hname, Year(SurveyStartDate) AS [year], SurveyStartDate
FROM qxtbRspnsIDxQstnLvls;

Produces this:

Hname year SurveyStartDate
Test A 2010 11/1/2010
Test A 2009 10/1/2009

Step 2

SELECT c0.Hname, c0.SurveyStartDate AS date0, c1.SurveyStartDate AS date1
FROM Candidate AS c0, Candidate AS c1
WHERE (c0.Hname = c1.Hname)
AND
       (c0.year+1 = c1.year);

Produces this:

Hname date0 date1
Test A 10/1/2009 11/1/2010

Step 3

SELECT cp.*
FROM CandidatePair AS cp, qxtbRspnsIDxQstnLvls AS d0, qxtbRspnsIDxQstnLvls AS d1
WHERE ((cp.Hname = d0.Hname) AND
         (cp.date = d0.date))
        AND
        ((cp.Hname = d1.Hname) AND
         (cp.date = d1.date))
        AND
        (((d0.1 = 2) AND (d0.1 = d1.1))
         OR
         ((d0.2 = 2) AND (d0.2 = d1.2))
         OR
         ((d0.3 = 2) AND (d0.3 = d1.3))
         OR
         ((d0.4 = 2) AND (d0.4 = d1.4))
         OR
         ((d0.5 = 2) AND (d0.5 = d1.5)))

Note: questions are not actually preceded by the letter "Q" as I put in my example
Also, I will have upwards of 150 questions. Once I get this going, is there a way I can loop through instead of specifying each question as you did above?

[Oralloy]

Oh my....

At this point you have several options:

1. [#]Build support VB code to dig through the CandidatePair result, load each pair of records, and test them.
   [#]Build support VB code to run a query which selects all fields associated with each CandidatePair record.
   [#]Build the complete SQL query FoundCandidateP air

All three are a pain, how you progress depends on how you need the output information.

One thing you need to be aware of is that many databases limit the number of fields in an SQL statement. I don't know if Access has a 256 field limit or not. This is, however, something you need to be aware of, given the size of your problem space.

Let's assume that you have a table FoundCandidateP air, that you are trying to populate. Let's try some pseudocode, as I'm not willing to build a database and actually code this out...

RecordSet0 = DB.Execute("SELECT * FROM CandidatePair")

FOR EACH Record0 IN RecordSet0
  name = "'" + Record0("name") + "'"
  date0 = Record0("date0")
  date1 = Record0("date1")

  queryA = "SELECT * FROM Data WHERE (name=" + name + ") AND (date=" + date0 + ")"
  queryB = "SELECT * FROM Data WHERE (name=" + name + ") AND (date=" + date1 + ")"

  RecordSetA = DB.Execute(queryA)
  RecordA = RecordSetA.fetch       ' or whatever the VBA code is to do this
  RecordSetB = DB.Execute(queryB)
  RecordB = RecordSetB.fetch

  FOR EACH FieldA IN RecordA.fields
    IF FieldA.name = "name" THEN
      ' noOp
    ELSE IF FieldA.name = "date" THEN
      ' noOp
    ELSE IF FieldA.value <> 2 THEN
      ' not interesting
    ELSE IF FieldB(FieldA.name).value <> 2 THEN
      ' not interesting
    ELSE
      ' match, save the candidate as a found
    END IF
  END FOR EACH
END FOR EACH

There are a couple problems, like multiple column matches that you can sort out.

However, I hope this makes sense, so you can get on with your project.

Luck!
Oralloy

[tuxalot]

thanks again Oralloy. I'll muss around and see what I can get going.