beginner over her head

[Guest]

I converted a donation database to Access this year because I like Access a
lot more than the old Mac program I was using. I use this databse to input
donations that are made to 4 different funds that I report on. I now need
to make a monthly receipt for my donors, and this is where I am getting
stumped. Because some have split their checks to more than one fund or
because I have posted more than one donation for some donors, I am getting
duplicates. I would like my receipt to say thank you for your donation to
fund x and z, and I would like to say the total donations for the month were
$ and the check numbers were # and #. Please help, here are my tables -

DonorsTable -
Donor Id
Name and address fields

DonationMainTable
DonationNumber
date
check number
check amount
donor id (from DonorsTable)

DonationDetailsTable
Fundnumber (I have 4 funds)
Donation Number (from DonationMainTable)
Amount (so that a check amount can be split to this fund and another)

I made a form that consists of all 3 tables. When I enter my info, I pull
up a donor field for name and addresses, I then enter the new info in the 2
sub forms.

Does this make sense? My duplicates are occuring in the donation number
field, which it should, I think.

I guess the first thing I want to know, Is it possible to do what I want to
do? If so, how?

 

[Phil]

I noticed you did not get a response yet. Your post is a little
confusing (to me at least.)

You should be able to do what you want to do, but maybe not with the
data you have to work with.

Looking at the structure of your databases, everything looks cool.

MsAccess loves to double and triple things up if you don't get the joins
right.

I am going to assume it is some report where you are getting the doubling?

This may or may not be a problem with the report, or just what you want
to see.

I would go about this problem with a crosstab.

Make a crosstab report that takes each donation (and/or donor, depending
on whether you want a reciept for each donation, or each donor, )as a
row header, fund as a Coloumn header, and the amount for that fund as a
value.


This will result in:


Donor # Donation# Fund1 Fund2 Fund3 Fund4
1 1 100 0 50 50
1 2 100 100 0 0
2 3 0 0 200 0


etc.

Crosstabs have parameter issues, make sure every fund name is a
parameter in the query, so you can access them with another query


Now that you have this, use Concatenation and iff statements. (This is
the basic idea, look up the exact syntax.)

Your thank you line" would have something along the lines of


iif(fund1=0,"","Fund1 Donation of "&fund1)
iif(fund2=0,"","Fund2 Donation of "&fund2)
iif(fund3=0,"","Fund3 Donation of "&fund3)
iif(fund4=0,"","Fund4 Donation of "&fund4)


What this does is check the amount for each fund. IF it is zero, they
did not donate to that fund, and what prints is ""(nothing), if it has
an amount, then it prints the text, "Fund1 Donation of " and the amount
in Fund1

the "&" is what concatenates things together, so they become one thing.
each of the fund "Iifs" can be contanated together as well, or left
ona singel line, depending on how you wnat yoru reciept to look.

Look up the iif statement, and crossstabs, and play with it a bit.

Then come back with any questions.

Phil

 

[Guest]

I actually figured out yesterday that I can do what I need to do in a report
instead of doing it in a query. Yes, my post sounded confusing because I am
confused. But I get very excited each time I figure just one little thing
out, like my report yesterday. I posted a new question regarding putting
bullets in my report, maybe you can help me there too!