Chart showing actual vs target

[jazzy]

i am having a very difficult time trying to get a chart to show actual vs target. excel pretty simple.... in Access not so much. also want to show totals See PIC and attached




qry_AssetTotals1

SELECT tbl_NewAsset.ID, tbl_NewAsset.MonthID, Sum([tbl_NewAsset]![FeeBasedAssetTotals]+[tbl_NewAsset]![VA]+[tbl_NewAsset]![MutFundAlt]+[tbl_NewAsset]![InvestBrok]+[tbl_NewAsset]![AnnuityFixDefImm]) AS Totals, IIf([Source]="Existing" Or [Source]="Spouse Existing","Existing",IIf([Source]="Child Existing" Or [Source]="Child New","Child",IIf([Source]="Existing Client Referral" Or [Source]="Cold New Prospects","New"))) AS SourceTotalFROM tbl_NewAsset
GROUP BY tbl_NewAsset.ID, tbl_NewAsset.MonthID, IIf([Source]="Existing" Or [Source]="Spouse Existing","Existing",IIf([Source]="Child Existing" Or [Source]="Child New","Child",IIf([Source]="Existing Client Referral" Or [Source]="Cold New Prospects","New")))
HAVING (((tbl_NewAsset.MonthID) Between [Forms]![frm_NewAssetMain]![From] And [Forms]![frm_NewAssetMain]![To]));

qry_AssetTotals2

SELECT qry_AssetTotals1.MonthID, Sum(qry_AssetTotals1.Totals) AS SumOfTotals, qry_AssetTotals1.SourceTotal, IIf([SourceTotal]="New",12000000,IIf([SourceTotal]="Existing",12000000,IIf([SourceTotal]="Child",1000000))) AS Goal
FROM qry_AssetTotals1
GROUP BY qry_AssetTotals1.MonthID, qry_AssetTotals1.SourceTotal, IIf([SourceTotal]="New",12000000,IIf([SourceTotal]="Existing",12000000,IIf([SourceTotal]="Child",1000000)));

Chart

MonthID	SumOfTotals	SourceTotal	Goal
January, 2023	$40,001.00	Child	$1,000,000.00
January, 2023	$2,748,000.00	Existing	$12,000,000.00
January, 2023	$834,000.00	New	$12,000,000.00
February, 2023	$170,223.71	Child	$1,000,000.00
February, 2023	$1,638,000.00	Existing	$12,000,000.00
February, 2023	$3,878,658.00	New	$12,000,000.00
March, 2023	$609,292.00	Child	$1,000,000.00
March, 2023	$1,938,591.00	Existing	$12,000,000.00
March, 2023	$1,465,000.00	New	$12,000,000.00
April, 2023	$953,000.00	Existing	$12,000,000.00
April, 2023	$50,000.00	New	$12,000,000.00
May, 2023	$1,165,000.00	Existing	$12,000,000.00
May, 2023	$1,900,000.00	New	$12,000,000.00

[June7]

Could you provide sample raw data. Build table in post or attach file (Excel or Access).

[jazzy]

Could you provide sample raw data. Build table in post or attach file (Excel or Access).

Here ya go, thanks

[June7]

Exactly what difficulty are you having?

I used the following as chart RowSource:

SELECT [SourceTotals], SumOfTotals, [Goal] FROM [qry_AssetTotals2] WHERE MonthID = #1/1/2023#;

I did not apply MonthID filter in the first query but instead in the chart RowSource query.

Can eliminate a query, revised qry_AssetTotals1:

SELECT tbl_NewAsset.MonthID, Sum([tbl_NewAsset]![FeeBasedAssetTotals]+[tbl_NewAsset]![VA]+[tbl_NewAsset]![MutFundAlt]+[tbl_NewAsset]![InvestBrok]+[tbl_NewAsset]![AnnuityFixDefImm]) AS Totals, IIf([Source]="Existing" Or [Source]="Spouse Existing","Existing",IIf([Source]="Child Existing" Or [Source]="Child New","Child",IIf([Source]="Existing Client Referral" Or [Source]="Cold New Prospects","New"))) AS Sources, IIf([Source]="Existing" Or [Source]="Spouse Existing",12000000,IIf([Source]="Child Existing" Or [Source]="Child New",1000000,IIf([Source]="Existing Client Referral" Or [Source]="Cold New Prospects",12000000))) AS Goal
FROM tbl_NewAsset
GROUP BY tbl_NewAsset.MonthID, IIf([Source]="Existing" Or [Source]="Spouse Existing","Existing",IIf([Source]="Child Existing" Or [Source]="Child New","Child",IIf([Source]="Existing Client Referral" Or [Source]="Cold New Prospects","New"))), IIf([Source]="Existing" Or [Source]="Spouse Existing",12000000,IIf([Source]="Child Existing" Or [Source]="Child New",1000000,IIf([Source]="Existing Client Referral" Or [Source]="Cold New Prospects",12000000)));

Then chart RowSource:
SELECT [Sources], Totals, [Goal] FROM [qry_AssetTotals1] WHERE MonthID = #1/1/2023#;


If you want different goals for each MonthID/Source pair, should have a Goals table.

[jazzy]

Need to show in same chart, like the excel screen shot i sent, the actual money vs what the goal is. not sure what Chart Settings or Query i need to have the chart in Access be the same as the one i sent in excel. thanks for consolidating the query.

Thanks for commenting.

[June7]

Sorry, I forgot about the overall total. Use UNION query for the chart RowSource.

SELECT [Sources], [Goal], [Totals] FROM qry_AssetTotals1 WHERE [MonthID]=#1/1/2023#
UNION SELECT "Total", Sum([Goal]), Sum(Totals) FROM qry_AssetTotals1 WHERE MonthID=#1/1/2023#;