Grouping and Sorting questions

Welcome Guest! To enable all features please Login or Register.

RaymondC
Advanced Member Topic Starter

11 years ago

I will use a very simple example. (using Access 2010)

Imagine that we have a table of Customers and another table of Invoices. I want to create a grid that groups by Customer (the “group row” method) and then shows all the Invoices for that Customer.

The Customer table has a CustomerID field and a CustomerName field. The CustomerID field is unique for each Customer but it is possible that more than one Customer has the same name. To make certain that each group is properly listing the Invoices for only one Customer, I want to group by the CustomerID field; however, I want to sort the groups alphabetically by CustomerName.

#1) How do I group by CustomerID but sort by CustomerName?

#2) How do I display the CustomerName and not the CustomerID in the group row?

Igor/10Tec
Administration

11 years ago

I have been thinking on your problem for many days, but it seems it has no simple solution - at least, in the form of the original specification. The main problem is that you need to show customer names which may be duplicated for different id's. And there is a question for you related to this: how will your customer distinguish two different customers with the same name?

A feasible solution I suggest is to display the customer id in addition to its name in group rows/cells. Then you can use cell dynamic contents for that. Here is my code that should explain the implementation of the idea much better:

Option Compare Database
Option Explicit

Private Const CUST_ID_COL As Long = 1
Private m_arrCustomerName(1 To 3) As String

Private Sub Form_Load()
    m_arrCustomerName(1) = "ABC Company"
    m_arrCustomerName(2) = "XYZ Company"
    m_arrCustomerName(3) = "ABC Company"

    Dim grd As iGrid
    Set grd = iGrid0.Object

    With grd
        .BeginUpdate

        .ColCount = 3
        .RowCount = 3

        .CellValue(1, CUST_ID_COL) = 1
        .CellValue(1, 2) = 111
        .CellValue(2, CUST_ID_COL) = 2
        .CellValue(2, 2) = 222
        .CellValue(3, CUST_ID_COL) = 3
        .CellValue(3, 2) = 333

        .ColSortType(CUST_ID_COL) = igSortByCellTextNoCase

        .GroupObject.AddItem CUST_ID_COL, igSortAsc, igSortByCellTextNoCase
        .Group

        .EndUpdate
    End With
End Sub

Private Sub iGrid0_CellDynamicText(ByVal lRow As Long, ByVal lCol As Long, ByVal vValue As Variant, sText As String)
    If (lCol = CUST_ID_COL) Then
        sText = m_arrCustomerName(vValue) & " (" & vValue & ")"
    End If
End Sub

This is the full form module of the sample I was playing with. The array m_arrCustomerName is used to get the customer name by its id (just for a sample).

RaymondC
Advanced Member Topic Starter

11 years ago

Thank you for taking the time to prepare a possible solution.

I had actually considered doing what you suggest. The problem is that the CustomerID value would become part of the text used for sorting and since CustomerName can include all sorts of characters like parenthesis and numbers then the sorting order is not guaranteed to be correct.

I have another idea.

I use a recordset object in Access to gather the records for the grid. I could pre-sort my recordset query with "ORDER BY CustomerName" and then use the AbsolutePosition property of the recordset to group. The AbsolutePosition property is unique for each row in the recordset and would be in the correct sort order so I think it would work. The only issue that concerns me is that I would have to iterate through the recordset in a loop and populate every single cell as opposed to using the very simple FillFromRS method. I'll test and see if it is much slower.

Changing the text of the group row to display the CustomerName can be solved as you demonstrated.

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close