Variable grouping by dates

[tallenwa]

I'm trying to write a query/report combination that will group my data by distinct Vessel and dates that are within 20 days of each other. So if I have 8 Vessels, the end result would group the vessels into three groups and sum them. If I could figure how to group them this way, I can easily get the sum in the report.
So for example, assuming a 20 days grouping of 8 Vessels, we have the following:

Group 1: Vessel A B/L Date 01/01/02 - $100,000
Vessel A B/L Date 01/15/02 - $1,000,000

Group 2: Vessel A B/L Date 01/31/02 - $2,000,000
Vessel A B/L Date 02/01/02 - $1,000,000
Vessel A B/L Date 02/10/02 - $2,000,000
Vessel A B/L Date 02/15/02 - $1,000,000

Group 3: Vessel A B/L Date 03/31/02 - $2,000,000
Vessel A B/L Date 03/10/02 - $1,000,000

The earliest date for Group 1 is 01/01/02. Add 20 days to this date and the maximum date it could group the data is 01/21/02. So the row with 01/31/02 B/L date would fail and it becomes the earliest date for the next grouping. Add 20 days to this new date and the maximum date it would group is 02/19/02. So then the row with 03/31/02 date would fail and it becomes the earliest date for the next grouping. And so on....

So finally the report will sum up the data for each group, and it will always display the "earliest" B/L date for each group and you'll get only 3 lines:

Vessel A B/L date 01/01/02 $1,100,000
Vessel A B/L date 01/31/02 $6,000,000
Vessel A B/L date 03/31/02 $3,000,000

Thanks for your help,
Travis

 

[PaulBricker]

Travis, the issue I see here is getting the first date. Is there something that determines what date 1/1/02 is going to be. Is it always the first of the year. If it is then the rest should be simple. Put this function in a module.

