Access Looping? and calculating Gaps

[AVILA]

Possibly someone may ?

Here is my problem. I have a list of members who show up multiple times. My goal is to (1) get the GAPS between "FrmDate" and "ThruDate" by Unique MembID. (2)Tag each unique MembID with 1 or 2 for members withs gaps greater than 45 days.

In example below MembID "1234" would not qualify. So this Membid would have an "0" or "N"

*IS NULL = Still Acitve
-------------------------------------------____RESULTS_____
Membid.....FrmDate...........ThruDate........GAP.....Qualify
1234..........01/01/02..........06/30/03...........0....... 0 or N
1234..........07/15/03..........07/30/03........16........ 0 or N
1234..........08/01/03..........12/31/03...........1....... 0 or N
1234..........02/15/04..........*IS NULL.........45....... 0 or N

9876..........01/01/02..........06/30/03...........0....... 1 or Y
9876..........07/02/03..........*IS NULL...........1....... 1 or Y

4321..........01/01/02..........*IS NULL...........0....... 1 or Y
4321..........07/01/03..........*IS NULL...........0....... 1 or Y

6789..........01/01/02..........06/30/03...........0....... 1 or Y
6789..........01/01/03..........*IS NULL......-180....... 1 or Y


Additional Info: I have everthing in SQL Server and can bring into Access, Excel or Stay in SQL.

Any suggestions would be greatly appreciated!
Thanks!

 

[Cbrine]

I would say in MS Access you want to create a query with the Member ID, Min of FromDate and Max of ToDate. Save this query, and setup a second query accessing the first, and build a custom formula to do iff(DateDiff("D',MinOfFromDate,Maxoftodate)>45,Y,N) I'm not sure about catching the Null value issue, it might casue some problems.
Hope this helps.

 

[bat17]

I am not sure that I can follow the logic that you are using to calculate the gap between "FrmDate" and "ThruDate".
However if Null needs to be calculated with todays date you could use the nz() function to do it.
gap = [frmDate]-nz([ThruDate],Date())

I would also score the other way around so that a good result is 0 and a bad = 1, then you can use a grouping query and sum on the scoring field, any score other than 0 will be a "NO"

HTH

Peter