Establishing primary & foreign keys

[Rachel]

I am a first time database builder for the non-profit organization I work
with and am needing some pointers. I understand how to use the software and
the templates give a great start, but designing the ideal database has left
me a bit stumped... The goal is to establish a mailing list of all donors,
and at the same time, or on another table, keep track of all the individual
donors to make reports for tax purposes.
If I have Access assign an auto number to each donor entry as primary
key(contact ID?), what would be a good primary key to have on the second
table to help keep record of thieir donations (Contact type such as, IND for
indiviual?)? But would that make it dificult to pull up information through
queries?--
Rachel

 

[Barry Gilbert]

If I understand you, each donor may have multiple donation records. Is this
correct?

I agree that an autonumber is a likely candidate for a primary key on the
donors table. It might also work as a primary key on the donations table. In
the donations table, you'll want another column called DonorId that is a
foreign key to the donors table. This will allow you to build queries to find
all donations per donor.

Autonumber fields are useful for primary keys, unless they will be used by
people to refer to records. It might be easier to people refer to Mr. John
Smith as Smith002 than as 120471. That's something you have to decide based
on how people will interact with the data.

Barry

 

[BruceM]

To what Barry has written let me add that if the primary key is autonumber
then the foreign key field (it is often helpful to give it the same name as
the corresponding primary key) can only be a number field (long integer).
The primary key is established in table design view, but the foreign key
exists only through its relationship to the primary key. More on that in a
moment. Note that there will almost surely be gaps in the autonumber
sequence, in case that matters (e.g. the users need to see the number).

tblDonor (Donor table)
DonorID (autonumber primary key)
FirstName
LastName, etc.

tblDonation (Dontation table)
DonationID (autonumber primary key)
DonorID (foreign key - long integer)
DonationDate
DonationAmount
etc.

Click Tools > Relationships, add both tables, drag one DonorID field onto
the other, and click Enforce Referential Integrity. DonorID in tblDonation
may now be thought of as the foreign key, but will not be identified as such
in table design view or anywhere else I'm aware of.

I don't know anything about the template you are using. A typical interface
for this kind of setup is form/subform (based on tblDonor/tblDonation).
Post back if you need help setting up the forms.

 

[Rachel]

Thank you very much for the tips! They are shedding some major light! Here's
another question. If I create two tables just like the examples you both
gave, would I be able to pull all donors through queries (at the end of the
year) and create a report to send to each donor that gives their donation
detail and total by bulk mailing?
And yes, if you could share some form creating tools, I'd really appreciate
it! Thanks again!

Rachel