Variable Grouping by Date

[tallenwa]

I need help writing a module that will sum the total insured value (TIV) for shipments incured within 20days. The module needs to look at the first vessel and set the BOL date for the grouping to 20days beyond. Once the 20day grouping is done, the next date becomes the starting date for the 20day cycle. IE, it must reset the 20day grouping per the next BOL date.

Here is a sample data set (20 day vessel Table):

Vessel BOL TIV
HANJIN 3/26/2004 $10
HANJIN 4/26/2004 $20
HANJIN 4/26/2004 $30
HANJIN 5/10/2004 $40
HANJIN 5/20/2004 $50
HANJIN 5/25/2004 $60
SMITH 3/10/2004 $10
SMITH 4/26/2004 $20
SMITH 4/30/2004 $30


The grouping would be:

Vessel BOL TIV
HANJIN 3/26/2004 $10

HANJIN 4/26/2004 $20
HANJIN 4/26/2004 $30
HANJIN 5/10/2004 $40

HANJIN 5/20/2004 $50
HANJIN 5/25/2004 $60

SMITH 3/10/2004 $10

SMITH 4/26/2004 $20
SMITH 4/30/2004 $30

The final report, or query, would show the ship name and starting BOL and the sum of the TIV. The results would be like this:
HANJIN 3/26/2004 $10
HANJIN 4/26/2004 $90
HANJIN 5/20/2004 $110
SMITH 3/10/2004 $10
SMITH 4/26/2004 $50

I have created a query (20 day vessel summary report query1)that finds the first BOL date for each vessel. Then in a query based on the 20 day vessel table, I call the myGroup3 function. I loop through the module comparing my data to this record set. The problem is that I don't know how to reset the 20 day clock once I'm out of range.
Any help would be greatly appreciated. Here is the module that I've got so far.

Function myGroup3(mygrp As String, myDte As Date) As String
Dim rst As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim dteVal As Date
Set rst = CurrentDb.OpenRecordset("20 day vessel summary report query1", dbOpenDynaset)
rst.MoveFirst
Do While Not rst.EOF
If rst!Vessel = mygrp Then
dteVal = rst![FirstofBOL]
i = Abs(DateDiff("d", dteVal, myDte))
myGroup2 = rst!Vessel & " Group" & Int(i / 20) + 1
End If
rst.MoveNext
Loop
End Function


Thanks for any help you can provide.
Travis

 

[tallenwa]

Bump.
Any help out there would be greatly appreciated.

 

[JonFer]

You can use something like this to generate a 20-day grouping number for each ship:

Select Ship,
Int([BOL]-(Select Min(a.BOL) from Table1 as a
where a.Ship=table1.Ship)/20) as 20DayGrooup
From Table1

BOL dates 0-19 days from the first BOL date for a ship will be group "0", BOL dates 20-39 days beyond the first BOL date will be group "1", etc.

 

[tallenwa]

Hey, I really appreciate the response. That code greatly simplifies what I had before. But it didn't reset the 20day cycle based on the next date. I think I found someone who can help me crack this nut. I'll have to test their input, but here's the code they provided:

Option Compare Database
Option Explicit
Dim strHoldVessel As String
Dim dteHoldBOL As Date
Dim intHoldGroup As Integer


Public Function GroupDates(ByVal strVessel As String, ByVal dteBOL As Date) As Integer
If strHoldVessel = "" Then
intHoldGroup = 1
strHoldVessel = strVessel
dteHoldBOL = dteBOL
Else
If strHoldVessel = strVessel Then
If dteBOL > (dteHoldBOL + 20) Then
intHoldGroup = intHoldGroup + 1
dteHoldBOL = dteBOL
End If
Else
strHoldVessel = strVessel
dteHoldBOL = dteBOL
intHoldGroup = 1
End If
End If
GroupDates = intHoldGroup
End Function