Inconsistent Colors for Pie Chart Categories

[MatthewR]

Hi, all. I'm making reports with pie charts. And some categories will appear in more than one pie chart. I want to make sure that a category is the same color in each pie chart. The problem is, if a category is null on a chart, it throws the whole color scheme off. I've included an example below.

On the Victim Age chart, the "36-45" age range is yellow, and the "46-55" range is green.

However, on the Perpetrator Age chart, the "36-45" age range is null, so it is excluded from the chart. So, Access makes the "46-55" range yellow. And as a result "46-55" is green in one chart, and yellow in another.

How do I fix this?

Thanks for your help!



Matt

[RuralGuy]

Can you use the Nz() function and return either 0 or 1 if Null (whatever works)?

[MatthewR]

Thanks for your response. That doesn't seem to be working for me. I went to the query and used the NZ function, but it still omits the category.

I tried writing it 5 ways, and none worked:

Nz(Count(tblVictimsPerps.PersonID))
Nz(Count(tblVictimsPerps.PersonID),0)
Count(Nz([tblVictimsPerps].[PersonID]))
Count(Nz([tblVictimsPerps].[PersonID],0))
Count(Nz(([tblVictimsPerps].[PersonID]),0))

Any idea what I'm doing wrong?

Here's what the full query looks like:

[CJ_London]

I think your problem is - there is no data, so nothing is returned

Also not clear why you need tblDemographics

I presume your sortorder is what provides the age ranges (tip - better to post the sql rather than the gui) and is based on some field in tblVictimPerps. If so then you need to create another query based on all perps and victims to generate your sortorder and left join that to the rest of your data to ensure you have all ranges.

You might also want to investigate using the partition function for age ranges

https://support.office.com/en-us/art...7-c94278274dc5

[MatthewR]

Thanks for the response, Ajax. I've posted the SQL below.

I guess you're right. I should probably create a separate query that categorizes the age group for everybody. In addition to dealing with my main problem, making a separate query, combined with using the partition function, would make my query much simpler. As you can see, the SQL statement is quite long.

So, I'll give it a try and see if it works.




SELECT Switch(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=25,1,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=35,2,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=45,3,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=55,4,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=65,5,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)>65,6,IsNull(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)),7) AS SortOrder, IIf(IsNull(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)),"<Unknown / Blank>",Switch(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=25,"25 & Under",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=35,"26-35",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=45,"36-45",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=55,"46-55",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=65,"56-65",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)>65,"Over 65")) AS Categories, Count(tblVictimsPerps.PersonID) AS PersonCount
FROM (tblFatalIncidents INNER JOIN tblVictimsPerps ON tblFatalIncidents.[Fatal Incident #] = tblVictimsPerps.Incident) LEFT JOIN tblDemographicInfo ON tblVictimsPerps.PersonID = tblDemographicInfo.Person
WHERE (((tblFatalIncidents.DataComplete)=True) AND ((tblVictimsPerps.VictimOrPerp)="Perpetrator"))
GROUP BY Switch(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=25,1,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=35,2,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=45,3,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=55,4,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=65,5,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)>65,6,IsNull(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)),7), IIf(IsNull(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)),"<Unknown / Blank>",Switch(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=25,"25 & Under",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=35,"26-35",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=45,"36-45",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=55,"46-55",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=65,"56-65",DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)>65,"Over 65"))
ORDER BY Switch(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=25,1,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=35,2,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=45,3,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=55,4,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)<=65,5,DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)>65,6,IsNull(DateDiff("yyyy",[DateofBirth],[Incident Date])-IIf(Format([DateofBirth],"mmdd")>Format([Incident Date],"mmdd"),1,0)),7);

[RuralGuy]

The Ajax post suggested a Left Join. That is an important point and I wanted to point it out.

[MatthewR]

Okay, I did the left join, but it's still not working for me. Any idea what I'm doing wrong? My SQL is below.

qryAgeGroupCalculation is the new query that has everybody's Age Group.

Also, I replaced tblVictimsPerps and tblFatalIncidents with qrySelectVictimsPerpsDataComplete, which combines data from those 2 tables.

SELECT qryAgeGroupCalculation.SortOrder, qryAgeGroupCalculation.AgeGroup AS Categories, Count(qrySelectVictimsPerpsDataComplete.PersonID) AS PersonCount
FROM qryAgeGroupCalculation LEFT JOIN qrySelectVictimsPerpsDataComplete ON qryAgeGroupCalculation.PersonID = qrySelectVictimsPerpsDataComplete.PersonID
WHERE (((qrySelectVictimsPerpsDataComplete.VictimOrPerp) ="Perpetrator"))
GROUP BY qryAgeGroupCalculation.SortOrder, qryAgeGroupCalculation.AgeGroup;

