Grouping

[mlahajnar]

Hello

Just registered so don't be too angry if I missed any rules or anything, and English isn't my native language, so there could be some mistakes.

So I got this summer job at a bigger company and my task for now is to make an access database with forms, that contain tasks for employees. Whole reason for this will be, that they have quick access to them on meetings. One request I got from an employee is, that he wants to have data sorted in forms by various categories (which ever he clicks on), like in Ms Outlook, where you can press the category, for example date, and it will sort it like today, tomorow, next week, this month. So I already figured out that this will not be possible in forms (he doesn't like the subform option).

Now what my question here is, is it possible to do something like this in reports, I have already figured out that you can group in them, just have 0 idea how to do it like this.

Thanks in advance

 

[plog]

So I already figured out that this will not be possible in forms

That's untrue. The same method you would use in a Report would work in a form. The problem is, if you have 0 idea how to do this, you probably don't have the requisite skills to do this on your own.

I'll give you the broad strokes, but you really need to up your Google abilities and start working through some tutorials. To do what you want you would make your report, then on the column headers you would add OnClick events. Those events would determine which column header was clicked, compile a sort command and apply it to the report.

Again, I really don't think that's helpful to you because your skill level isn't enough to translate that into specific actions to take to achieve this. I'd ask your employer for some formal Access training or go get some books.

 

[mlahajnar]

You kinda understood me wrong.

I know how to work with events, I know how xxxx_Click() works and all, I've created a database with forms much more complicated than that.

But here is the problem:

I have a database, that on startup opens a dialog form where you select your name (not very safe, but they said that wasn't a problem). Then another form opens, where you can see your task, and if you are responsible for a project or you are admin of the db, 2 more tabs are shown. But now, one of my coworkers wants to have it grouped like this

Tasks

--Today
Task1
task2...
--Tomorow
Task3
Task4...
--This week
Task5...
--This month
Task6...

He wants it grouped like that, with the "Today" and others written. I googled alot and all I could find is, that you can't really group like that in forms, or you kinda can with subforms, but my coworker doesn't want that.

So by any means, I'm just looking for help, I don't need you to write the whole code here for me, all I need is some help regarding the Report grouping and how to modify it to meet my needs.

So just say what ever comes to your mind, I'll probably understand it.

 

[spikepl]

Your grouping is grouping by what? That is not at all evident.

Make a query where you in the first column have whatever it is your day/week/month is , and the task name in the second column. Once you have that play with the Grouping option in the Report (not the query - that will not work). You can than physically drag the textbox holding the group name to the header of the group - work in the design view - and thus get what you want.

 

[mlahajnar]

I will have the possibility to group by date, person, project, subproject, status (competed/not completed) and so on, and the user will decide which one he wants by clicking on the header of the report. I tried playing a bit with the grouping in reports yeah, then I went to googling. I don't think it will be hard grouping by other categories, haven't tried yet, but date is the one that concerns me the most. I tried it, it gave me the option; day, month, year, and I couldn't quite figure it out.

Anyway, the team I'm working with has 2 weeks of "forced" vacation, starting next week and I can come to work during this period, so I will probably just be trying out stuff and testing various scenarious that might happen with the database.

I'll try to get a screenshot how the sort looks in outlook, before I go home today (cca 15 min).

 

[mlahajnar]

Ok tried to post it, but figured out you need 10 posts to be able to post images. I guess I'll post it later when I will be able to

 

[plog]

I will have the possibility to group by date, person, project, subproject, status (competed/not completed) and so on, and the user will decide which one he wants by clicking on the header of the report.

Not possible. You can change sorting in a report, but changing the grouping requires a whole new report.

My advice would be to make all the reports you need (1 for each grouping you want to do), then build a form which allows the user to select the one he wants. He clicks a button and the correct report opens.

 

[plog]

Thinking more on this, I think it might be able to make it appear to the user that the reports function this way.

You would still make all those different grouping reports and then when the user clicks a column header it closes the current report and opens the one he selected.

 

[mlahajnar]

Ok, so we got that out of the way, altho I wasn't that far into the thing
First I wanted to make sure that the grouping is possible.

Now we are still left with the grouping by date. When I played with it a bit, i found out you could sort by day, month, year. But is it possible to make custom grouping rule, so you name the header, and then by SQL or someway decide which records belong into that group?

 

[mlahajnar]

Ok another update

I managed to find the way, how to group by a rule (Stupid me, I don't know, how I didn't see it before), so I tried the most basic one "[DateDue] = Date()" for the Today group. And I got it to work. Here is the catch; the group name was "0" and "1", as for true/false, I set the name "Today" in the option to change name of the group, didn't do anything, I guess I'm doing something wrong.

Ok so I decided that the names won't bother me for just now, so I decided to try the second rule, which i thought should be "Tomorrow". So got the rule ready when i remembered that it will do a subgroup. So that won't work, unless there is a way around it. Here this forum comes in, as you are alot more experienced in this field.

So thanks for the help.

 

[mlahajnar]

The more I go deep into this, the more I realize that it's really hard to make or even impossible (not sure about this one). Might aswell tell my coworker that this won't be really possible. And I'm really not sure why he even wants it, i mean i understand it would be practical, but with just a little more work he can do the same in the search that I created, which can search tasks for this week or for a period between 2 dates.

I don't know, whats your opinion?

 

[wiklendt]

I disagree that you can't change grouping in a single report. I have done it. As for dates, I would try datediff() perhaps coupled with an option group, I've done something similar but not quite what your colleague had in mind - though with a little fiddling you might be able to work it out.

Edit: can't quite figure how it's going to be possible in a form without subforms but maybe explain to him that outlook is almost subformy anyway...\

Edit2: actually, my grouping changes in reports was generating an existing report and changing the grouping from the last time it was generated, not having varied groupings in the one instance of the report.

 

[mlahajnar]

Disregard that about subforms, I gave wrong information. Before he had his idea, I made it so, that there was a form, which had shown all the projects, where specific user had tasks in, this was done so the user had more info on the project and in the footer of this form, there was a subform that showed tasks for that project and that user. He didn't like that (to be honest, neither did I) becouse it wasn't very nice to look at (i dont know how to say it differently xD)

 

[wiklendt]

but, the reports do allow you to have various groups. not sure if these can be limited on various dates from one query... worth a try.

another thing worth trying: have a calculated field in a query which returns "today" "this week" etc... depending on datediff in a a nesedt if?

 

[mlahajnar]

Hmm, can you kinda point me into the right direction about the various groups in a report?

 

[plog]

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).

 

[wiklendt]

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)

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.

 

[wiklendt]

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

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:

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

 

[mlahajnar]

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)

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.