Tips for a newbie - setting up tables

[Guest]

I want to run this thought by somebody if you don't mind since I am new and
I'd rather spend time planning than fixing.

I work for a nonprofit org and I am trying to transfer my donor database to
Access. Many of my donors will give to more than one fund at a time. So
this is what I am thinking.

I will make a table with donor information. Date, name, address, the check
amount and #. Then I will make a table for each fund where I will record how
much should be credited to that fund.

Example,
Mr. Donor gives us a $1,000 check but wants $500 to go to fund A and $500 to
go to fund B. So I will record $1000 in the donor table for tax purposes,
but then each fund will be credited also.

Question - should the date be the link between tables?

Does anyone have any better ideas or tips for me to get this started.

 

[Rick B]

I see a flaw right off.

You say you will have a table with Date, name, address, the check amount and
#. This should be at least two tables. If John Doe writes a check every
month, you don't want to enter his name and address each month.

Id have the following (at least)

TableDonors
DonorIdentificationNumber (Primary Key)
DonorFirstName
DonorLastNAme
DonorAddress1
DonorAddress2
DonorCity
DonorState
DonorZIP
DonorHomePhone
DonorWorkPhone
etc.


TableFunds
FundNumber (Primary Key)
Fund Name
etc.



TableDonationMain
DonationNumber (Primary Key)
Date
Method (cash, check, etc.)
DonorID (foreign key to TableDonors)
TotalAmount
etc.


TableDonationDetails
DonationNo (Foreign Key to TableDonationMain)
Amount
FundNumber (Foreign Key to TableFunds)




Then, a person or organization would have one entry in TableDonors. But
might have many entries in the TableDonationMain if they have given several
times.

One check would have one entry in the TableDonationMain. If this check is
split between three funds, you'd have three entries in TableDonationDetails.


Hope that helps a bit.

 

[Guest]

Thanks for catching my flaw and the new suggestions.

I am thinking forward to reports I will need to make at the end of each
month. I will need to report on each fund individually. Each fund will want
to know who their donors are. So I am thinking that I will have a query for
each fund that will include TableDonors, TableFunds, and
TableDonationDetails. Am I on the right track?

I will also need to print a receipt for each donor - monthly and annually.

 

[Vincent Johns]

This looks good to me, but I'd omit the
[TableDonationMain].[TotalAmount] field, as that is easily calculated by
summing the [TableDonationDetails].[Amount] values that are linked to
it. (You'd use a Query to do the arithmetic.) A redundant field
occupies space and requires effort to maintain, to little or no purpose.

If you do include both, you might write a Query that would list all the
cases where they don't match, for use in maintaining your database.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Thanks for catching my flaw and the new suggestions.

I am thinking forward to reports I will need to make at the end of each
month. I will need to report on each fund individually. Each fund will want
to know who their donors are. So I am thinking that I will have a query for
each fund that will include TableDonors, TableFunds, and
TableDonationDetails. Am I on the right track?

I think you want a Query for all funds (not "each fund") that will
include what you need. You can then display (report on, print, etc.)
either information for all the funds, in some order that you choose (for
example, alphabetically by fund name), or for a selected fund (by
specifying its name in a WHERE clause, or in a list box control on a
Form, or by right-clicking on the fund name in Query Datasheet View and
choosing Filter By Selection, etc.)

I will also need to print a receipt for each donor - monthly and annually.

For that, you'd define a Query that will sum the donor's donations for
the specified period, grouped by donor's identifier, maybe sorted by
donor's last name or postal code. That Query would be the data source
for a Report that would produce the receipts.

For fancier receipts, you could use MS Word's "Mail Merge" facility,
using your Query as the data source. You could then also easily include
the fancy graphics that Word does well, such as a curly border or
watermark images.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.