Greetings UA

I am helping a friend set up a simple db to manage the a club where he is a member.

It is simple db with 3 majors tables so far.

One becomes a member for a year(365 days) after paying a membership fee of around $40. If one pays today, his/her membership expires on 19th April 2013.

Upon paying the fee, the member is issued with a membership ID.

He wants to be able to track membership expiration. e.g list of members whose membership is expiring within a time frame e.g 1 month, 3 months, etc

He also wishes to track Inactive members. A member categorized as Inactive today, can become active again by paying the membership fee.

I have the following tables so far.

tblMembers. MemberID(PK), firstName, midlename, email, MembershipStatusID, ( and other fields)

tblMemberShipStatus. StatusID(PK), Status(e.g active, inactive, etc)

tblMemberCurrentStatus CurrentStatusID(PK), MemberID(FK), StatusID(FK), StatusDate

tblPayments. PaymentID(PK),MemberID(FK),PaymentDate, Amount.

Where i am stuck is to match the Payments made and membership. One is only considered an active member for the duration of the period /year for which he paid the fee. E.g we need to know that a payment of $30 made by Member John on 19/04/2012 is for the period starting 20/04/2012 ending 19/04/2012.

A member can pay in installments or in advance say for a period of two years.

I have an idea of making membership table. with ID, MemberID, StartDate,EndDate. Does this make sense?

I am thinking that "Membership" is an entity which guarantees it an independent table and may that means i wont need the Status Tables.

To get the member's expiry , we query this table.

I have searched here on UA , found some threads, looked at templates from Microsoft, but i have been able to solve this issue.

So please advise accordingly. I dont have any sample yet. Just on the drawing board right now.

Ronald,
How about something like:

tblMembers
MemberID
FirstName
MiddleName
LastName
etc

tblTypeMembership
TypeMembershipID
TypeMembership--1 year, 2 year, etc

tblMembershipDues--this table would allow for Storing dues changes but keep the correct amounts that were to be paid
MembershipDuesID--PK
TypeMembershipID--FK to tblTypeMembership--IF there is a difference in the dues amount for 1 year versus 2 years--if no difference in the amount then this field isn't needed
Dues---Currency
Current--Y/N

tblMembership
MembershipID--PK
MemberID--FK to tblMembers
MembershipDuesID--FK to tblMembershipDues
TypeMembershipID--FK to tblTypeMembership--This field would be needed only IF this field is NOT included in tblMembershipDues
MembershipDt--Date/Time

tblPayType
PayTypeID
PayType--Cash, CreditCard, Check

tblMembershipPayments
MembershipPaymentsID
MembershipID--FK to tblMembership
PayDt--Date/Time
PayAmount--Currency
PayTypeID--FK to tblPayType

HTH

Thank you Cynthia for the reply

Let me try to understand your suggestions .

Will update you later


Ronald

Hello Cynthia

Thank you so much. I have adopted your table structure. But can you give me some clues on:

1... Calculating expiry of membership.

2....Preventing duplicate entries in tblMembership.


Ronald

Ronald,

Calculating expiry of membership
That will depend on which table stores TypeMembershipID as an FK.

I think in tblTypeMembership I would change TypeMembership from DataType Text to a DataType Number.
Tnen when using tblTypeMembership as a cbo in a form I would have an alias in the underlying query:
MemTypeYrs: [MembershipType]&" Year" and use TypeMembershipID as the Bound Column and MemType as Column(1)
Expiry date could then be calculated using DateAdd().

Preventing duplicate entries in tblMembership
Because MembershipDt is the Start Date that the Membership for that Time Period, there are probably at least 2 ways to do this:
1. DCount MemberID and MembershipDt
2. At table level--Index MemberID and MembershipDt

How does this sound to you?

Hello Cynthia

That is beautiful. Understood it well.

Want one more tip from you on data validation. How do i prevent overlapping membership?
e.g member A 's membership begun on 12/04/2012 ,ended on 11/04/2013. perhaps accidentally some one can enter another record for same member say starting 12/05/2012 .

How do i prevent that?

Ronald

Ronald,

Thought you said this was "simple"

I don't know the answer, so I've put out a call to teach us both

Hello Cynthia

My db simple in terms of number of tables....

Thank for calling out for help.


Ronald

Hi

There are two parts to this, in that you'll need to include code that provides the check for intersecting dates and refuse to create the new record based on that (attempting to do this at table level would leave you frustrated).
So we're talking about a form event (BeforeUpdate with a Cancel of the event if the record intersects with another record for that member).

The other part is the check itself (that you perform in the called event procedure code).
That's basically just looking for date intersections. That kind of thing is disccussed here (and is, hopefully, simple enough to understand that I've since canabalised it elsewhere :-)

You've done well with taking Cynthia's suggestions and running with them so far. See how you get on and shout for further detail if required.

Cheers.

Ronald,

I don't know of a way to prevent overlapping membership at the table level. However, if you are using forms for data entry, then you can test a new commencement date when it is entered to determine if the new date is greater than the members last membership end date. You would do this in the relevant date control's before update event.

Glenn

That's spooky. (I found that thread by searching on our collective names Glenn ;-)

@ Leigh. Looks like I was typing as you posted.

Glenn

Hello LPurvis and argeedblu

I am planning to put the code in before update event of the form.

I have bookmarked the thread . Will return to it when designing the membership form.

In case of a problem, i will need to start another thread.

Thanks


Ronald

That's great Ronald.

Let us know if you need more assistance.

GLENN

Leigh & Glenn,

Thanks!!