Determine if New or Repeat Client

[Jana]

Good morning, Access gurus!

I'm having a brain fart and can't seem to wrap my head around the
logic for this problem, so I'm hoping someone out there can help me
out. Here's my situation:

I have a table of debts placed by clients (we're a collection agency).
tblDebts:
DebtID (autonumber pk)
ClientID (string)
ListDate (date)
ListAmt (double)

What I need to determine is whether a debt is considered New Business
or Repeat Business. A function that returns a boolean (e.g.
CountAsNewBiz) would be fine for my purposes. A new client placement
is either a) a placement by a client who's never placed with us
before; or b) a placement by a client who hasn't placed a debt in over
a year. We then have an activation period of 30 days in which we
count any placements as New Business. Once the 30 days expires,
placements are considered Repeat Business.

Here are a few examples:
ClientID 1234A places a debt on 01/15/07 for $100. They have never
placed with us before, so any placements listed between 01/15/07 and
02/14/07 should be considered as New Business.

ClientID 3456B places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. They have been continuously
placing over the years, and last placed a debt on 01/12/07. This
placement should be considered as Repeat Business.

ClientID 56789C places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. The last time this client
placed with us was 12/31/05, so there has been a gap of over a year.
This placement should be considered New Business.

ClientID 56789C now places another debt on 01/20/07 for $150. Since
01/15/07's placement was considered New Business and it's between
01/15/07 and 02/14/07, this debt is also considered New Business.
Anything placed after 02/14/07 would be Repeat Business.

If my explanation is unclear, please let me know.

Thanks in advance for your suggestions,
Jana

[DavidB]

Prior Activity (Y/N)?
If N then NEW.

If Y then...

Activity within the last 30 days (Y/N)?
If Y then...
Prior Activity 30 days old (Y/N)?
If Y then Repeat.
If N then NEW.

If N then...

Activity within last year (Y/N)?
If Y then REPEAT.
If N then NEW.

Thats my best guess anyway...

On Feb 27, 10:17 am, "Jana" <Bauer.J...@gma il.comwrote:

Good morning, Access gurus!

I'm having a brain fart and can't seem to wrap my head around the
logic for this problem, so I'm hoping someone out there can help me
out. Here's my situation:

I have a table of debts placed by clients (we're a collection agency).
tblDebts:
DebtID (autonumber pk)
ClientID (string)
ListDate (date)
ListAmt (double)

What I need to determine is whether a debt is considered New Business
or Repeat Business. A function that returns a boolean (e.g.
CountAsNewBiz) would be fine for my purposes. A new client placement
is either a) a placement by a client who's never placed with us
before; or b) a placement by a client who hasn't placed a debt in over
a year. We then have an activation period of 30 days in which we
count any placements as New Business. Once the 30 days expires,
placements are considered Repeat Business.

Here are a few examples:
ClientID 1234A places a debt on 01/15/07 for $100. They have never
placed with us before, so any placements listed between 01/15/07 and
02/14/07 should be considered as New Business.

ClientID 3456B places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. They have been continuously
placing over the years, and last placed a debt on 01/12/07. This
placement should be considered as Repeat Business.

ClientID 56789C places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. The last time this client
placed with us was 12/31/05, so there has been a gap of over a year.
This placement should be considered New Business.

ClientID 56789C now places another debt on 01/20/07 for $150. Since
01/15/07's placement was considered New Business and it's between
01/15/07 and 02/14/07, this debt is also considered New Business.
Anything placed after 02/14/07 would be Repeat Business.

If my explanation is unclear, please let me know.

Thanks in advance for your suggestions,
Jana

[Jana]

On Feb 27, 9:36 am, "DavidB" <[e-mail] wrote:

Prior Activity (Y/N)?
If N then NEW.

If Y then...

Activity within the last 30 days (Y/N)?
If Y then...
Prior Activity 30 days old (Y/N)?
If Y then Repeat.
If N then NEW.

If N then...

Activity within last year (Y/N)?
If Y then REPEAT.
If N then NEW.

Thats my best guess anyway...

On Feb 27, 10:17 am, "Jana" <Bauer.J...@gma il.comwrote:

Good morning, Access gurus!

I'm having a brain fart and can't seem to wrap my head around the
logic for this problem, so I'm hoping someone out there can help me
out. Here's my situation:

I have a table of debts placed by clients (we're a collection agency).
tblDebts:
DebtID (autonumber pk)
ClientID (string)
ListDate (date)
ListAmt (double)

What I need to determine is whether a debt is considered New Business
or Repeat Business. A function that returns a boolean (e.g.
CountAsNewBiz) would be fine for my purposes. A new client placement
is either a) a placement by a client who's never placed with us
before; or b) a placement by a client who hasn't placed a debt in over
a year. We then have an activation period of 30 days in which we
count any placements as New Business. Once the 30 days expires,
placements are considered Repeat Business.

Here are a few examples:
ClientID 1234A places a debt on 01/15/07 for $100. They have never
placed with us before, so any placements listed between 01/15/07 and
02/14/07 should be considered as New Business.

ClientID 3456B places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. They have been continuously
placing over the years, and last placed a debt on 01/12/07. This
placement should be considered as Repeat Business.

ClientID 56789C places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. The last time this client
placed with us was 12/31/05, so there has been a gap of over a year.
This placement should be considered New Business.

ClientID 56789C now places another debt on 01/20/07 for $150. Since
01/15/07's placement was considered New Business and it's between
01/15/07 and 02/14/07, this debt is also considered New Business.
Anything placed after 02/14/07 would be Repeat Business.

If my explanation is unclear, please let me know.

Thanks in advance for your suggestions,
Jana- Hide quoted text -

- Show quoted text -

Thanks, I'll give it a shot.

[Jana]

On Feb 27, 9:57 am, "Jana" <Bauer.J...@gma il.comwrote:

On Feb 27, 9:36 am, "DavidB" <[e-mail] wrote:

Prior Activity (Y/N)?
If N then NEW.

If Y then...

Activity within the last 30 days (Y/N)?
If Y then...
Prior Activity 30 days old (Y/N)?
If Y then Repeat.
If N then NEW.

If N then...

Activity within last year (Y/N)?
If Y then REPEAT.
If N then NEW.

Thats my best guess anyway...

On Feb 27, 10:17 am, "Jana" <Bauer.J...@gma il.comwrote:

Good morning, Access gurus!

I'm having a brain fart and can't seem to wrap my head around the
logic for this problem, so I'm hoping someone out there can help me
out. Here's my situation:

I have a table of debts placed by clients (we're a collection agency).
tblDebts:
DebtID (autonumber pk)
ClientID (string)
ListDate (date)
ListAmt (double)

What I need to determine is whether a debt is considered New Business
or Repeat Business. A function that returns a boolean (e.g.
CountAsNewBiz) would be fine for my purposes. A new client placement
is either a) a placement by a client who's never placed with us
before; or b) a placement by a client who hasn't placed a debt in over
a year. We then have an activation period of 30 days in which we
count any placements as New Business. Once the 30 days expires,
placements are considered Repeat Business.

Here are a few examples:
ClientID 1234A places a debt on 01/15/07 for $100. They have never
placed with us before, so any placements listed between 01/15/07 and
02/14/07 should be considered as New Business.

ClientID 3456B places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. They have been continuously
placing over the years, and last placed a debt on 01/12/07. This
placement should be considered as Repeat Business.

ClientID 56789C places a debt on 01/15/07 for $100. The first time
this client placed with us was 12/31/02. The last time this client
placed with us was 12/31/05, so there has been a gap of over a year.
This placement should be considered New Business.

ClientID 56789C now places another debt on 01/20/07 for $150. Since
01/15/07's placement was considered New Business and it's between
01/15/07 and 02/14/07, this debt is also considered New Business.
Anything placed after 02/14/07 would be Repeat Business.

If my explanation is unclear, please let me know.

Thanks in advance for your suggestions,
Jana- Hide quoted text -

- Show quoted text -

Thanks, I'll give it a shot.- Hide quoted text -

- Show quoted text -

David:

Thanks very much for your assistance! Here's what I came up with as my
final function (I still need to add error handling and clean up my
naming). I'm doing a reality check on my results, but so far I
haven't found any anomalies:

Function isNew(CltID As String, LD As Date) As Boolean
Dim potFirstAD
Dim lst30 As Date
Dim AD As Date
lst30 = LD - 30
Select Case DCount("[LIST_DATE]", "DEBT", "[CLT_ID] = '" & CltID & "'
and[LIST_DATE] >= #" & lst30 & "#")
Case 0
'No biz in past 30 days
potFirstAD = LD
Case Is 0
potFirstAD = CDate(DMin("[LIST_DATE]", "DEBT", "[CLT_ID] = '" &
CltID & "' and[LIST_DATE] >= #" & lst30 & "#"))
End Select
AD = DMin("[LIST_DATE]", "DEBT", "[CLT_ID] = '" & CltID & "' and[LIST_DATE] BETWEEN #" & potFirstAD - 365 & "# AND #" & potFirstAD &
"#")
If LD - AD <= 30 Then
isNew = True
Else
isNew = False
End If
End Function

Once again, this group is a fabulous resource.

Jana