advice for form and table design

[Kelly]

I am working on transerring a database from Panorama into Access. The form
deals with donations from donors for up to 5 different funds. In Pan. each
donor has a donor # (Primary key). When the donor gives to more than one
fund, they previously made a new record, using the same donor # just
different recipient info. I am sure there is a way to narrow down the
records to just one per donor with more than one recipient on the same
record. What I want to say is that Mr. Donor has given $x to fund A, $x to
fund B, and so on. This sounds easy and I am sure that I can figure this out
with a lot of thought. What I need to do at the end of the month is to let
each fund know who they recieved gifts from. I am making a lot of changes
from my previous database and my creative juices are running dry. I guess I
am looking for a starting point or just a word of encouragement. Any tips
out there?

 

[John Nurick]

Hi Kelly,

The standard way of doing this is to have one table for Donors (with a
DonorID primary key), another for Funds (FundID), and a third for the
donations, something like this:

tblDonations
DonorID*
FundID*
DonationDate*
Amount

The fields marked * should all be in the table's primary key. (This
structure will need to be modified if it's possible for any donor to
make more than one donation to the same fund on the same day.) Each
donation is stored as a record in tblDonations (the saying goes in
relational databases, records are cheap, fields are expensive).

To display and work with the data, you use a form-and-subform. (There's
an example of this in the Northwind sample database that comes with
Access, in the "Orders" form and "Order Subform" subform).

The form needs to be bound to the table of Donors, and the subform to
tblDonations, with form and subform linked on DonorID. The subform
should be in continuous view. To display the recipient's name instead of
just the fund ID, use a combobox on the subform, bound to FundID and
with its row source getting the data from the Funds table.

 

[gls858]

I am working on transerring a database from Panorama into Access. The form
deals with donations from donors for up to 5 different funds. In Pan. each
donor has a donor # (Primary key). When the donor gives to more than one
fund, they previously made a new record, using the same donor # just
different recipient info. I am sure there is a way to narrow down the
records to just one per donor with more than one recipient on the same
record. What I want to say is that Mr. Donor has given $x to fund A, $x to
fund B, and so on. This sounds easy and I am sure that I can figure this out
with a lot of thought. What I need to do at the end of the month is to let
each fund know who they recieved gifts from. I am making a lot of changes
from my previous database and my creative juices are running dry. I guess I
am looking for a starting point or just a word of encouragement. Any tips
out there?

Kelly,

See if this link helps any. It's an Access template for donations.
May not be exactly what you're looking for but it will give you
an idea of how tables, queries,and reports work. Watch for line
wrap on the URL.

http://office.microsoft.com/en-us/templates/TC010185821033.aspx


gls858