My boss wants me to build a database for people who donate money to us (which
I've never used Access until now). He wants me to set it by months, the
donation, the date the donation was given, nad the total year to date. On the
form we're using, he wants it where after the donation has been put in that
it will add the donation to the year to date and then equal the year to date.
At the office here they used to use Q & A and they said that program did it,
so Access should. Could anyone help me with this? Am I even headed in the
right direction? Thanks so much.
If neither you nor your boss has any experience with relational database
design - as appears to be the case - you'll likely find this a challenge. But
it's not that big a task and probably we can help!
First off, Access doesn't come with a database all set up with Donation
Screens and reports for your particular application. It's more like a box of
Tinkertoys - it doesn't have a windmill or a sportscar in the box, but you
have all the parts needed to build one!
First off, don't confuse data PRESENTATION ("by months", "total year to date")
with data STORAGE. The foundation of your database is the Tables (don't get
into forms and reports until the tables are done!). I'd suggest two tables
(you'll probably need more later):
Donors
DonorID <Autonumber, Primary Key>
LastName
FirstName
<contact info such as address, phone, email, ...>
Donations
DonationID <Autonumber, Primary Key>
DonorID <Long Integer, joined to Donors.DonorID in relationships window>
DonationDate <Date/Time>
Amount <Currency>
<any other fields about this donation, e.g. comments, restrictions,
dedications...>
You'ld put together a Form based on the Donors table (to enter and find
donors), and a Subform based on Donations (to enter each donor's donations). I
You can easily *calculate* the year to date on a Form or Report (once you have
your tables set up and data in them). For instance, you could put a Textbox on
the form with its Control Source property set to
=DSum("[Amount]", "[Donations]", "[DonorID]=" & Me!DonorID & " AND
DonationDate >= #" & DateSerial(Year(Date()), 1, 1) & "#")
to sum up all this donor's gifts so far this year. There is no need to (and
many good reasons NOT to!) store this value in any table.
John W. Vinson [MVP]
