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.
"Annie" wrote in
message
...
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.
.
|