Using a separate sort column works in query, but doesn't transfer to pivot table or chart view 1

[uwrijc]

Hello,

I have a query that shows age ranges, e.g.:

0-17mo
18mo-3yrs
4-5yrs
6-10yrs
11yrs

I created a sort column (age1; age2; etc.) so they are returned in the correct order, and not:

0-17mo
11yrs
18mo-3yrs
etc.

I am setting this in the sort row of design view. It works fine when I run the query in datasheet view, but in pivot table and pivot chart views, it seems I must include the sort column in the chart. I wouldn't mind so much if I could hide it. Is there some SQL or VBA I can use? Or a simpler way?

Thanks - sorry if this has already been asked; I did search but didn't see anything.

Here is the SQL if that helps:
=======================
SELECT tblCohorts.CohortName, tblAgencies.AgencyName, tblGrantType.GrantType, tblPrograms.ProgName, tblDemogsAllYears1and2.Report, tblDemogsAllYears1and2.DemogType, tblDemogsAllYears1and2.Demog, tblDemogsAllYears1and2.ClientsServed, tblDemogsAllYears1and2.DemogSortOrder, tblPrograms.AllocationAmt, tblPrograms.ProgNo
FROM (tblGrantType INNER JOIN (tblCohorts INNER JOIN (tblAgencies INNER JOIN tblPrograms ON tblAgencies.AgencyNo = tblPrograms.AgencyNo) ON tblCohorts.CohortNo = tblPrograms.CohortNo) ON tblGrantType.ID = tblPrograms.ProgramType) INNER JOIN tblDemogsAllYears1and2 ON tblPrograms.ProgNo = tblDemogsAllYears1and2.ProgNo
ORDER BY tblAgencies.AgencyName, tblDemogsAllYears1and2.DemogSortOrder;
========================

 

[dhookom]

Would you be okay with using:

a) 0-17mo
b) 18mo-3yrs
c) 4-5yrs
d) 6-10yrs
e) 11yrs


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

Duane, love your “simple” thinking.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

simple minds create simple solutions.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[uwrijc]

Yes, I think I will have to do that. I'd rather not not have to, but after researching more, it seems to be the only option.

Another thing I am trying is I exported the query into Excel, as it has a few more chart options.

 

[TheresAlwaysAWay]

I've dealt with issues like this in the past, and I solved it by creating a query field called Ordinal. It's populated by a Switch statement.

Assuming you're using a bit field for the different age ranges (Yes/No), then it's a simple matter. Switch([0-17mo]=True,1,[18mo-3yrs]=True,2,[4-5yrs]=True,3,[6-10yrs]=True,4,[11yrs]=True,5)

Make Ordinal your first field and then set the field's Sort to Ascending. It will be permanently sorted by age range in the query output. You can also sort by that field in any place the data is displayed.