If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Renewing member database design question.
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 |
#2
|
|||
|
|||
Renewing member database design question.
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" wrote in message ... 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 |
#3
|
|||
|
|||
Renewing member database design question.
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 |
#4
|
|||
|
|||
Renewing member database design question.
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" wrote in message ... [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. |
#5
|
|||
|
|||
Renewing member database design question.
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 |
#6
|
|||
|
|||
Renewing member database design question.
Hi Dennis,
I wrote and run membership databases for 5 organizations, varying from 12 to 600 members. Your structure should also be determined by your needs and goals. For example, yours is oriented around databasing the renewal as an entity, and recording the details of such. In my case, in the largest organizaiton, the treasurer does and records all of that separately. For my objectives, I have settled on what some would call an unnormalized structure of one field in the "people" table for each year. I have fields for 5-10 years into the future, and, each year, I take 30 seconds to write a simple view query to mark life members (that haven't died etc.) as members for the new year. The biggest advantage is that it's about 20 easier / simpler to query based on membership history. For example, current members who were also members in 1955 and 1965. One of the organizations has been in existence since 1937, and so, after about another 150 years I'll have to restructure that one. :-) |
#7
|
|||
|
|||
Renewing member database design question.
Fred, It sound interesting, but I'm don't quite understand how you did it. I'm sorry, but could you provide a little more detail? I am all for simply since this only 400 members. Since you brought up deceased members, I have a date of death field on the member rcd, so I know who had died. Thanks for your assitance. Dennis |
#8
|
|||
|
|||
Renewing member database design question.
Hello Dennis,
I'd be happy to. There's no claim that it's what you should do, my only claim is that it's good for my "600 member" situation. First, I am the secretary of that organizaiton, and there is a good Treasurer. And so the treasurer records all payments (actually in a General Ledger Access application that I wrote for him) and he gives me list of who paid, organized so that he tells me "once and only once" for each payment. I also issue (mail merged from Access) "invoice/update" forms once a year which print out all of their current information and asks them to mark it up with updates. When these come in with money, the treasurer gives me the form instead of the "notice" Membership is to individuals, although in some cases I make badges for their family members (more on that later) - - - - - So my DB (in the areas that you are discussing) is basically one big table of people who are members or whoever were members. It has about 20 fields of "one to one" type information for that person. (Name, address, email, land-line phone, cell phone etc.. There is also a field for "LifeMember", a "Y" in that field = yes, BTW, when other persons in the organization have databases that are well run enough to really have and respect a primary key (such as work scheduling) also have that as a field so that I can either link and use their data, or export sets of data to them that is helpful to them, including their PK. If I were a DB architecture structural purist, I would dictate that they use the PK of the organizations main databases, but I decided that that would not be a good idea because it would make those valuable volunteer's jobs harder. And I have a column that indicates membership status for each year that the organization has been in existence. A "Y" in that field = is/was a member in that year, a null in that field = not a member in that year. The organization started in 1937. So I have about 78 fields indicating membership status for each year from 1937 until 2015. (e.g. field names: "mem37", "mem38" .......mem09, mem10). I have been runniign this databased (moved from Dbase to Access) for 22 years. Prior to that, I am slowly entering from onld membership lists, etc. for historical purposes. This a a rare case (and yes there are some) where a fully normalized database would be less optimal for the needs. I know exactly howo I would normalize this aspect, I know it well enough to not do it. When I hear that someone has dies, I enter that in the "notes" field. And then end of the year (using this year as an example) I write a query which shows only life members who were member in 2009, and which shows their first name, last name, notes field and the "mem10" field and, unless they died, I check them off for 1020 membership. If we had more than 20 life members, I would further utomate this, but I don't. - - - I mentioned that I only have one main table in the areas that you discussed. I have several more linked "one to many" tables for other items such as: - For issuing "family member" version of membership badges, when ordered (the badges are a color Access report) - recording instances of dontations - recording instances of a person serving in elected positions This organization is big on history. - - - - I debate with my son who runs 100,000,000+ record databases. The rules that should be categorically followed for such a databases would be be a bad idea for someone who has decided that they want to use Access for their grocery list. |
Thread Tools | |
Display Modes | |
|
|