Function myGroup(myDte As Date)
Dim i As Integer
i = DateDiff("d", #1/1/2002#, myDte)
myGroup = "Group " & Int(i / 20) + 1
End Function

Then in your query put this expression.
Grouping:myGroup([DateFieldName]

Paul

 

[tallenwa]

Thanks for the help.
No, the user will input a starting date and ending date (right now, I've just entered criteria in the in the query field while I try to get it to work).
Paul, do I have the function run before the query (as an event in the form), or within it? Can you tell me what the MyDte variable is?
Thanks again.

 

[PaulBricker]

The process starts in the query when you open it. As it formats a record, it passes the value of the Date Field to the column Grouping. At that point the value for the DateField is passed to the Function and the variable myDte just holds the value passed from the query. Then the function runs, and passed the return value, in this case the Group # back to the query as Group1, Group2 ....GroupN.
I'll look into passing a parameter to the Function.

Paul

 

[PaulBricker]

Travis, is case you were asking "What do I do with this?" here's how you use it. Put the function in a new module. Save it as anything except myGroup. funmyGroup would be fine. Then in a new column of your query put this

myGrouping:myGroup([DateFieldName])

I change the alias Grouping to myGrouping because Grouping is too close to a reserved word in Access.
If you are using a parameter query, post your parameter from the query and I will post back a solution to work with the parameters.

Paul

 

[tallenwa]

You've got me real close now, WAY closer than I was. The query calls the function and provides the grouping, but not quite like I need, so I'll try to clarify. Also, one piece of the puzzle that I over simplified was that the Vessel Name does not remain constant :-(. So my grouping is FIRST by Vessel name, then by the 20day. Also, the function you provided appears to group by a rolling 20 day. But the twenty day clock for the next group is triggered by the first date in that group. Does that make sense? If I have three BOL dates of 1/1/02, 1/15/02, 2/7/02, 2/10/02, 2/20/02; the second group needs to start (ie reset the beginning date) with 2/7/02 and would run through 2/27/02 (so it would include 2/7/02, 2/10/02, 2/20/02 in it's group).
Can you help me with a revised Function that will group by such a 20day method within specific Vessel names?

Travis

 

[PaulBricker]

this is either pure genius or pure madness. Anyway try it on a small sample of data. This requires a couple of things.
Fist, create a query. Add the Vessel field and the [B/LDate] field. Turn on the totals and groupby Vessel and select First for the Date field. Save this query as qryGroups.
Next, add this function to a module. Make sure the Field names for Vessel and [B/LDate] match your field names in the Totals query. The alias 'FirstOf' is appended to the name of your date field in the Totals query.

Function myGroup(mygrp As String, myDte As Date)
On Error GoTo ErrHandler
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim i As Integer
Dim dteVal As Date
Set rst = CurrentDb.OpenRecordset("qryGroups", dbOpenDynaset)
rst.MoveFirst
ErrHandler:
Resume Next
If rst!Vessel = mygrp Then
dteVal = rst![FirstofB/LDate]
i = DateDiff("d", dteVal, myDte)
myGroup = "Group" & Int(i / 20) + 1 & rst!Vessel
Else
rst.MoveNext
GoTo ErrHandler
End If
End Function

In your query with the information put this in a new column

myGrouping:myGroup(Vessel,[B/LDate])

Again, make sure Vessel and [B/LDate]) match the field names in your query.

Try it out and let me know.

Paul

 

[PaulBricker]

Function myGroup(mygrp As String, myDte As Date)

Dim rst As DAO.Recordset, rst2 As DAO.Recordset
After looking for a GoTo example, I finally found it. Use this function instead of the previous one. I use Error Handling to control the loop and I should not have.

Paul

Function myGroup(mygrp as String, myDte as Date)
Dim i As Integer
Dim dteVal As Date
Set rst = CurrentDb.OpenRecordset("qryGroups", dbOpenDynaset)
rst.MoveFirst
Line1:
If rst!Vessel = mygrp Then
dteVal = rst![FirstofB/LDate]
i = DateDiff("d", dteVal, myDte)
myGroup = "Group" & Int(i / 20) + 1 & rst!Vessel
Else
rst.MoveNext
GoTo Line1
End If
End Function

 

[tallenwa]

Paul, your patience is beyond what I could ask.
I've got the query built and the equation is:
my grouping: myGroup([Vessel],[B/LDate])
I have the Total Set to "Expression"
The Total for [Vessel] is set to Group By, and the Total for B/LDate is set to First.

I cut and pasted the module that you sent:
Function myGroup(mygrp As String, myDte As Date)
Dim i As Integer
Dim dteVal As Date
Set rst = CurrentDb.OpenRecordset("qryGroups", dbOpenDynaset)
rst.MoveFirst
Line1:
If rst!Vessel = mygrp Then
dteVal = rst![FirstofB/LDate]
i = DateDiff("d", dteVal, myDte)
myGroup = "Group" & Int(i / 20) + 1 & rst!Vessel
Else
rst.MoveNext
GoTo Line1
End If
End Function

When I run the query the following error appears: "You tried to execute a query that doesn't include the specified expression 'myGroup(Test.Vessel,[B/LDate])' as part of an aggregate function."


The sample data that I'm using in the Test table is:
Vessel B/LDate TIV
ADMIRAL 6/8/94 $29,779.00
ADMIRAL 11/23/94 $65,724.00
ADMIRAL 5/11/95 $30,272.00
ADMIRAL 6/22/95 $17,633.00
ADMIRAL 9/14/95 $22,174.00
ADMIRAL 5/23/96 $64,255.00
ADMIRAL 5/23/02 $41,135.58
ADMIRAL 5/23/02 $43,864.68
ADMIRAL 6/27/02 $88,073.69
ADMIRAL 6/27/02 $10,424.13
AKASHI BRIDGE 2/27/02 $24,647.80
AKASHI BRIDGE 2/27/02 $4,565.61
AKASHI BRIDGE 2/28/02 $9,893.04
AKASHI BRIDGE 4/3/02 $10,894.54
AKASHI BRIDGE 4/4/02 $38,311.64
AKASHI BRIDGE 5/19/02 $300,897.00
AKASHI BRIDGE 5/21/02 $338,225.00
AKASHI BRIDGE 5/21/02 ($3,107.00)
AKASHI BRIDGE 6/19/02 $6,119.94

I can give you a call if you'd like, or take this offline, let me know.
Travis

 

[PaulBricker]

OK you are very close. Sorry but this forum doesn't have an edit feature and when I posted the code before I messed it up so the code needs a little work. This is correct.

Function myGroup(mygrp As String, myDte As Date)
Dim rst As DAO.Recordset
Dim i As Integer
Dim dteVal As Date
Set rst = CurrentDb.OpenRecordset("qryGroups", dbOpenDynaset)
rst.MoveFirst
Line1:
If rst!Vessel = mygrp Then
dteVal = rst![FirstofB/LDate]
i = DateDiff("d", dteVal, myDte)
myGroup = "Group" & Int(i / 20) + 1 & rst!Vessel
Else
rst.MoveNext
GoTo Line1
End If
End Function

Now to run this, you need two queries to start with. qryGroups is just the two fields, Vessel and B/LDate. Turn on the Totals, Group By Vessel and set B/LDate to First. What this gives us is something to compare our data in the second query against. The second query will have at least 3 fields, and more if you want.
Vessel, B/LDate and TIV. We don't turn on any totals in this query. It's in this query that you put the call to the Function. mygrouping:myGroup(Vessel,[B/LDate])


That should do it.

Paul

 

[tallenwa]

Paul,
Thank you so much. I've been messing with this thing all day. I removed the "Firstof" from your earlier code (dteVal = rst![FirstofB/LDate]) and it appears to WORK! I've added the TIV to the original query and Sum on it, and it appears to work. I'm going to increase my test table to check it some more.

 

[PaulBricker]

Let me know how you make out but it sounds like your on the right path. Double check the 20 day group and be sure it gets the correct numbers in the correct group.

Paul

 

[tallenwa]

Couldn't have come close w/o your help. I may have to break it into 2 queries as it locks up when I put it to task with a record set of 400+. Thanks again for your help, hopefully you won't here from me again.
Have a good weekend! -Travis

 

[PaulBricker]

Not a problem Travis. I just ran it against a small set of data but I'm a little surprised that 400 records would lock it up. Post back if you have any questions.

Paul

 

[PaulBricker]

Travis, I created a table with 500 records in it and the Function ran without incident so if you are having problems, it shouldn't be related to the Function. Let me know what's happening and lets see if we can figure out why there's an issue. When your ready.

Paul

 

[tallenwa]

You've got it working for me. I had a data error that was breaking the code.
Thank you very much and Merry Christmas!

 

[PaulBricker]

Great. Merry Christmas to you.

Paul