TRANSFORM Count(Q.Symptom) AS CountOfSymptom
SELECT Q.CASEID
FROM (SELECT tblListSymptomsOther.CaseID, "symp_5" & tblSymptoms.Symptom As SymptomPivot
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
PIVOT Q.SymptomPivot;
John Viescas
Sent from my iPad
On Feb 14, 2016, at 16:20, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Greetings,
I am using this crosstab query
TRANSFORM Count(Q.Symptom) AS CountOfSymptom
SELECT Q.CASEID
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
PIVOT Q.Symptom;
to generate the following results.
Query2 | ||||||||||
CASEID | Chills | Dehydration | Dizziness | Fatigue | Headache | Joint pain | Malaise | Muscle pain | Other | Perspiration |
5 | 1 | |||||||||
10 | 1 | 1 | ||||||||
14 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |
16 | 1 | 1 | ||||||||
20 | 1 | |||||||||
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.
Glenn
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar