Prev: Part and Product Database Help - replied by Losers R US
Next: AYS Link broken?
From: Dennis on 27 Feb 2010 18:18
Hi,


1. Background:

I have a member database for a local community club. I'm in the process of
adding membership renewal processing to the module. There are two types of
membership; Life Time and Annual.

The Life members pay a one time fee and are members for life. The Annual
members pay a membership fee once a year. The renewal drive starts in
November. In October, when we print the November newsletter, we include a
reminder notice in the monthly news letter that is mailed each member.
Members will then pay their renewal by either check or cash.

I want to start tracking the renewal history, which I do not do right now.
Currently, I just have the member ship term (annual or life) and current
expiration date in the master rcd.


2. Existing DB:

I have a Member Master table as follows:
Tbl tblMember
Key: AcctNo – Automatically assign number.
Fld: Name
Address
Etc.
Membership Type (Annual or Life)
Expiration Date (Annual terms expire on 12-31-current year
Life terms expired on 12-31-2099)


3. Proposed new DB
Tbl tblRenew
Key: RenewalID: Automatically assign number.
Fld: AcctNo
New / Renew Flag N = New member, R = Renewing member.
MemYear = Calendar year for membership
(ie. 2009, 2010, etc.)
DtPaid = Date the member paid their dues.
PaidBy = How did member paid their dues (check or cash)
AmtPaid = How much they paid for membership.
(Can chg each year)
RenPrtDt = The date the membership Dues & Remittance
form was sent to National.
DtCardRcvd = The date the card was received back from
National membership.
DateExpires = The date the membership expires
(end of current year of 12-31-2099)


I have a query that joins the tblMember to tblRenewal for the current term
as followings:

SELECT tblMember.AcctNo, tblMember.LastName, tblRenew.NewRenew,
tblRenew.MemYear, tblRenew.PaidBy, tblRenew.PaidBy, tblRenew.AmtPaid,
tblRenew.RenPrtDt,
FROM tblRenew RIGHT JOIN tblMailingList ON tblRenew.AcctNo = tblMember.AcctNo
WHERE (((tblRenew.MemYear)=Year(Date())));


I also have another query that joins the tblMember to tblRenew for all of
the renewal terms.

4. Issues / Questions.

A. Any suggestions on how to deal with Life Member? About 50% of the 400
members are life members. Should I just run an update query once a year to
create a copy a renewal record for each Life members? Or is there a better
way to deal with Life members?

B. As far as regular members to, I was planning to just have the user
create a new record at data entry time. I really don't want to copy 2009 to
2010 for renewing member because they I would have to delete those 2009
member who did not renew. Any suggestion on this approach?

C. I originally joined the tblMember and tblRenew using a Group By and
Max(CurrYr). However, this approach left me with a read only query, which I
did not want. I want to end up with a query where I can change that data
because it will be used for the data entry form. The query I have above is
input enabled.

Anybody have a better way to join the two tables?

D. I know about the Allen Browne multi-tier approach and for simplicity,
I've chosen not to implement a Family / Family Member table structure at this
time. I only have 400 members, of which I have maybe 30 people in a joint
address.

E. Does anyone have any other suggestions?


Thanks,

Dennis



From: Allen Browne on 27 Feb 2010 20:27
Two tables:
- a Client table, containing only details about the person (nothing about
membership or renewals.)
- a Payments table, containing information about payments received
(including membership payments.)

The payments table would contain fields that indicate the amount paid, the
membership period this starts from, and the number of years it covers. You
can indicate that a life membership covers (say) 99 years. The next
membership payment is due:
RenewDate: DateAdd("yyyy", [Years], [FromDate])
where Years is the number of years they paid, and FromDate is the date they
joined (or renewed.)

So, you type an expression like that into a query.
Group By the member.
Choose the Max of RenewDate.
That's when they are due again.

