Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-24-2015, 05:05 AM   #16
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,348
Thanks: 10
Thanked 2,270 Times in 2,222 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Grouping

You really need to think about your Groups better. I don't think you realize the overlap.

Just to make sure we are on the same page, I'm from America, so I use the date format mm/dd/yyyy. Also, for this exercise let's say your week runs Sun - Sat. With that in mind suppose this is your data:

SalesData
SalesDate, SalesAmount
7/24/2015, 500
7/22/2015, 101
7/19/2015, 21
7/18/2015, 200
7/7/2015, 113
7/1/2015, 88
6/30/2015, 11

When you calculate totals you can't really group by Today, this Week, this month all at the same. 7/24 is Today and also This Week and This month. Also, suppose today was 7/1/2015. That means 6/30/2015 would be yesterday, this week but last month.

To group your data you would need to create a field for every level you wanted and assign a value to every record:

SalesQuery
SalesDate, SalesAmount, DailyGroup, WeeklyGroup, MonthlyGroup
7/24/2015, 500, Today, This Week, This Month
7/22/2015, 101, 2 Days Ago, This Week, This Month
7/19/2015, 21, More Than 5 Days, Last Week, This Month
7/18/2015, 200, More Than 5 Days, Last Week, This Month
7/7/2015, 113, More Than 5 Days, More Than 2 Weeks, This Month
7/1/2015, 88, More Than 5 Days, More Than 2 Weeks, This Month
6/30/2015, 11, More Than 5 Days, More Than 2 Weeks, Last Month

That's what your underlying query should look like, then in your report you choose the grouping field to use (DailyGroup, WeeklyGroup, MonthlyGroup).

plog is offline   Reply With Quote
Old 07-24-2015, 04:01 PM   #17
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Grouping

