Group tables (1 Viewer)

[Kepcla72]

Hi Guys, I'm trying to group a table, but it´s impossible for me...

I have this table:

Car	From	To	Color
Ford	1​	2​	Red
Ford	2​	3​	Red
Ford	4​	5​	Blue
Ford	6​	7​	Blue
Ford	8​	9​	Blue
Ford	10​	11​	Blue
Ford	12​	13​	Red
Ford	14​	15​	Red
Ford	16​	17​	Green
Ford	18​	19​	Green
Ford	19​	20​	Blue
Ford	21​	22​	Blue
Ford	23​	24​	Blue

And I need to achieve this:

Car	From	To	Color
Ford	1​	3​	Red
Ford	4​	11​	Blue
Ford	12​	15​	Red
Ford	16​	19​	Green
Ford	20​	24​	Blue

I appreciate a lot your help.
Regards
K

 

[bob fitz]

EDITED
How do you arrive at the values you have in the "From" and "To" columns of the second diagram

 

[Gasman]

DMAX() ?

 

[Uncle Gizmo]

I think this might do what you want - See Attached:-

 

[jdelano]

I was thinking that because the colors aren't grouped (so they aren't looking for the min/max) I used VBA on a form to group the records into another table.

Option Compare Database

Private Sub Command1_Click()
    
    Dim carTable As Recordset
    Dim groupedTable As Recordset
    
    Dim currentColor As String
    Dim previousToQTY As Integer
    Dim previousCar As String
    Dim firstFromQty As Integer
    Dim lastToQTY As Integer
    
    ' get the records from the car table
    Set carTable = CurrentDb.OpenRecordset("Select * from tblCar")
    
    ' clear the grouped table
    CurrentDb.Execute "Delete * from tblCarGrouped"
    
    ' opent the grouped table
    Set groupedTable = CurrentDb.OpenRecordset("Select * from tblCarGrouped")
    
    Do While Not carTable.EOF
        If currentColor <> carTable("Color") Then
            ' a different car has been reached
            
            If currentColor <> "" Then
                ' the loop has gone through at least 1 row
                ' write the group to the group table
                groupedTable.AddNew
                groupedTable("Car") = previousCar
                groupedTable("FromQty") = firstFromQty
                groupedTable("ToQty") = previousToQTY
                groupedTable("color") = currentColor
                groupedTable.Update
            End If
            
            firstFromQty = carTable("FromQty")
            currentColor = carTable("Color")
        End If
        
        previousToQTY = carTable("ToQty")
        previousCar = carTable("Car")
        
        carTable.MoveNext
    Loop
    
    ' write the last grouping to the group table
    groupedTable.AddNew
    groupedTable("Car") = previousCar
    groupedTable("FromQty") = firstFromQty
    groupedTable("ToQty") = previousToQTY
    groupedTable("color") = currentColor
    groupedTable.Update
    
    ' close and release the recordsets
    carTable.Close
    Set carTable = Nothing
    
    groupedTable.Close
    Set groupedTable = Nothing
    
    Me.Grouped_Data.Requery ' force the subform to display the new records
End Sub

You can add another level to the loop if the car changes as well.

 

[Pat Hartman]

Select Car, Min(From) as MinFrom, Max(To) as MaxTo, Color
From YourTable
Group by Car, Color;

 

[Kepcla72]

Appreciate a lot your help, the solution of [B]jdelano[/B] it works perfect!

 

[Kepcla72]

I was thinking that because the colors aren't grouped (so they aren't looking for the min/max) I used VBA on a form to group the records into another table.

Option Compare Database

Private Sub Command1_Click()
   
    Dim carTable As Recordset
    Dim groupedTable As Recordset
   
    Dim currentColor As String
    Dim previousToQTY As Integer
    Dim previousCar As String
    Dim firstFromQty As Integer
    Dim lastToQTY As Integer
   
    ' get the records from the car table
    Set carTable = CurrentDb.OpenRecordset("Select * from tblCar")
   
    ' clear the grouped table
    CurrentDb.Execute "Delete * from tblCarGrouped"
   
    ' opent the grouped table
    Set groupedTable = CurrentDb.OpenRecordset("Select * from tblCarGrouped")
   
    Do While Not carTable.EOF
        If currentColor <> carTable("Color") Then
            ' a different car has been reached
           
            If currentColor <> "" Then
                ' the loop has gone through at least 1 row
                ' write the group to the group table
                groupedTable.AddNew
                groupedTable("Car") = previousCar
                groupedTable("FromQty") = firstFromQty
                groupedTable("ToQty") = previousToQTY
                groupedTable("color") = currentColor
                groupedTable.Update
            End If
           
            firstFromQty = carTable("FromQty")
            currentColor = carTable("Color")
        End If
       
        previousToQTY = carTable("ToQty")
        previousCar = carTable("Car")
       
        carTable.MoveNext
    Loop
   
    ' write the last grouping to the group table
    groupedTable.AddNew
    groupedTable("Car") = previousCar
    groupedTable("FromQty") = firstFromQty
    groupedTable("ToQty") = previousToQTY
    groupedTable("color") = currentColor
    groupedTable.Update
   
    ' close and release the recordsets
    carTable.Close
    Set carTable = Nothing
   
    groupedTable.Close
    Set groupedTable = Nothing
   
    Me.Grouped_Data.Requery ' force the subform to display the new records
