Minggu, 14 Februari 2016

Re: [MS_AccessPros] Modify query field names

 

Glenn-

Because you're pivoting on the values in Symptom, you can generate the column names you want by doing:

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:

Poskan Komentar