querying first occurence

[kwelch]

I have two tables related by an ID:

tbl_Patients
PatientID
PatientName
etc.



tbl_survey
SurveyID
PatientID_FK
DateofSurvey
q1
q2
q3
etc.

They are related by the PatientID and PatientID_FK.

The survey responses are marked by a date of entry. Patients will be surveyed multiple times (T=0, T=3m, T=6m, etc. more or less). I would like to be able to design a query that collects the first occurrence of the survey and summarizes the data (it will be a total score, which I know how to average), the second occurrence of the survey and summarize the data, etc.

Since everyone will have different dates of collection, I can't use a date range.

I suppose I could redesign this to have definitive collection time periods (0, 3, 6, 12, 24 months) and then include the date. Maybe that would be much easier. However, after a certain period of time, while seeing these patients, I might not know if this is the 6m survey or 3m survey...other than looking at dates.

[sychobob]

From what I understand you could simply order them by date and patient by doing so you don't need a date range but it would still order the surveys from the first to the last one they got

[JoeM]

If you create an Aggregate Query, you can get the first survey data for each Patient.
To do this, only add PatientID_FK and DateofSurvey to the Query.
Then, click on the Totals button (looks like a sigma). This will add a Totals row under each field with the phrase "Group By" under each of the two fields.
Under the DateofSurvey field, change the "Group By" option to "Min". This will return the earliest date for each person.

Now, if you wanted to get all the data for these people and their first survey, create a new query that links your tbl_Survey table to the query you just created, being sure to join the two on BOTH the PatientID_FK and DateofSurvey fields. Then return any of the fields you want.

This should give you all the data you are looking for.

[kwelch]

JoeM,

That works great. Thank you for the suggestion. This will get me the initial survey as well as the most recent survey (I assume I would use "max" instead). I'm wondering what I could do to get the survey results between the max and the min.

[JoeM]

I'm wondering what I could do to get the survey results between the max and the min.

OK. In your Aggregate Query, add the PatientID_FK field and then add the DateofSurvey field twice.
Change to an Aggregate Query, and under the first DateofSurvey field, select "Min" and then under the seocomd DateofSurvey field, select "Max".
Save the query.

Now, create your new query joining your original table to the query built above, but ONLY on the PatientID_FK field.
Then, add the DateofSurvey field from your original table, and add this on the criteria line of that field:

<>[QueryName]![MinOfDateofSurvey] And <>[QueryName]![MaxOfDateofSurvey]

where QueryName is the name of the query you created.

[kwelch]

Awesome. Thank you!