By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Determine if New or Repeat Client

P: n/a
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

Feb 27 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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...@gmail.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

Feb 27 '07 #2

P: n/a
On Feb 27, 9:36 am, "DavidB" <j...@yahoo.comwrote:
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...@gmail.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.

Feb 27 '07 #3

P: n/a
On Feb 27, 9:57 am, "Jana" <Bauer.J...@gmail.comwrote:
On Feb 27, 9:36 am, "DavidB" <j...@yahoo.comwrote:


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...@gmail.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

Feb 27 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.