Combining identical primary keys

[Geitebil]

I am pretty new to Access and most is accomplished by trial-and-error, with
help of books, and most of all by this Office Discussion Online. But now I am
stuck and hope somebody can help me.
I have a Table (called 'Paid') with the fields 'Date' "Donor' and "Amount"
("date" is a Date-field; "amount" is a currency field and "Donor" is a number
that is also primary key. Each Donor is represented by a number.)
I can easily call up the donations each donor made on a certain date / his
sum of donations (even within a specified "Start" and "End" date. No trouble !
My difficulty is as follows: When making a Report I would like to see in one
entry what is the sum total a certain donor gave over a certain period of
time. I always get multiple lines indicating the donor with his donation on a
particuler date. I, however, want the donor's number mentioned once, and the
sum total donated within a specified period mentioned once.
Could anybody help me please. (VBA is difficult for me !)
Thanks in advance

 

[Albert D. Kallal]

I have a Table (called 'Paid') with the fields 'Date' "Donor' and "Amount"
("date" is a Date-field; "amount" is a currency field and "Donor" is a
number
that is also primary key. Each Donor is represented by a number.)

Are you sure the primary key of he above table is "donor". I think the
correct term is called foreign key.

In other words, that "donor" field is simply the field used to relate *back*
to the donor table.

So, here how you do this.

Fire up the query builder, and drop in the donors table. Drop in first name,
last name, and any other fields you need.

then, create one new column, and enter the following additional column in
the query builder:

DonationTotal: (select sum(Amount) from tblPaid
where tblPaid.Donor = tblDoner.Donor
and Date >= #mystartDate# and <= #myendDate#)

In the above, you would replace mystartDate with your date, eg:

#01/01/2007#

I also assumed that in your table donor (tblDonor), that the primary key is
"donor",and often in ms-access we simply used "id" as the default primary
key. (but, just change the above to whatever you are actually using).


Try the query, save it....get it working. Then simply base your report on
the above...you will get one line per donor, and you be able to list/use
information from the "main" donor table. such as phone numbers etc too...

 

[Steve]

Note - Date is a reserved word in Access and should not be used as a field
name. Sooner or later you are going to run into trouble. You need to change
the name of this field.

To answer your question ---
Create a query that contains the fields:
Donor
Date
Amount

Put an appropriate croteria in the Donor field to get the donor you want.
Put the following expression in the criteria of the Date field:
Between [Enter Start Date] And [Enter End Date]

With the query in design view, click on the Sigma button (looks like a
capital E) in the menu at the top of the screen. Under Amount, change
GroupBy to Sum. The query will return the the sum total the donor gave
between start date and end date. use this query for the recordsource of your
report.

Another thing ---
If you do not set a criteria for donor, the query will return the sum total
each donor gave between start date and end date.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Spencer]

IN the query grid

-- Select donor, Amount, and Date fields
-- Select VIEW: Totals from the menu
-- Under Amount change GROUP BY to SUM
-- Under Date change Group By to WHERE
-- Under date in the criteria add Between somestartdate and someenddate
(replacing somestartdate and someenddate with the dates you want)

That should return a list of all donors and the total they gave during
the specified date range.

By the way Date is not a good name for a field since Access has a Date
function and it is possible to get the Date function confused with the
date field. DonationDate would be a better name.

Also, if you want you could change the criteria to
Between [Enter Start Date] and [Enter End Date]
If you do so you will be prompted for the two dates when the query executes.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================