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