Query to count groups of dates

[DMBLuva]

Hi All,
I'm trying to figure out if this is possible and I've gone round and round with the possible scenarios in and out of the access query. What I need to do is find the number of days between a submittal and an approval of a amendment. Now seems easy, but there's a catch, there could be more than one amendment per grant and there can be more than one submittal date if it's rejected. This means that I can't just count the first submittal date and the last submittal date in my calculations. To complicate things more, the amendments do not have a unique number given to them so I can't use that to tell access when to start a new calculation. So is there is a sql I could write that would work? I've tried max and min dates but again it won't be accurate as the first submittal date might not be the same.
So is this doable? Maybe Sql? Or will I have to do this by hand you think?

Here's what kind of data I'm looking at:

date EventComment EventID
2/14/2012 SYSTEM:Submit 30103
2/16/2012 SYSTEM:Reject 30306
2/22/2012 SYSTEM:Submit 30103
2/28/2012 SYSTEM:Approve 30308
3/30/2012 SYSTEM:Submit 30103
4/12/2012 SYSTEM:Approve 30308

Any help is appreciated. THANKS!

 

[dhookom]

DMBLuva,
Can you at least provide the answer given your sample data as well as the reason why?

Duane
Hook'D on Access
MS Access MVP

 

[DMBLuva]

I apologize. Ok so here is why. I need to count the days between when amendments are submitted and when they are approved. My sample data above shows that there could be (multiple) rejections and submissions before an approval is given. Basically, I need to weed those out and only count the original submission and approval for each amendment submitted. An amendment is tied to a projectid, but the amendments themselves are not given a unique number to filter by. So the run down is this - a project id can have more than one amendment and an amendment can be submitted and rejected more than one time before it's approved.

So based on my sample data -

ProjectID date EventComment EventID
1234 2/14/2012 SYSTEM:Submit 30103
1234 2/16/2012 SYSTEM:Reject 30306
1234 2/22/2012 SYSTEM:Submit 30103
1234 2/28/2012 SYSTEM:Approve 30308
1234 3/30/2012 SYSTEM:Submit 30103
1234 4/12/2012 SYSTEM:Approve 30308
To:
ProjectID SubmitDate ApproveDate
1234 2/14/2012 2/28/2012
1234 3/20/2012 4/12/2012
If this formatting is not possible, I'll take it the original way with each row being a date.

I hope that helps to clear it up. I'm not even sure if it can be done. I was thinking of a loop where it would start at the first submission date and end at the approval date and start over if the approval eventid count for the project is greater than 1, but I don't even know where to start there.
I appreciate you taking a look at this.

-Sarah

 

[dhookom]

There was no submit on 3/20/2012.

Duane
Hook'D on Access
MS Access MVP