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.