renewal letters not needed

[quaddawg via AccessMonster.com]

I'm posting this in queries because I have a slight hunch the solution might
have to do with the proper query; please excuse me if it does not.

In my database, a button generates three renewal request letters: 2 mos from,
1 month from, and month of membership expiration. That works fine except
when members pay after the first renewal request and become irate when they
receive two more renewal requests.

What is the best way to exclude the records for those who have already
renewed? I'd like this to be automated somehow so that it does not depend on
the user checking a box on the old record indicating it no longer needs to be
selected for renewal letters.

Structure-wise, I have two tables with a one to many relationship (Members,
Memberships).
I have a membership form with a subform displaying all memberships for the
member.

Thanks,
Lawton

 

[Guest]

Your query should be pulling records of thos that have NOT renewed. You then
need to be creating a record when thet renew. The query then would ignore
them and not pull their records.

 

[Smartin]

Lawton,

I'm posting this in queries because I have a slight hunch the solution might
have to do with the proper query; please excuse me if it does not.

In my database, a button generates three renewal request letters: 2 mos from,
1 month from, and month of membership expiration. That works fine except
when members pay after the first renewal request and become irate when they
receive two more renewal requests.

What is the best way to exclude the records for those who have already
renewed? I'd like this to be automated somehow so that it does not depend on
the user checking a box on the old record indicating it no longer needs to be
selected for renewal letters.

How do you determine whether an account has been renewed? You need to
work that logic into the query that selects accounts for letters.

Structure-wise, I have two tables with a one to many relationship (Members,
Memberships).
I have a membership form with a subform displaying all memberships for the
member.

If you're having trouble determining if an account is "current" just
provide give us some details about your tables and data and we'll go
from there.

Thanks,
Lawton

Good luck!

 

[quaddawg via AccessMonster.com]

Thanks for replying.

How do you determine whether an account has been renewed? You need to
work that logic into the query that selects accounts for letters.

The subform lists all memberships past and present for a member. The subform
for John Doe (form level) lists an inactive membership from 8/01/05 - 8/01/06
and lists an active membership from 8/02/06 - 8/02/07. The form pulls from
tblMembers and the subform pulls from tblMemberships (they have a one to many
relationship, as you can see; the foreign key in tblMemberships is the
MemberID of tblMembers).
There is a field in tblMemberships to indicate whether the membership is New
or Renewal, as well as fields for StartDate, EndDate, MembershipLevel, etc.

The renewal request reports are generated based off the EndDate field of the
Membership records in tblMemberships.

Let's say John Doe sent in his renewal after the first renewal request letter
(6/06). We renew it for one year after the current membership expires (8/06,
expiring in 8/07). John has renewed, yet he'll get a renewal reminder in
7/06 and 8/06 because the queries are still pulling off the prior
Membership's EndDate--the subform lists both by design.

Ideally, the queries would take into account the fact that the Member record
has two Membership records, and if there exists a future Membership record (a
renewal), it will not select the soon-to-be-expired membership. I.e, because
there is a future Renewal Membership record associated with the Member record
there is no need to continue sending renewal requests for the other, current
Membership record.

So, somehow I want some sort of "recognition" or comparison of multiple
M'ship records for a given Member in order to not continue to send renewal
requests for the current M'ship if we've already created a new, future M'ship
for the Member.
Hopefully that makes some sense.

 

[Smartin]

Thanks for replying.


The subform lists all memberships past and present for a member. The subform
for John Doe (form level) lists an inactive membership from 8/01/05 - 8/01/06
and lists an active membership from 8/02/06 - 8/02/07. The form pulls from
tblMembers and the subform pulls from tblMemberships (they have a one to many
relationship, as you can see; the foreign key in tblMemberships is the
MemberID of tblMembers).
There is a field in tblMemberships to indicate whether the membership is New
or Renewal, as well as fields for StartDate, EndDate, MembershipLevel, etc.

The renewal request reports are generated based off the EndDate field of the
Membership records in tblMemberships.

Aha!?

So if /any/ EndDate is less than the comparison date your current
problem is you tag this Member for a letter, even if another EndDate
exists that is greater than the comparison date for the same member.

Let's say John Doe sent in his renewal after the first renewal request letter
(6/06). We renew it for one year after the current membership expires (8/06,
expiring in 8/07). John has renewed, yet he'll get a renewal reminder in
7/06 and 8/06 because the queries are still pulling off the prior
Membership's EndDate--the subform lists both by design.

