Results 1 to 6 of 6
  1. #1
    jazzy is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    77

    Chart showing actual vs target

    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


    Click image for larger version. 

Name:	Capture.PNG 
Views:	25 
Size:	18.1 KB 
ID:	50256

    qry_AssetTotals1


    Code:
    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
    Code:
    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Could you provide sample raw data. Build table in post or attach file (Excel or Access).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jazzy is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    77
    Quote Originally Posted by June7 View Post
    Could you provide sample raw data. Build table in post or attach file (Excel or Access).
    Here ya go, thanks
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jazzy is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    77
    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.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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#;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Chart is showing non existent data at the front
    By PastorMike in forum Access
    Replies: 9
    Last Post: 02-08-2022, 07:40 AM
  2. Chart is not showing up on the report.
    By Guntar in forum Programming
    Replies: 1
    Last Post: 12-19-2012, 03:11 PM
  3. some problem in showing chart legend
    By afshin in forum Queries
    Replies: 5
    Last Post: 09-16-2012, 10:03 AM
  4. Scatter Chart not Showing X-axis Values
    By ward0749 in forum Forms
    Replies: 4
    Last Post: 03-20-2012, 06:37 AM
  5. line chart not showing lines
    By ajetrumpet in forum Reports
    Replies: 0
    Last Post: 09-11-2010, 05:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums