Results 1 to 13 of 13
  1. #1
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9

    Grouping

    Hi All,

    I have created one query. In my query there are Day(s), CaseID, Type.
    I will like to group my report with day(s) but if the days is more than 5, I will like to group them in one category.
    How can I do so?

    So which mean my report will only have 5 groups.


    1 day
    2 days
    3 days
    4 days
    more than 5 days

    Thanks in advance

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can construct a field in report RecordSource to calculate the 'groups' and use that field in the report design. Or you can even do the calc in the report group header.

    Field name is 'Day(s)'? Advise NOT to use spaces or punctuation/special characters (underscore only exception) in names.

    GroupID: IIf([Day(s)] >= 5, ">= 5", [Day(s)]) & IIf([Day(s)]=1, " Day", " Days")
    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
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9
    Click image for larger version. 

Name:	error.JPG 
Views:	20 
Size:	29.9 KB 
ID:	29301

    Hi June7, thanks for ur advise but I have this error when trying to run the report.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you do the calc in query? Post the query SQL statement.
    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
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9
    SELECT TBLCaseDetail.CaseID, DateDiff("d",[ReportedDate],[ResolvedDate]+1) AS [Days], TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status
    FROM TBLCaseDetail INNER JOIN TBLCaseHistory ON TBLCaseDetail.CaseID = TBLCaseHistory.CaseID
    WHERE (((TBLCaseDetail.ReportedDate)>=[Forms]![FormDateRangeForCloseCase]![StartDateforCloseCase] And (TBLCaseDetail.ReportedDate)<[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1) AND ((TBLCaseDetail.Status)="Resolved"))
    GROUP BY DateDiff("d",[ReportedDate],[ResolvedDate]+1), TBLCaseDetail.CaseID, TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status
    HAVING (((TBLCaseHistory.ResolvedDate) Is Not Null))
    UNION SELECT TBLCaseDetail.CaseID, DateDiff("d",[ReportedDate],[ResolvedDate]+1) AS [Days], TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status
    FROM TBLCaseDetail INNER JOIN TBLCaseHistory ON TBLCaseDetail.CaseID = TBLCaseHistory.CaseID
    WHERE (((TBLCaseDetail.ReportedDate)>=[Forms]![FormDateRangeForCloseCase]![StartDateforCloseCase] And (TBLCaseDetail.ReportedDate)<[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1) AND ((TBLCaseHistory.ResolvedDate)>[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1))

    OR (((TBLCaseDetail.ReportedDate)>=[Forms]![FormDateRangeForCloseCase]![StartDateforCloseCase] And (TBLCaseDetail.ReportedDate)<[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1) AND ((TBLCaseDetail.Status)<>"Resolved") AND ((IsNull([TBLCaseHistory].[ResolvedDate]))<>False))
    GROUP BY DateDiff("d",[ReportedDate],[ResolvedDate]+1), TBLCaseDetail.CaseID, TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status;

  6. #6
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9
    [Days] is a calc.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't see my suggested calc in the query. Did you do it in the report Sorting and Grouping design?

    Why are you doing GROUP BY when there are no aggregate calcs? Both GROUP BY clauses seem unnecessary.
    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.

  8. #8
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9
    Hi June7,

    Thanks for your help.
    I am not sure how can I add it into my query so I add it into the report Sorting and Grouping design.

    I have removed the grouping in my query to below.

    SELECT TBLCaseDetail.CaseID, DateDiff("d",[ReportedDate],[ResolvedDate]+1) AS [Days], TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status
    FROM TBLCaseDetail INNER JOIN TBLCaseHistory ON TBLCaseDetail.CaseID = TBLCaseHistory.CaseID
    WHERE (((TBLCaseHistory.ResolvedDate) Is Not Null) AND ((TBLCaseDetail.ReportedDate)>=[Forms]![FormDateRangeForCloseCase]![StartDateforCloseCase] And (TBLCaseDetail.ReportedDate)<[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1) AND ((TBLCaseDetail.Status)="Resolved"))
    UNION SELECT TBLCaseDetail.CaseID, DateDiff("d",[ReportedDate],[ResolvedDate]+1) AS [Days], TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status
    FROM TBLCaseDetail INNER JOIN TBLCaseHistory ON TBLCaseDetail.CaseID = TBLCaseHistory.CaseID
    WHERE (((TBLCaseDetail.ReportedDate)>=[Forms]![FormDateRangeForCloseCase]![StartDateforCloseCase] And (TBLCaseDetail.ReportedDate)<[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1) AND ((TBLCaseHistory.ResolvedDate)>[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1))

    OR (((TBLCaseDetail.ReportedDate)>=[Forms]![FormDateRangeForCloseCase]![StartDateforCloseCase] And (TBLCaseDetail.ReportedDate)<[Forms]![FormDateRangeForCloseCase]![ENDDateforCloseCase]+1) AND ((TBLCaseDetail.Status)<>"Resolved") AND ((IsNull([TBLCaseHistory].[ResolvedDate]))<>False))
    GROUP BY DateDiff("d",[ReportedDate],[ResolvedDate]+1), TBLCaseDetail.CaseID, TBLCaseDetail.Type, TBLCaseDetail.ReportedDate, TBLCaseHistory.ResolvedDate, TBLCaseDetail.Status;



    And add in the report sorting and group with the below:

    =IIf([Days]>5,"> 5",[Days])


    I am still having the same error.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  10. #10
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9
    Hi June7,

    I am trying to upload my database but the problem is the file to too huge to upload. Tried to delete most of the data but still too huge.
    Is there any other way to upload the file?
    I tried to zip to Rar but it does not accept this file format to be uploaded.

    Thanks!

  11. #11
    Yewee is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    9
    FCC Case Database Version 1.12.zip

    Hi June7,

    Manage to reduce the size of the file but have deleted a lot of data.
    Not sure if it is ok.

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create a query for the days, containing CaseID and the field:
    Days: IIf(DateDiff("d",[ReportedDate],[ResolvedDate]+1)>5,5,DateDiff("d",[ReportedDate],[ResolvedDate]+1))

    Then join this query in to the union query using this field instead of the calc and remove the calcs from the report.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Graph RowSource still using field name [Day(s)].

    Remove criteria from the report Filter and OrderBy properties. I started building a new report to test and these properties are automatically filled in. Never seen that happen. And it used field name [Query1].[Day(s)] - it's coming from the design properties of the UNION query. I never set properties for tables and queries.

    I rebuilt the report by copy/paste controls from the original and same grouping calc, except without group footer. Do not get the error. Can only suggest the original report is corrupted beyond repair.


    I never use dynamic parameterized queries. You are referencing the form controls in the UNION query as well as in the queries the UNION is based on. This works of course. As developer, I just sometimes like to open queries when forms are not open.
    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. About Grouping
    By GraeagleBill in forum Reports
    Replies: 7
    Last Post: 03-02-2017, 02:36 PM
  2. Sum/ grouping help
    By beegee in forum Access
    Replies: 5
    Last Post: 12-08-2016, 10:18 AM
  3. Grouping and Sum
    By dejhost in forum Reports
    Replies: 9
    Last Post: 08-20-2015, 07:01 AM
  4. Grouping
    By greg2725d in forum Reports
    Replies: 7
    Last Post: 09-29-2011, 10:00 AM
  5. Grouping By Age
    By xnixiel in forum Queries
    Replies: 1
    Last Post: 07-01-2010, 09:14 AM

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