How do I search for a name/specfic info?

[Guest]

I am trying to create a database of people who have given money to non profit
organizations, which is the field I work in. I have Excel spreadsheets for
individual donors who have given in the past. The spreadsheets contain the
donor name, the date they gave, how much they gave, who they gave to, and a
couple of other colums, so there isn't much to these spreadsheets, though
some people have given multiple times to a ton of organizations.

What I want to do is upload spreadsheets on several hundred donors. Then I
would like to be able to find people who have given to the same organizations
or people who have given over a certain amount or people who have given in a
certain time period.

I also have spreadsheets from some of the organizations that have the
details as well. Would it be better to upload these files and then search
the database for those donors who I might know by name? I'm a novice with
Access but I know some basics. This part is kicking my butt. Please advise.

 

[Rick B]

If you are simply going to upload these as flat files, why bother? Just
search the spreadsheets.

To put this in a database, there is much more to it than just "uploading
them".

First, you have to build your database structure. You have a many to many
relationship (at least).

You need a table to store the details about your donors.
You need a table to store the details about the organizations.
You need a table to store the donations. This might need to be further
broken down if each "check" could be split between more than one
organization. If I send a $100 check and ask you to split it between two
charities, for example.

In short, an Access database is NOT a glorified spreadsheet. Access is a
relational database.

 

[Guest]

I appreciate your advice and perhaps you are right. To address one of your
points about the checks, the tax laws are pretty tough, so there is no
splitting of the checks. Each check is written to and given to one org.

As I said, I'd like to have a "master list" of all this information so I can
search by date or amount or whatever else. In the future, my lists will be
from org's and not of the particular donors, so what would be better then?
The spreadsheets or the database? What if I want donor profiles or call
sheets?

Thanks again.

 

[Rick B]

I would build it in a database. You will be able to produce multiple forms
or reports using the data and there will be no need to duplicate
information. You can produce stats and compare figures to your history.

Lots more flexibility in the database.

 

[Guest]

So now I'm back to square one where I need to know how to do this. I knew
someone who did something similar to this about 4 years ago but I lost
contact.

Let's say I want to upload the spreadsheets of the organizations into
tables. Let's say Org A and Org B. Do I just upload those spreadsheets as
tables? If so, how do I search for one particular donor who might have given
to both, say "John Doe" or how do I search for only those people who gave
over $500 at any one time?

 

[Rick B]

Again, it is not that simple. You can IMPORT the spreadsheets into your
database to get the data in there, but I would not use any of the tables
created by the import. Create your own tables using a structure like the
one I recommended. Then, create several append queries to grab specific
information from your uploaded tables and add that information to the newly
designed tables.

For example. Build your "tblDonor" with a structure something like...

DonorNumber
DonorLastName
DonorFirstName
DonorAddress1
DonorAddress2
DonorCity
DonorState
DonorZip
DonorHomePhone
DonorType (maybe?)



Then, create an append query to grab all the records from your imported
table1 and select the fields needed to populate the table we just built.
Run it, and all the appropriate data should now be in the donor table.
Modify that append query to pull all the donor data from table2. Run it to
append all that data. Repeat this process until all the donor records exist
in your tblDonor.

Then, create your organization table with a structure something like...

OrgTaxIDNumber (or some similar unique key)
OrgName
OrgAddress1
OrgAddress2
etc.

Again, create an append query(ies). blah blah blah.


Just continue this until all the data is in your properly formed tables.
You will need at least one other table to track each payment/donation made.
It would look similar to the following...

tblDonations
DonNumber
DonDate
DonAmount
DonOrganization (would be the same as the TaxID number or similar field
in the tblDonor)
DonDonorNumber (would match the person's number in tblDonor)



Hope you get the idea. Once you have all the data where it needs to be,
then you can start working on the forms you would use to add new data to the
system. I'd build a Donor Maintenance form to allow you to add/modify
Donors. I'd create an Organization Maintenance form, and a seperate form to
post donations that you receive. I'd probably add a button to that form
labeled "Print Receipt" that would let you produce a receipt after posting
the payment.

I'd create a "receipt" report to be used in the form above. I'd create a
"Donor Statement" report to allow you to produce tax forms at the end of the
year. (I am not sure which IRS form is sent at the end of the year, or if
you even need to do that.) I'd create an "Organization Recap" report to
allow you to run a list of donations for any given time frame.

Good Luck. Post back as you run into specific problems. You may want to
look at the various templates available on the Microsoft website and see if
you can use any of them as a starting point. Most of them have customer and
vendor tables. You could modify them to meet your needs.