Show all category in a chart

[June7]

I am able to get a chart control to work.



I created a query ConsultationsChart:
SELECT Nz([Year],"None") AS YearCPT, Nz([ProvideName],"None") AS Provider, 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 Nz([Year],"None"), Nz([ProvideName],"None"), T_ProcedureCode.CPT
HAVING (((T_ProcedureCode.CPT)<"99206"));

Then the RowSource for chart control:
SELECT CPT, SumOfN FROM [ConsultationsChart] WHERE [YearCPT]=[Year] AND [Provider]=[ProvideName];

[Ray67]

June 7

Can you send me a copy of the db? I try it over here and it still doesn't show CPT with null value on the chart. It is showing me the one's with value.

Thank you

[June7]

Right, the 'null' records chart will be blank unless you handle the nulls in the report RecordSource same way I handled them in the chart query. Create the YearCPT and Provider fields in the report query. Then change the chart RowSource to reflect those names in the WHERE clause.

[Ray67]

The report is a crosstab query. How would i handle field N??? SumOfN: Sum(Nz([N],0))
I was able to create YearCPT and Provider fields.

[June7]

Yes, that should work, just like I did in the chart query.

[Ray67]

Ok i try it but it doesn't give me Nulls. The report is by month not by year.
Here is the SQL
TRANSFORM Sum(Nz([N],0)) AS SumOfN
SELECT Nz([Year],"None") AS YearCPT, Nz([ProvideName],"None") AS Provider, T_CPT.CPT
FROM T_CPT, T_ProcedureCode
WHERE (((T_CPT.CPT)="99211" Or (T_CPT.CPT)="99212" Or (T_CPT.CPT)="99213" Or (T_CPT.CPT)="99214" Or (T_CPT.CPT)="99215" Or (T_CPT.CPT)="99201" Or (T_CPT.CPT)="99202" Or (T_CPT.CPT)="99203" Or (T_CPT.CPT)="99204" Or (T_CPT.CPT)="99205" Or (T_CPT.CPT)="99241" Or (T_CPT.CPT)="99242" Or (T_CPT.CPT)="99243" Or (T_CPT.CPT)="99244" Or (T_CPT.CPT)="99245"))
GROUP BY Nz([Year],"None"), Nz([ProvideName],"None"), T_CPT.CPT
PIVOT T_CPT.Month In ('January','February','March','April','May','June' ,'July','August','September','October','November', 'December');

[June7]

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

ConsultationsChart:
SELECT Nz([Year],"None") AS YearCPT, Nz([ProvideName],"None") AS Provider, 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 Nz([Year],"None"), Nz([ProvideName],"None"), T_ProcedureCode.CPT
HAVING (((T_ProcedureCode.CPT)<"99206"));

Q_CPTTConsultationsChart:
SELECT Nz([Year],"None") AS YearCPT, Nz([ProvideName],"None") AS Provider, 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 Nz([Year],"None"), Nz([ProvideName],"None"), T_CPT.Month, T_ProcedureCode.CPT
HAVING (((T_ProcedureCode.CPT)<"99206"));

Chart RowSource:
SELECT CPT, SumOfN FROM [ConsultationsChart] WHERE [YearCPT]=[Year] AND [Provider]=[ProvideName];

Fix the expression for the ProvideName group. Fix textboxes ControlSource. Fix the Master/Child links for the subform or remove the subform.

[Ray67]

Thank you very much for you help.