Quote:
Originally Posted by mlahajnar View Post
Hmm, can you kinda point me into the right direction about the various groups in a report?
so say you have DueDate as a field in your tasks table. make a query with a calculated field using nested datediffs or dateserials, something like (disclaimer: aircode)
Code:
Urgency: iif([DueDate]=Date(),"Today",iif([duedate-dateserial(month(date()),year(date()),"This month")
...etc

you'll end up with a result like:

DueDate | Urgency | Task
25/7/15 | Today | Walk the dog
25/7/15 | Today | Write that code
31/7/15 | This month | Clean the windows.

you can then either present the query in a subform datasheet view or even a continuous form with the "Urgency" field displayed. or you can base a report on this query and group by the Urgency field to have them split up like in outlook.

The groupings in the report can be multilevel, so you can start with "project", then "user" then "urgency"... or whatever. Here i have the received date for our lab purchases both by year and by month. i'm sure with a little fidling you could figure out how to get your urgency in a similar way.

Attached Images
File Type: png report grouping.png (30.7 KB, 222 views)
__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by wiklendt; 07-24-2015 at 04:06 PM.
wiklendt is offline   Reply With Quote
Old 07-24-2015, 11:34 PM   #18
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Grouping

as for giving users grouping options in a report, i have another area in my database where, once a user has a list they want to print, they press a button "print options" which opens a new window that has sorting, grouping, and filtering options:



when the user presses "Go!" this triggers a filter building code (you can leave this part out if you don't intend your users to change which records they want):

Code:
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

    Dim strWhere As String

    strWhere = BuildFilter
    
    If Left(strWhere, 7) = " WHERE " Then
        strWhere = Right(strWhere, Len(strWhere) - 7)
    End If

    DoCmd.OpenReport "rptOrder_Queue", acViewPreview, , strWhere

Exit_cmdOpenReport_Click:
    Exit Sub

Err_cmdOpenReport_Click:
    strErrorNum = str(Err.Number)
    strErrorDsc = Err.Description
    Msg = "Error # " & strErrorNum & Chr(13) & " (" & strErrorDsc & ")" & _
    Chr(13) & " in: " & cstrModule & " | cmdOpenReport_Click"
    MsgBox Msg, vbOKOnly, fstrDBname & ": Error"
    'log the error message to the external file referenced in modFunctions
    fErrorLog "--" 'separates error events
    fErrorLog "--|" & Now() & "|" & fstrDBversion() & "|" & GetAccessVersion & "|" & fstrCurrentUserID() & ": " & fstrCurrentUser() & _
    "|" & cstrModule & "|cmdOpenReport_Click|" & strErrorNum & "|" & strErrorDsc
    Resume Exit_cmdOpenReport_Click
    
End Sub

...

Private Function BuildFilter() As Variant
On Error GoTo Err_BuildFilter

    Dim varWhere As Variant
    Dim varLabs As Variant
    Dim varSuppliers As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    varLabs = Null  ' Subfilter used for Labs
    varSuppliers = Null  ' Subfilter used for Suppliers
    
    ' Check for LABS in multiselect list
    ' --------------------------------------------------
    For Each varItem In Me.lstLabs.ItemsSelected
        varLabs = varLabs & "[LabCode] = """ & _
        Me.lstLabs.ItemData(varItem) & """ OR "
    Next
    
    ' Check for SUPPLIERS in multiselect list
    ' --------------------------------------------------
    For Each varItem In Me.lstSuppliers.ItemsSelected
        varSuppliers = varSuppliers & "[Supplier] = """ & _
        Me.lstSuppliers.ItemData(varItem) & """ OR "
    Next
    
    ' Test to see if we have subfilter for LABS...
    ' --------------------------------------------------
    If IsNull(varLabs) Then
        ' do nothing
        Else
        ' strip off last " OR " in the filter
        If Right(varLabs, 4) = " OR " Then
            varLabs = Left(varLabs, Len(varLabs) - 4)
            varLabs = "(" & varLabs & ")"
        End If
    End If

    ' Test to see if we have subfilter for SUPPLIERS...
    ' --------------------------------------------------
    If IsNull(varSuppliers) Then
        ' do nothing
        Else
        ' strip off last " OR " in the filter
        If Right(varSuppliers, 4) = " OR " Then
            varSuppliers = Left(varSuppliers, Len(varSuppliers) - 4)
            varSuppliers = "(" & varSuppliers & ")"
        End If
    End If
    
    ' Put LABS and SUPPLIERS together in the string
    ' --------------------------------------------------
    If IsNull(varLabs) Then 'so, no Labs selected
    
        If IsNull(varSuppliers) Then ' neither Lab nor Supplier chosen
            varWhere = ""
        Else ' no labs, but SUPPLIERS were selected
            varWhere = " WHERE " & varSuppliers
        End If
        
    Else 'ok, so labs ARE selected
        
        If IsNull(varSuppliers) Then ' but SUPPLIERS are NOT
            varWhere = " WHERE " & varLabs
        Else ' both labs AND suppliers are selected
            varWhere = " WHERE " & varLabs & " AND " & varSuppliers
        End If
    End If
    
    ' Add varCostRange to the filter string
    ' --------------------------------------------------
    BuildFilter = varWhere '& varCostRange
    ' --------------------------------------------------

Exit_BuildFilter:
    Exit Function

Err_BuildFilter:
    strErrorNum = str(Err.Number)
    strErrorDsc = Err.Description
    Msg = "Error # " & strErrorNum & Chr(13) & " (" & strErrorDsc & ")" & _
    Chr(13) & " in: " & cstrModule & " | BuildFilter"
    MsgBox Msg, vbOKOnly, fstrDBname & ": Error"
    'log the error message to the external file referenced in modFunctions
    fErrorLog "--" 'separates error events
    fErrorLog "--|" & Now() & "|" & fstrDBversion() & "|" & GetAccessVersion & "|" & fstrCurrentUserID() & ": " & fstrCurrentUser() & _
    "|" & cstrModule & "|BuildFilter|" & strErrorNum & "|" & strErrorDsc
    Resume Exit_BuildFilter

End Function
Then the report opens and uses the grouping/sorting options to format the report. this formatting has certain fields on top of one another and depending on the group/sort the user has chosen, these fields will be either visible or not to make the report look right:

Code:
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
    
    Dim szGroupBy As String
    Dim szOrderBy As String
    Dim f As Form_frmPrintQueue_Choices
    
    If bIsLoaded("frmPrintQueue_Choices") Then
        'Only do this if the frmMyUserChoiceForm is loaded
        'otherwise don't change the sort order set at report design time.
        Set f = Forms!frmPrintQueue_Choices
        
        Select Case f.fmeOrderBy.Value
            Case 1 ' Supplier
                szOrderBy = "[Supplier]"
            Case 2 ' Lab
                szOrderBy = "[LabCode]"
            Case 3 ' Product
                szOrderBy = "[Item]"
            Case 4 ' Unit Price
                szOrderBy = "[UnitPrice]"
            Case 5 ' Total Cost
                szOrderBy = "[Cost]"
        End Select
        
        Select Case f.fmeGroupBy.Value
            Case 1 ' none (make group by same as order by), hide group header
                szGroupBy = szOrderBy
                Me.GroupHeader0.Visible = False
                Me.lblLabCode.Visible = True
                Me.txtLabCode.Visible = True
                Me.lblLabCostCode.Visible = True
                Me.txtLabCostCode.Visible = True
            
            Case 2 ' Supplier
                szGroupBy = "[Supplier]"
                Me.GroupHeader0.Visible = True
                Me.txtGroupTitle_Supplier.Visible = True
                Me.lblSupplier.Visible = False
                Me.txtSupplier.Visible = False
                Me.txtGroupTitle_Lab.Visible = False
                Me.txtGroupTitle_LabCostCode.Visible = False
                Me.lblLabCode.Visible = True
                Me.txtLabCode.Visible = True
                Me.lblLabCostCode.Visible = True
                Me.txtLabCostCode.Visible = True
                Me.lblCC.Visible = False
            
            Case 3 ' Lab
                szGroupBy = "[LabCode]"
                Me.GroupHeader0.Visible = True
                Me.txtGroupTitle_Lab.Visible = True
                Me.txtGroupTitle_LabCostCode.Visible = True
                Me.lblLabCode.Visible = False
                Me.txtLabCode.Visible = False
                Me.lblLabCostCode.Visible = False
                Me.txtLabCostCode.Visible = False
                Me.lblCC.Visible = True
        
        End Select
        
        Me.GroupLevel(0).ControlSource = szGroupBy
        Me.OrderBy = szOrderBy
        Me.OrderByOn = True
        Set f = Nothing
    
    End If

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    strErrorNum = str(Err.Number)
    strErrorDsc = Err.Description
    Msg = "Error # " & strErrorNum & Chr(13) & " (" & strErrorDsc & ")" & _
    Chr(13) & " in: " & cstrModule & " | Report_Open"
    MsgBox Msg, vbOKOnly, fstrDBname & ": Error"
    'log the error message to the external file referenced in modFunctions
    fErrorLog "--" 'separates error events
    fErrorLog "--|" & Now() & "|" & fstrDBversion() & "|" & GetAccessVersion & "|" & fstrCurrentUserID() & ": " & fstrCurrentUser() & _
    "|" & cstrModule & "|Report_Open|" & strErrorNum & "|" & strErrorDsc
    Resume Exit_Report_Open

End Sub
Attached Images
File Type: png report grouping and sorting user options.png (15.8 KB, 162 views)
File Type: png report grouping sorting design.png (18.6 KB, 162 views)

__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 07-27-2015, 10:54 PM   #19
mlahajnar
Newly Registered User
 
Join Date: Jul 2015
Posts: 28
Thanks: 3
Thanked 0 Times in 0 Posts
mlahajnar is on a distinguished road
Re: Grouping

Quote:
Originally Posted by wiklendt View Post
so say you have DueDate as a field in your tasks table. make a query with a calculated field using nested datediffs or dateserials, something like (disclaimer: aircode)
Code:
Urgency: iif([DueDate]=Date(),"Today",iif([duedate-dateserial(month(date()),year(date()),"This month")
...etc

you'll end up with a result like:

DueDate | Urgency | Task
25/7/15 | Today | Walk the dog
25/7/15 | Today | Write that code
31/7/15 | This month | Clean the windows.

you can then either present the query in a subform datasheet view or even a continuous form with the "Urgency" field displayed. or you can base a report on this query and group by the Urgency field to have them split up like in outlook.

The groupings in the report can be multilevel, so you can start with "project", then "user" then "urgency"... or whatever. Here i have the received date for our lab purchases both by year and by month. i'm sure with a little fidling you could figure out how to get your urgency in a similar way.
I think this may work. Thank you very much, I'll post if i succeed.

mlahajnar is offline   Reply With Quote
Reply

Tags
access , date , grouping , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
rpt grouping slimjen1 Reports 1 04-28-2009 06:01 PM
Grouping BondiBabe Reports 5 03-06-2009 03:39 AM
grouping vivian Reports 0 10-20-2005 12:08 PM
Help With Grouping CCIDBMNG Reports 3 10-25-2004 12:33 PM
Grouping or something? smask Forms 2 01-29-2003 08:49 AM




All times are GMT -8. The time now is 08:46 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World