Your guidance

[Glennzo]

Hi everyone. I am going to create a database that will track weekly pledges for about 70 active members. I already have a 'membership' table that contains names and addresses, etc.

We need to track items such as pledge type (weekly, monthly, etc ) and amount. I also need to calculate totals for the month overall and by member. Each member also will have an 'envelope number' assigned in numerical order.

My membership table has the following fields:

MemberId (autonumber)
LastName
FirstName
Address
City
State
ZipCode
HomePhone

Should I create a new table with items such as pledgetype, pledgeamount, and weekly labels like Jan1, Jan2, Jan3, etc? What would be the best way to link these tables?

Thanks in advance.

 

[Rob.Mills]

You do need to setup a separate table for the pledges and relate it to the member table. That should include pledge type, amount and date.

 

[Glennzo]

Thanks for the reply Bob. I'll create a table with the following fields:

PledgeId - AutoNumber
MemberID - Number ( Link this one to MemberId in the Members table? )
PledgeType - Number
PledgeAmount - Currency
TotalPledge - Currency ( do I need this in the table or should I do the calculations on the form and report? ) Calculation will be PledgeAmount * PledgeType.
Date - Date/Time

Now, should I also add the fields for 52 weeks in this table or in a third table?

Thanks again...

 

[Rob.Mills]

Don't put the calculated field in the table. Rule of thumb is that tables are only for storing data.

Not sure what you mean by "should I also add the fields for 52 weeks in this table or in a third table?

 

[Glennzo]

I'm tracking weekly pledge amounts for church members. I plan on entering the amounts that each member gave on a weekly basis along with the initial pledge and pledge type. Will also need to have totals on a monthly basis, individually and overall.

Initial pledge is the amount that they pledge at the beginning of the year. Pledge type, how often they will donate, is weekly, monthly, quarterly, yearly, etc. Total pledge will be pledge amount * pledge type. Example, a pledge of $10 per week is of course $520 per year, $100 per month is $1200 per year and so forth. Reports will show total pledge sums for the whole church for the year, track how closely we're meeting the pledges at any time during the year, summarize other year to date figures.

At the end of the year we will print statements for all the members for tax purposes.

Thank you for your interest Rob.