You will also want to add an Inactive (Yes/No) field to the Client table,
and exclude those from this query, so you don't send renewal notices out to
dead people.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Dennis" <Dennis(a)discussions.microsoft.com> wrote in message
news:F75BF7D5-A6BB-4EA8-8BF5-6AC94B098250(a)microsoft.com...
> Hi,
>
>
> 1. Background:
>
> I have a member database for a local community club. I'm in the process
> of
> adding membership renewal processing to the module. There are two types
> of
> membership; Life Time and Annual.
>
> The Life members pay a one time fee and are members for life. The Annual
> members pay a membership fee once a year. The renewal drive starts in
> November. In October, when we print the November newsletter, we include a
> reminder notice in the monthly news letter that is mailed each member.
> Members will then pay their renewal by either check or cash.
>
> I want to start tracking the renewal history, which I do not do right now.
> Currently, I just have the member ship term (annual or life) and current
> expiration date in the master rcd.
>
>
> 2. Existing DB:
>
> I have a Member Master table as follows:
> Tbl tblMember
> Key: AcctNo – Automatically assign number.
> Fld: Name
> Address
> Etc.
> Membership Type (Annual or Life)
> Expiration Date (Annual terms expire on 12-31-current year
> Life terms expired on 12-31-2099)
>
>
> 3. Proposed new DB
> Tbl tblRenew
> Key: RenewalID: Automatically assign number.
> Fld: AcctNo
> New / Renew Flag N = New member, R = Renewing member.
> MemYear = Calendar year for membership
> (ie. 2009, 2010, etc.)
> DtPaid = Date the member paid their dues.
> PaidBy = How did member paid their dues (check or cash)
> AmtPaid = How much they paid for membership.
> (Can chg each year)
> RenPrtDt = The date the membership Dues & Remittance
> form was sent to National.
> DtCardRcvd = The date the card was received back from
> National membership.
> DateExpires = The date the membership expires
> (end of current year of 12-31-2099)
>
>
> I have a query that joins the tblMember to tblRenewal for the current term
> as followings:
>
> SELECT tblMember.AcctNo, tblMember.LastName, tblRenew.NewRenew,
> tblRenew.MemYear, tblRenew.PaidBy, tblRenew.PaidBy, tblRenew.AmtPaid,
> tblRenew.RenPrtDt,
> FROM tblRenew RIGHT JOIN tblMailingList ON tblRenew.AcctNo =
> tblMember.AcctNo
> WHERE (((tblRenew.MemYear)=Year(Date())));
>
>
> I also have another query that joins the tblMember to tblRenew for all of
> the renewal terms.
>
> 4. Issues / Questions.
>
> A. Any suggestions on how to deal with Life Member? About 50% of the 400
> members are life members. Should I just run an update query once a year
> to
> create a copy a renewal record for each Life members? Or is there a
> better
> way to deal with Life members?
>
> B. As far as regular members to, I was planning to just have the user
> create a new record at data entry time. I really don't want to copy 2009
> to
> 2010 for renewing member because they I would have to delete those 2009
> member who did not renew. Any suggestion on this approach?
>
> C. I originally joined the tblMember and tblRenew using a Group By and
> Max(CurrYr). However, this approach left me with a read only query, which
> I
> did not want. I want to end up with a query where I can change that data
> because it will be used for the data entry form. The query I have above
> is
> input enabled.
>
> Anybody have a better way to join the two tables?
>
> D. I know about the Allen Browne multi-tier approach and for simplicity,
> I've chosen not to implement a Family / Family Member table structure at
> this
> time. I only have 400 members, of which I have maybe 30 people in a joint
> address.
>
> E. Does anyone have any other suggestions?
>
>
> Thanks,
>
> Dennis
>
>
>
From: Dennis on 27 Feb 2010 22:00

Allen,

Can do the two tables. No worries.


Your comment:
The payments table would contain fields that indicate the amount paid, the
membership period this starts from, and the number of years it covers. You
can indicate that a life membership covers (say) 99 years. The next
membership payment is due:

RenewDate: DateAdd("yyyy", [Years], [FromDate])

where Years is the number of years they paid, and FromDate is the date they
joined (or renewed.)