[FYI, the Sort Order isn't the field that creates the age groups- it's purpose is to assign a sort value to each Age Group. Therefore, it sorts "Under 25" prior to "26-35", even though it follows it in alphabetical order. It also moves the "<Unknown / Blank> category to the end of the sort.

The field that creates the Age Groups is the AgeGroup field in qryAgeGroupCalculation, which is renamed as "Categories" in this query.]

Thanks so much for your help.

[RuralGuy]

Make the Nz() return a 1 instead of zero just to see what it does to the chart.

[MatthewR]

Still no luck.

Just to be clear this is what you're suggeting, right? : Nz(Count(qrySelectVictimsPerpsDataComplete.PersonI D),1)

I also that, as well as Count(Nz(qrySelectVictimsPerpsDataComplete.PersonI D,1)),which didn't work either.

[RuralGuy]

Does that PersonI D have a space in it? If so, it should be [PersonI D]. Better yet, don't use embedded spaces in names. I use CamelFontNames myself.

[June7]

The space is a forum error (happens sometimes, throws in random space).

Query would be easier to read if didn't try to do all in one.

SELECT tblFatalIncidents.*, tblVictimsPerps.*, tblDemographicInfo.*, DateDiff("yyyy",[DateofBirth],[Incident Date]) AS Age, Switch([Age]<=25,1, [Age]<=35,2, [Age]<=45,3, [Age]<=55,4, [Age]<=65,5, True,6) AS SortOrder, Switch([Age]<=25,"25 & Under", [Age]<=35,"26-35", [Age]<="36-45",3, [Age]<="46-55",4, [Age]<="56-65",5, True,"Over 65") AS Categories FROM (tblFatalIncidents LEFT JOIN tblVictimsPerps ON tblFatalIncidents.[Fatal Incident #] = tblVictimsPerps.Incident) LEFT JOIN tblDemographicInfo ON tblVictimsPerps.PersonID = tblDemographicInfo.Person;

Now use that query like a table in subsequent queries.

If there are no incidents for a Category, that category will not be in the graph. Assuring all categories are represented requires a dataset of all possible categories. This dataset could be accomplished with a table or a UNION query. Then join that dataset into the query for the chart. Use an IIf() or Nz() expression to replace Null with 0. Review http://allenbrowne.com/QueryPerfIssue.html


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[CJ_London]

try the partition function I suggested - something like

SELECT replace(replace(replace(S.SortOrder," - 25","25 & Under"),"66 - ","Over 65"),"Unknown","<Unknown / Blank>") As Category, Count(C.PersonID) AS PersonCount
FROM (SELECT DataComplete, Incident, PersonID, replace(nz(partition(DateDiff("yyyy",DateofBirth,[Incident Date])-Abs(Format(DateofBirth,"mmdd")<Format([Incident Date],"mmdd")),25,65,10),"Unknown"),":"," - ") AS SortOrder FROM tblFatalIncidents INNER JOIN tblVictimsPerps ON tblFatalIncidents.[Fatal Incident #] = tblVictimsPerps.Incident) AS S LEFT JOIN tblVictimsPerps AS C ON S.Incident=C.Incident AND S.PersonID=C.PersonID
WHERE S.DataComplete=True AND C.VictimOrPerp  ="Perpetrator"
GROUP BY  replace(replace(replace(S.SortOrder," - 25","25 & Under"),"66 - ","Over 65"),"Unknown","<Unknown / Blank>") 
ORDER BY S.SortOrder

I've used replace around the partition function to replace : with -, used the nz function rather than isnull for unknown birthdates and the abs function rather than iif to simplify the code. Then used replace to provide the categories you want.

[June7]

I was curious and did a test with Partition(). If no one was in the age range of 56-65, that category was not in the dataset.

[CJ_London]

If no one was in the age range of 56-65, that category was not in the dataset

I agree - same with your solution. Databases can't create data out of nothing. If that is the situation then the OP would need to create an age range table to left join to everything else.

Both yours and my solution ensures that both charts in post#1 would show the same ranges for the same group of incidents

[MatthewR]

Thanks so much to all of you. June, I went ahead and separated it into multiple queries like you recommended, and it worked. Oh happy day!

I'm really glad you guys helped me figure this out.