Access Pivot Chart - Specify Order Of Categories On The X-Axis

[DrPat]

I have a pivot chart based on a crosstab query. I would like the items on the category axis (x-axis) to appear left to right in the
order that they appear in the query results.

Some specifics:
Tables:
tblFreq
FreqID (PK, Number, Range 1-7)
Frequency (text)

tblResp
RespID (PK,Number, Range 1-5)
Response (text)

tblResults
ResultID (PK,AN)
FreqID_FK
RespID_FK

Query:
TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID
SELECT tblFreq.Frequency
FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK
GROUP BY tblFreq.Frequency, tblFreq.FreqID, tblFreq.FreqID
ORDER BY tblFreq.FreqID
PIVOT tblResp.Response;


Sample Results:
Frequency 0-24% 25-49% 50-74% 75-99% 100%
64 Hz 0 1 2 1 4
128 Hz 0 0 0 4 1
256 Hz 1 2 4 1 0
512 Hz 2 2 3 0 1


Along the x-axis, the results lay out as follows:
128Hz, 256Hz, 512Hz, 64Hz

Which I suppose is alphabetically ordered. Not what I want to see.

I am unable to use OrderBy in the forms property sheet because tblFreq.FreqID is not an available field, even though it's an expression in the query.

A scaled down version is attached.

Any insights?

Thanks,

Pat

 

[JHB]

If you can live with a number in front of the frequency then:

TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID
SELECT [FreqID] & " - " & [tblFreq].[Frequency] AS Frequency
FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK
GROUP BY [FreqID] & " - " & [tblFreq].[Frequency]
ORDER BY [FreqID] & " - " & [tblFreq].[Frequency]
PIVOT tblResp.Response;

 

[DrPat]

Thanks JHB,

That was a consideration that I was saving if all else failed.

Thanks for providing the sql. I was dreading the prospect of burying hours into this issue over the weekend. Now I can log in some beach time.

Have a great weekend.

/Pat

 

[JHB]

Have a great weekend.

You too, and luck with your project.