A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

A/R tables



 
 
Thread Tools Display Modes
  #1  
Old March 25th, 2005, 11:48 PM
Annie
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2005, 02:03 PM
Scott McDaniel
external usenet poster
 
Posts: n/a
Default

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  
Old April 4th, 2005, 10:11 PM
Annie
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 03:07 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.