Ideally, the queries would take into account the fact that the Member record
has two Membership records, and if there exists a future Membership record (a
renewal), it will not select the soon-to-be-expired membership. I.e, because
there is a future Renewal Membership record associated with the Member record
there is no need to continue sending renewal requests for the other, current
Membership record.

Right, you are only concerned about the Max EndDate here.

So, somehow I want some sort of "recognition" or comparison of multiple
M'ship records for a given Member in order to not continue to send renewal
requests for the current M'ship if we've already created a new, future M'ship
for the Member.
Hopefully that makes some sense.

[snip]

I think you can solve this quickly using an aggregate query that groups
on Member and picks out the Max of EndDate in the Having clause.
Something along the lines of

SELECT Members.ID, Max(Memberships.EDate) AS MaxOfEDate
FROM Members JOIN Memberships ON Members.ID =
Memberships.MemberID
GROUP BY Members.ID
HAVING (Max(Memberships.EndDate))<Date();

Or, in Query Builder:
Members.ID Memberships.EndDate
Group By Max
Show yes Show yes
Criteria: < Date()

You will need to adjust the Date() piece to reflect your "close"
comparison (within 90 days, I think).

Something you have not mentioned is canceled memberships. E.g., if the
Max of EndDate for some Member is five years ago, this (apparently ex-)
Member probably doesn't want to hear from you. Unless you already have
something to handle that my gut reaction is to use a yes/no flag in
Members to denote a canceled status, and exclude a "yes" value from the
selection altogether.

HTH

 

[quaddawg via AccessMonster.com]

Thanks! I created a max query per your instructions and used it as an
element of the three renewal queries. Here's an example:

SELECT tblMemberData.Address1, tblMemberData.Address2, tblMemberData.City,
tblMemberData.State, tblMemberData.Zip, tbllkMemberCategory.CurrentDues,
qryMaxRenewal.FormatMaxDate, qryMaxRenewal.MemberID, tblMemberData.LastName,
tblMemberData.FirstName, qryMembershipsDataTable.StartDate
FROM (qryMaxRenewal INNER JOIN (tblMemberData INNER JOIN
qryMembershipsDataTable ON tblMemberData.MemberID = qryMembershipsDataTable.
MemberID) ON (qryMaxRenewal.MemberID = qryMembershipsDataTable.MemberID) AND
(qryMaxRenewal.FormatMaxDate = qryMembershipsDataTable.FormatThirdRenewal))
INNER JOIN tbllkMemberCategory ON qryMembershipsDataTable.MemberCategory =
tbllkMemberCategory.MemberCategory
WHERE (((qryMaxRenewal.FormatMaxDate)=Format(DateSerial(Year(Date()),Month
(Date())+1,1),"yyyymm")));

Not the prettiest query, but it seems to work.
As you can see from the WHERE clause, old memberships are not an issue.

Something you have not mentioned is canceled memberships. E.g., if the
Max of EndDate for some Member is five years ago, this (apparently ex-)
Member probably doesn't want to hear from you. Unless you already have
something to handle that my gut reaction is to use a yes/no flag in
Members to denote a canceled status, and exclude a "yes" value from the
selection altogether.

Thanks again.

 

[Smartin]

Great! Glad you got it working.

Thanks! I created a max query per your instructions and used it as an
element of the three renewal queries. Here's an example:

SELECT tblMemberData.Address1, tblMemberData.Address2, tblMemberData.City,
tblMemberData.State, tblMemberData.Zip, tbllkMemberCategory.CurrentDues,
qryMaxRenewal.FormatMaxDate, qryMaxRenewal.MemberID, tblMemberData.LastName,
tblMemberData.FirstName, qryMembershipsDataTable.StartDate
FROM (qryMaxRenewal INNER JOIN (tblMemberData INNER JOIN
qryMembershipsDataTable ON tblMemberData.MemberID = qryMembershipsDataTable.
MemberID) ON (qryMaxRenewal.MemberID = qryMembershipsDataTable.MemberID) AND
(qryMaxRenewal.FormatMaxDate = qryMembershipsDataTable.FormatThirdRenewal))
INNER JOIN tbllkMemberCategory ON qryMembershipsDataTable.MemberCategory =
tbllkMemberCategory.MemberCategory
WHERE (((qryMaxRenewal.FormatMaxDate)=Format(DateSerial(Year(Date()),Month
(Date())+1,1),"yyyymm")));

Not the prettiest query, but it seems to work.
As you can see from the WHERE clause, old memberships are not an issue.


Thanks again.