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
|
|||
|
|||
A/R tables
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Criterion - How to Write Query for Multiple Tables | jcinn | Running & Setting Up Queries | 1 | February 8th, 2005 01:42 PM |
need to join local tables with sql 7 tables using guids | Susan via AccessMonster.com | Running & Setting Up Queries | 0 | January 31st, 2005 09:58 PM |
Linked tables | Loi | New Users | 1 | January 26th, 2005 09:57 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |