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