Design Question

[Greg]

I currently have a database that we use to record Donations, currently as you
add donations new records are created as it should. The problem is if we have
a person, or business that routinely donates, say 3 times a year, we then
have 3 different records. How can I set it up so that I only have 1 record
(if its possible). Thanks in advance

 

[Allen Browne]

Greg, you will need to create these tables:
- a Donor table (one record per person, with a DonorID primary key);
- a Pledgetable (one record per pledge, with a DonorID indicating who
pledged what amount and how often);
- a Donation table (one record for each time money is received, with a
DonorID indicating who gave the money, and a PledgeID which is blank if the
dontation is not for a pledge.)

The choose Relationships on the Tools menu, to create the relationships
between the tables.

 

[mscertified]

I think you SHOULD have more than one record for the person as you say you
have. There is nothing wrong with that, in fact its the best way to do it.
Why is this creating a problem? When you want to gather information for the
person you just run a query which pulls the information from all the records
for the person.
This is the way Access was designed to work.

Dorian

 

[gerardb5]

you shoud NOT have more than one record per person, as mscertfied
advices. This is redundancy and is exactly what every database admin
will tell you to avoid. This takes up more space than necessary, it
enters duplicate information into the database, and this allows errors
to creep into the database. For instance, say you enter in the donor's
name 5 times for 5 different entries. If you make an error entering
either of the entries then it's not the same donor any more. Secondly,
what if some of the donor's information changes. Do you want to hae to
go back and edit numerous occurences of the same data?

In short, to have the best structured database is to always always
always reduce the duplication of the same data. Allen Browne's post
gives an excellent option: create a table and enter the donor's
information once. Give this table a primary key which is simply some
number. This number is then associated with the donor. In another
table, put the information for each donation made and put the donor's
primary key number into the field for who made the donation.

just my two cents...
G

 

[Jeff Boyce]

If we're talking about information about the person (e.g., first name, DOB,
etc.), I agree that you'd only want one record per person.

If we're talking about donations/contributions made by a person, we DO want
one record per person-contribution, detailing donation-date, amount, etc.

Two different tables.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Greg]

Thanks all for your advice....gerard5 that is just what it happening....Each
time someone enters a donor and makes a small mistake it creates a whole new
donor per say....so that before we run any queries or reports we have to
clean-up the data and make sure every matches....

 

[Greg]

The info we collect is name/business name, address, phone, amount, when they
donated, when they paid, how they paid, was it a certain fundraisier they
donated to...

 

[Jeff Boyce]

Greg

How close is your table design to that suggested by Allen up-thread?

Regards

Jeff Boyce
<Office/Access MVP>