I am using this crosstab query
TRANSFORM Count(Q.Symptom) AS CountOfSymptom
FROM (SELECT tblListSymptomsOther.CaseID, tblSymptoms.Symptom
FROM tblCases INNER JOIN (tblSymptoms INNER JOIN tblListSymptomsOther ON tblSymptoms.SymptomID = tblListSymptomsOther.ListItemID) ON tblCases.CaseID = tblListSymptomsOther.CaseID
WHERE (((tblListSymptomsOther.ListID)=1) AND ((tblSymptoms.SymptomClassID)=2))
ORDER BY tblSymptoms.Symptom) AS Q
GROUP BY Q.CASEID
to generate the following results.
So far so good. However, this is an intermediate query. In the final query I need the field names to be slightly modified by prefixing “symp5_” to each field name:
“symp5_Chills”, “symp5_Dehydration”, “symp5_Dizziness”, ... , “symp5_Perspiration”
The purpose of this is to generate a flat file that an analyst will use for analyzing the set of symptoms.
So far, I have found that I can’t simply modify the field names in a dao recordset. It has occurred to me to generate a new query in VBA with the desired field names as alias but I am wondering if there might be a better way to do it.
Posted by: "Glenn Lloyd" <firstname.lastname@example.org>
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (1)|