How do I add fields if dates are within 20days of each other

[tallenwa]

I'm trying to build a query that sums the value in one field if the "BOLdate" value is within +or-20 days of each other, when it's the same ship. I tried to do this within a report or the underlying query, but haven't had any luck. I can sort and group by month, but don't know how to have the query or report "compare" the ships and the dates.

Table:
Ship BOLdate Value
A 01/01/01 5
A 01/15/01 10
B 01/20/01 20
A 02/25/01 25

The query would then return three lines:
Ship Value
A 15
B 20
A 25

Thanks.

 

[Guest_imported]

Try

EXP1: mid(

.[field],3,2)

that might be it... But play with the mid command

 

[tallenwa]

Sorry, I used a poor choice for the dates. I'm not trying to extract the "day". I'm trying to sum the value field of the Table if the ship is the same and the BOLdates are within 20days of each other.

 

[MichaelRed]

not at all sure this will work unless there are additional criteria. Take the situatiom where three dates (same ship) 1st, 15th and 30th of some month. Each pair ie eithin the criteria, but the triad ie not. Would you have the 15th sunmmed with the first and the 30th, or how ELSE would you decide how to cope w/ it? W/o some direction, you cannot get useful results.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ia3p]

the only advise I can give you is select the table first by ship

then in a loop order by shipdate and check the difference and add.

This may help but I am not sure you can doit in a single query unless you play with aliases and that is a headache

 

[tallenwa]

I'm not very good at writing SQL, that's why I was hoping to do it in a query or report. In answer to Michael's good question, the 20days is around the first date of the group.

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

NOTE that the Vessel name has to be exact otherwise the B/L date is reset as the earliest date for the new grouping. Hope this makes sense.

I appreciate your help