Results 1 to 13 of 13
  1. #1
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170

    Creating Calculated Fields based on Calculated Fields

    Hello,

    I have a report whose record source is based on the query below.

    In my report I cannot figure out how to Sum([Goal]) or calculate an average based off [% of Goal].

    *I am able to sum CountofLID through Format Ribbon, Grouping & Totals Menu, Sigma Totals Option, but the only selections available when I select [Goal] or [% of Goal] is Count Records, Count Values. I am guessing this is because [Goal] and [% of Goal] are calculated fields in the query?

    Help is greatly appreciated.

    Date Operator Prod Hour Shift Type CountOfLID Goal % of Goal AccessTotalsCountOfLID
    09/26/2013 John Smith 11 Full Shift 14 35 40% 39
    09/26/2013 John Smith 12 Full Shift 2 35 6% 39
    09/26/2013 John Smith 12 Lunch 8 18 44% 39
    09/26/2013 John Smith 13 Break 9 26 35% 39
    09/26/2013 John Smith 13 Full Shift 6 35 17% 39





    Code:
    SELECT Format([Time Added],"mm/dd/yyyy") AS [Date], [D1-Prod-MainTable].Operator, Hour([Time Added]) AS [Prod Hour], [D1-Prod-MainTable].[Shift Type], Count([D1-Prod-MainTable].LID) AS CountOfLID, IIf([Shift Type]="Full Shift","35",IIf([Shift Type]="Break","26",IIf([Shift Type]="Lunch","18","35"))) AS Goal
    FROM [D1-Prod-MainTable]
    GROUP BY Format([Time Added],"mm/dd/yyyy"), [D1-Prod-MainTable].Operator, Hour([Time Added]), [D1-Prod-MainTable].[Shift Type], IIf([Shift Type]="Full Shift","35",IIf([Shift Type]="Break","26",IIf([Shift Type]="Lunch","18","35")))
    HAVING (((Format([Time Added],"mm/dd/yyyy"))>=Format([Forms]![D1-Reports]![DateStart],"mm/dd/yyyy") And (Format([Time Added],"mm/dd/yyyy"))<=Format([Forms]![D1-Reports]![DateTo],"mm/dd/yyyy")) AND (([D1-Prod-MainTable].Operator)=[Forms]![D1-Reports]![Operator]));

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, I think it's because the Goal values are text. Remove the quotation marks from around the number values.

    I don't see [% of Goal] calculated in query.

    Why are you using GROUP BY in the query - there are no aggregate functions used.
    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
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    What does aggregate function mean?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Aggregate means calculating summary info on groups of records - Avg, Count, Sum, etc. are the functions that accomplish - data is 'aggregated' as in a grand total for a purchase order.
    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
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Quote Originally Posted by June7 View Post
    No, I think it's because the Goal values are text. Remove the quotation marks from around the number values.

    I don't see [% of Goal] calculated in query.

    Why are you using GROUP BY in the query - there are no aggregate functions used.

    Thank you so much.. This was seriously about a 2 hour scour of this forum and Google and could not find anything.

    Unfortunately, with % of Goal can still only select "Count Values", % of Goal is a calculated field on the report... Is there a work around? I have fixed the problem by inserting a textbox into the report footer whose control: =Sum([CountofLID])/Sum([Goal]) but is there a more elegant solution?


    Doing the Group By because LID is a Count field.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, I missed the Count() in query. I do find it odd not getting the Sum option for the CountOfLid field because it works for me.

    However, consider not using an aggregate query as the report RecordSource, just a regular select. Build report using grouping and sorting features and aggregate calcs in group footers and report footer. This allows display of detail records as well as summary calcs.
    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.

  7. #7
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Quote Originally Posted by June7 View Post
    Sorry, I missed the Count() in query. I do find it odd not getting the Sum option for the CountOfLid field because it works for me.

    However, consider not using an aggregate query as the report RecordSource, just a regular select. Build report using grouping and sorting features and aggregate calcs in group footers and report footer. This allows display of detail records as well as summary calcs.
    What do you mean by a regular select for a report recourd source? Doesn't it have to be based on a query/table?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I mean a SELECT query that does not do aggregation (no GROUP BY). And the RecordSource does not have to be a table or query object. It can be an SQL statement created directly in the RecordSource property. Many of my forms and reports are done that way.
    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.

  9. #9
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Very interesting.

  10. #10
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Quote Originally Posted by June7 View Post
    I mean a SELECT query that does not do aggregation (no GROUP BY). And the RecordSource does not have to be a table or query object. It can be an SQL statement created directly in the RecordSource property. Many of my forms and reports are done that way.

    Hi, I've tried this and it's working pretty well so far, my question today: How do I Count([LID]) in a report?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Textbox in header or footer section:

    =Count([Lid])
    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.

  12. #12
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    That works great. However, I am wondering if there is a way to just have show the Count instead of every record?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can set the Detail section as not visible.

    Or go back the the GROUP BY query as the report RecordSource.
    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. Query Date Range Based on Calculated Fields
    By wilsoa in forum Queries
    Replies: 6
    Last Post: 03-08-2012, 02:41 PM
  2. Calculated fields
    By FinChase in forum Queries
    Replies: 5
    Last Post: 02-02-2012, 06:12 PM
  3. Replies: 10
    Last Post: 11-05-2011, 11:41 AM
  4. Calculated fields
    By graciemora in forum Forms
    Replies: 1
    Last Post: 02-04-2011, 06:07 AM
  5. calculated fields
    By nashr1928 in forum Forms
    Replies: 1
    Last Post: 07-21-2010, 04:49 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