End Sub

You can add another level to the loop if the car changes as well.

It works Perfect!!!

 

[gemma-the-husky]

What does the from and to columns mean. It looks really strange.

 

[jdelano]

Nice, my issue is I see VBA as my hammer, and everything is a nail.

Appreciate a lot your help, the solution of [B]jdelano[/B] it works perfect!

You're welcome, happy to help.

 

[Cronk]

Those VBA solutions will work fine as long as only one make of car is used. If you only have one make (Ford) why bother having a field in your table?

 

[jdelano]

This handles if there could be a different car

Option Compare Database

Private Sub Command1_Click()
    
    Dim carTable As Recordset
    Dim groupedTable As Recordset
    
    Dim currentCar As String
    Dim currentColor As String
    Dim previousToQTY As Integer
    Dim previousCar As String
    Dim firstFromQty As Integer
    Dim lastToQTY As Integer
    
    ' get the records from the car table
    Set carTable = CurrentDb.OpenRecordset("Select * from tblCar")
    
    ' clear the grouped table
    CurrentDb.Execute "Delete * from tblCarGrouped"
    
    ' opent the grouped table
    Set groupedTable = CurrentDb.OpenRecordset("Select * from tblCarGrouped")
    
    Do While Not carTable.EOF
        If currentCar <> carTable("Car") Then
        
            If currentCar <> "" Then
                ' the loop has gone through at least 1 row
                ' write the group to the group table
                groupedTable.AddNew
                groupedTable("Car") = previousCar
                groupedTable("FromQty") = firstFromQty
                groupedTable("ToQty") = previousToQTY
                groupedTable("color") = currentColor
                groupedTable.Update
            End If
            
            firstFromQty = carTable("FromQty")
            currentColor = carTable("Color")
            currentCar = carTable("Car")
        
        End If
    
        If currentColor <> carTable("Color") Then
            ' a different car has been reached
            
            If currentColor <> "" Then
                ' the loop has gone through at least 1 row
                ' write the group to the group table
                groupedTable.AddNew
                groupedTable("Car") = previousCar
                groupedTable("FromQty") = firstFromQty
                groupedTable("ToQty") = previousToQTY
                groupedTable("color") = currentColor
                groupedTable.Update
            End If
            
            firstFromQty = carTable("FromQty")
            currentColor = carTable("Color")
        End If
        
        previousToQTY = carTable("ToQty")
        previousCar = carTable("Car")
        
        carTable.MoveNext
    Loop
    
    ' write the last grouping to the group table
    groupedTable.AddNew
    groupedTable("Car") = previousCar
    groupedTable("FromQty") = firstFromQty
    groupedTable("ToQty") = previousToQTY
    groupedTable("color") = currentColor
    groupedTable.Update
    
    ' close and release the recordsets
    carTable.Close
    Set carTable = Nothing
    
    groupedTable.Close
    Set groupedTable = Nothing
    
    Me.Grouped_Data.Requery ' force the subform to display the new records
End Sub

 

[ebs17]

Nice, my issue is I see VBA as my hammer, and everything is a nail.

At this point, a VBA solution like the one shown is quick and easy, and for most people it is easy to read and maintain. So this is a good option.

A query solution is of course also possible. You could proceed as follows, for example:
- Generate a sequentiell number to display the sequence shown
- For each group (blue Ford), the first record (no predecessor) and the last record (no successor) are determined. Predecessor and successor related to this sequentiell number.
- From this, you can then determine the From value and the To value.

Overall, however, this is a bit complex.

 

[Cronk]

This handles if there could be a different car

Without any order, your code assumes the data is ordered by Car, To and Color

Maybe

    Set groupedTable = CurrentDb.OpenRecordset("Select * from tblCarGrouped" By order Car,To,Color)

 

[jdelano]

True, that could be the Order By given the list looking at the example.