Show all category in a chart

[Ray67]

Hello

I have a chart in report that show category for each month. This are thecategory 99201,99202,99203,99204,99205. When there is no value for a category on a given month the category will not show up.

I would like to show all categories even if there are no value for that month.



Thank you

[June7]

You need a data source that has all the categories, such as tblCategories. Include this table by join in the query for the chart. Jointype "Include all records from tblCategories ..." then pivot on the category field from tblCategories.

[Ray67]

June 7

I added the table like you said. It works great when i pivot it in queries. I wasn't sure how to transfer this to the report. I found online that i need to put this on a form and than drag the form into the report. The only think it's not giving me all of the category. I'm i doing it right.

[June7]

Build a query that joins tblCategories to the data for the chart. Then create the chart RowSource.

I would have to review the db to further analyze.

[Ray67]

Here is a copy of the db

Copy of CptChart.zip

[June7]

Why do you bother with WHERE clause in G_CPTCountChart query?

Why are there multiple tables of ProcedureCode? Why does T_ProcedureCode have duplicate CPT values? This will result in duplicate records in query join.

Why does the CPT field in T_CPT have values not in any of the ProcedureCode tables?

[Ray67]

I guess i forgot to take off the where clause after you told me to make a category table.
Why are there multiple tables of ProcedureCode? I was try different option because it wasn't working.
Cpt Field will have many values that i will not report on it. The report has all those values. I don't want to take off the other values because i will need to report on them for other reports.

[June7]

Why does T_ProcedureCode have duplicate CPT values? This will result in duplicate records in query join.

[Ray67]

That should not be there. another error. It should be 99201,99202,99203,99204,99205,99241,99242,99243,99 244,99245,99211,99212,99213,99214,99215

[Ray67]

I want to put three chart for each provider.

Chart number one would show numbers for 99201-99205

Chart number two would show numbers for 99241-99245

Chart number three would show numbers for 99211-99215

[June7]

Try:

1. Fix the data in T_ProcedureCode and delete the other code tables.

2. Q_CPTCountChart
TRANSFORM Sum(T_CPT.N) AS SumOfN
SELECT T_CPT.Year, T_CPT.ProvideName, T_ProcedureCode.CPT
FROM T_CPT RIGHT JOIN T_ProcedureCode ON T_CPT.CPT = T_ProcedureCode.CPT
WHERE (((T_ProcedureCode.CPT)<"99206"))
GROUP BY T_CPT.Year, T_CPT.ProvideName, T_ProcedureCode.CPT
PIVOT T_CPT.Month In ('January','February','March','April','May','June' ,'July','August','September','October','November', 'December');

3. Q_CPTTConsultationsChart
SELECT T_CPT.Year, T_CPT.ProvideName, T_CPT.Month, T_ProcedureCode.CPT, Sum(T_CPT.N) AS SumOfN
FROM T_ProcedureCode LEFT JOIN T_CPT ON T_ProcedureCode.CPT = T_CPT.CPT
GROUP BY T_CPT.Year, T_CPT.ProvideName, T_CPT.Month, T_ProcedureCode.CPT
HAVING (((T_ProcedureCode.CPT)<"99206"));

4. Put the subform in the ProvideName group footer.

Don't think I would embed PivotChart form on report. I would use Chart control. I've never used Pivot Chart/Table views. Be aware Microsoft has eliminated Pivot Chart/Table views from Access2013.

[Ray67]

Thank you for your reply.
I'm using the chart control in reports. It works but the only think it doesn't give me is all of the categories. It only give's my categories with values.

[June7]

True, would need to convert the Null fields to 0 in query. Can use Nz function for that.

3. Q_CPTTConsultationsChart
SELECT T_CPT.Year, T_CPT.ProvideName, T_CPT.Month, T_ProcedureCode.CPT, Sum(Nz([N],0)) AS SumOfN
FROM T_ProcedureCode LEFT JOIN T_CPT ON T_ProcedureCode.CPT = T_CPT.CPT
GROUP BY T_CPT.Year, T_CPT.ProvideName, T_CPT.Month, T_ProcedureCode.CPT
HAVING (((T_ProcedureCode.CPT)<"99206"));

I am finding the chart control more of a challenge with this report design. Will have to explore it some more tomorrow.

Year and Month are reserved words. Advise not to use reserved words as names.

[Ray67]

Hi

I try the new SQL and still didn't work. I think this is one step closer. I think what we are missing is for the values that are null to include Year, ProvideName and moth.

Example:

Year	ProvideName	Month	CPT	SumOfN
2012	Doe	April	99201	0
2012	Doe	April	99202	0
2012	Doe	April	99203	0
2012	Doe	April	99204	3
2012	Doe	April	99205	1

[June7]

Why not working?

It works in my test, although no chart shows for the categories that are 0 because there is no year and provider associated. Yes, if you want graph for those, need year and 'dummy' provider. The provider could be Nz(ProvideName,"None"). The year is more complicated if the db is going be multi-year.