A/R tables

[Annie]

Hello. I'm trying to decide the best way to handle
membership dues. I have:

Members
MemberID
Name
Address

MembershipType
MembershipTypeID
MembershipTypeName
(for example, single, family- which could include a member-
spouse or not)
MembershipDues
($X single, $Y family)

I think I want:

MembershipDues
MembershipDueID
(account to show membership dues for each Member- not sure
what to do for member-spouse)

Members can combine dues across member/member-spouse, and
they can use "fake money" they've earned (again, across
member/member-spouse). There is a limit to "fake money"
based on MembershipType. I think I need a many-to-many
between what's due and how it's paid. Which would give me
MembershipDues, PaymentsReceived and PaymentTracking (to
take into account "fake money" or maybe even a membership
paid on behalf of a Member).

I think I'd want enough info in maybe PaymentTracking so
that if a Member says I sent you a check a month ago, we'd
know if we have a record of it.

Am I on the right track? If so, I'd appreciate help with
foreign keys to make sure I have the relationships sound.
Thank you for any assistance you can provide.

 

[Scott McDaniel]

Well, dues are just like Invoices and payments in a traditional AR system
.... Members are like Customers, etc etc. I've included several "lookup"
tables (i.e tblMemberTypes, etc) that would be used to feed the main tables.
This is just a beginning, and I'm sure you'll have to change things around
to fit. The first field listed for each table would be the Primary Key (I'd
use an Autonumber), but make sure to set any Unique Indexes you need to
ensure data integrity.

tblMembers
============
lngMemberID
lngMemberType [FK to tblMemberTypes]
strName
strAddress
etc etc

tblMemberTypes
============
lngMemberTypeID
strMemberType

tblDuesHeader
==========
lngDuesID
lngMemberID [FK to tblMembers - tells you who owes these dues]
dteDateDue
etc etc

tblDuesLineItem
===============
tblDuesItemID
lngDuesID [FK to tblDuesHeader]
lngItemType [FK to tblDuesItemType - allows you to differentiate between
basic dues, late fees, addtional items, etc etc]
curAmountDue
dteDateCreated
etc etc

tblDuesItemType
===========
lngDuesItemTypeID
strDuesItemType

tblPaymentsHeader
===========
lngPaymentID
lngDuesID [FK to tblDuesHeader]
dteDateCreated
etc etc

tblPaymentsLineItem
==============
lngPaymentItemID
lngPaymentsID [FK to tblPaymentsHeader]
lngRemittedBy [FK to tblMembers, if possible - if people outside of the
organization can make payments, then they won't be in the table]
lngType [FK to tblPaymentTypes - allows you to differentiate between
different types of payments, i.e. "fake" money, etc]
curAmount
dteDateRemitted
etc etc

tblPaymentTypes
===========
lngTypeID
strType

You'd have to use VBA code to determine how much "fake" money could be used,
based on Membership types, etc ... I'm not entirely sure how you'll store
these "combined" dues ... however, if you want to store Members and family
members of a Member (note the capitaliztion), then I would revise this
somewhat and make a Membership, a generic object that represents an
all-encompassing entity, and add Members to a Membership:

tblMembership
===============
lngMembershipID
strDescription
dteDateCreated
etc etc

tblMembers
==========
lngMemberID
strName
strAddress
etc etc

tblMemberships (this would be your Many-to-Many) table
===========
lngMemberships
lngMemberID
strNotes
etc etc

At this point, I'd think a Membership would "own" all dues and payments,
therefore you would need to change the Foreign keys in Payments and Dues
tables to look at tblMembership.lngMembershipID ... again, this would need
to be fleshed out to determie who own what, but in general these are good
places to start.

 

[Annie]

Thank you for your post!! I thought I was bad at many
things Access, but table design takes the cake! I'll
check it out- I appreciate the step-by-step.

-----Original Message-----
Well, dues are just like Invoices and payments in a traditional AR system
.... Members are like Customers, etc etc. I've included several "lookup"
tables (i.e tblMemberTypes, etc) that would be used to feed the main tables.
This is just a beginning, and I'm sure you'll have to change things around
to fit. The first field listed for each table would be the Primary Key (I'd
use an Autonumber), but make sure to set any Unique Indexes you need to
ensure data integrity.

tblMembers
============
lngMemberID
lngMemberType [FK to tblMemberTypes]
strName
strAddress
etc etc

tblMemberTypes
============
lngMemberTypeID
strMemberType

tblDuesHeader
==========
lngDuesID
lngMemberID [FK to tblMembers - tells you who owes these dues]
dteDateDue
etc etc

tblDuesLineItem
===============
tblDuesItemID
lngDuesID [FK to tblDuesHeader]
lngItemType [FK to tblDuesItemType - allows you to differentiate between
basic dues, late fees, addtional items, etc etc]
curAmountDue
dteDateCreated
etc etc

tblDuesItemType
===========
lngDuesItemTypeID
strDuesItemType

tblPaymentsHeader
===========
lngPaymentID
lngDuesID [FK to tblDuesHeader]
dteDateCreated
etc etc

tblPaymentsLineItem
==============
lngPaymentItemID
lngPaymentsID [FK to tblPaymentsHeader]
lngRemittedBy [FK to tblMembers, if possible - if people outside of the
organization can make payments, then they won't be in the table]
lngType [FK to tblPaymentTypes - allows you to differentiate between
different types of payments, i.e. "fake" money, etc]
curAmount
dteDateRemitted
etc etc

tblPaymentTypes
===========
lngTypeID
strType

You'd have to use VBA code to determine how much "fake" money could be used,
based on Membership types, etc ... I'm not entirely sure how you'll store
these "combined" dues ... however, if you want to store Members and family
members of a Member (note the capitaliztion), then I would revise this
somewhat and make a Membership, a generic object that represents an
all-encompassing entity, and add Members to a Membership:

tblMembership
===============
lngMembershipID
strDescription
dteDateCreated
etc etc

tblMembers
==========
lngMemberID
strName
strAddress
etc etc

tblMemberships (this would be your Many-to-Many) table
===========
lngMemberships
lngMemberID
strNotes
etc etc

At this point, I'd think a Membership would "own" all dues and payments,
therefore you would need to change the Foreign keys in Payments and Dues
tables to look at tblMembership.lngMembershipID ... again, this would need
to be fleshed out to determie who own what, but in general these are good
places to start.

Hello. I'm trying to decide the best way to handle
membership dues. I have:

Members
MemberID
Name
Address

MembershipType
MembershipTypeID
MembershipTypeName
(for example, single, family- which could include a member-
spouse or not)
MembershipDues
($X single, $Y family)

I think I want:

MembershipDues
MembershipDueID
(account to show membership dues for each Member- not sure
what to do for member-spouse)

Members can combine dues across member/member-spouse, and
they can use "fake money" they've earned (again, across
member/member-spouse). There is a limit to "fake money"
based on MembershipType. I think I need a many-to-many
between what's due and how it's paid. Which would give me
MembershipDues, PaymentsReceived and PaymentTracking (to
take into account "fake money" or maybe even a membership
paid on behalf of a Member).

I think I'd want enough info in maybe PaymentTracking so
that if a Member says I sent you a check a month ago, we'd
know if we have a record of it.

Am I on the right track? If so, I'd appreciate help with
foreign keys to make sure I have the relationships sound.
Thank you for any assistance you can provide.

.