Response: Very elegant. That solves a lot of issues.


Your comment:
So, you type an expression like that into a query.
Group By the member.
Choose the Max of RenewDate.
That's when they are due again.

Response: I've already tried this approach. It was my understanding that a
Group By and Max(RenewalDate) results in a read only query. One of my
requirements was the query support data entry so I can create a data entry
form around it. Sorry if I was not clear about that earlier. Since
membership renewals arrive in “bunches”, I want to have the continuous form
entry screen. This screen will have a comb box for member name, and then
fields that allow me to enter the renewal information.

Using the Max() does solve the function of connecting the Client table to
the Payment table without having to worry about the payment term, but it
leaves me without the data entry capability I need.

That is why I was using the

SELECT tblMember.AcctNo, …..
FROM tblRenew RIGHT JOIN tblMailingList ON tblRenew.AcctNo = tblMember.AcctNo
WHERE (((tblRenew.MemYear)=Year(Date())));

This join provided me with a data entry enabled query that I could use as a
source for a data entry form, but then I have the issues of Life versus
Annual.

I could modify my data entry form to post the key to the last payment
transaction to the Client table as a foreign key and that would give me a
direct link. I would have both a data entry capable query and resolves the
Life and Annual issues.



Thanks,

Dennis

From: Allen Browne on 28 Feb 2010 01:15
Yes: a GROUP BY query will give you a read-only result.

You could work around that with a DMax() expression, either in your source
query or in the Control Source of a text box on your form:
=DMax("DateAdd(""yyyy"", [Years], [FromDate])", "PaymentsTable",
"ClientID = " & Nz([ClientID],0)

I would try to resist the temptation to store this value as a field in the
Person table, as this violates basic normalization rules ("Don't store
dependent data.")

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Dennis" <Dennis(a)discussions.microsoft.com> wrote in message
news:7DCBA6AE-E90B-4B07-80E2-594A3173F0CE(a)microsoft.com...
> [snip]
> Your comment:
> So, you type an expression like that into a query.
> Group By the member.
> Choose the Max of RenewDate.
> That's when they are due again.
>
> Response: I've already tried this approach. It was my understanding that
> a
> Group By and Max(RenewalDate) results in a read only query. One of my
> requirements was the query support data entry so I can create a data entry
> form around it. Sorry if I was not clear about that earlier. Since
> membership renewals arrive in “bunches”, I want to have the continuous
> form
> entry screen. This screen will have a comb box for member name, and then
> fields that allow me to enter the renewal information.
>
> Using the Max() does solve the function of connecting the Client table to
> the Payment table without having to worry about the payment term, but it
> leaves me without the data entry capability I need.
>
> That is why I was using the
>
> SELECT tblMember.AcctNo, …..
> FROM tblRenew RIGHT JOIN tblMailingList ON tblRenew.AcctNo =
> tblMember.AcctNo
> WHERE (((tblRenew.MemYear)=Year(Date())));
>
> This join provided me with a data entry enabled query that I could use as
> a
> source for a data entry form, but then I have the issues of Life versus
> Annual.
>
> I could modify my data entry form to post the key to the last payment
> transaction to the Client table as a foreign key and that would give me a
> direct link. I would have both a data entry capable query and resolves
> the
> Life and Annual issues.

From: Dennis on 28 Feb 2010 02:25

Allen,


You could work around that with a DMax() expression, either in your source
query or in the Control Source of a text box on your form:
=DMax("DateAdd(""yyyy"", [Years], [FromDate])", "PaymentsTable",
"ClientID = " & Nz([ClientID],0)

Ok, I've not used this before so I'm going to have to play with it.




Your comment: I would try to resist the temptation to store this value as a
field in the Person table, as this violates basic normalization rules ("Don't
store dependent data.")

My Response: To which value are you referring – The current renewal record
key or the current year? The current year is a calculate field based upon
the system's current year.


Dennis

 |  Next  |  Last
Pages: 1 2
Prev: Part and Product Database Help - replied by Losers R US
Next: AYS Link broken?