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.
Code:
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.
