This isn't really a reply to John Vinson's answer, but it kind of builds
on it by showing a couple of examples of what you might do in a Query.
Since it wasn't fully clear to me exactly what you were after, I ran a
couple of variations. You can use them, as John suggested, as the basis
for a Form or Report, and the repeated values can go into section
headers or footers (an example Report follows). I might mention here
that, although you can set a "Running Sum" property in a Report, I have
had trouble getting it to behave the way I'd like... plus, there's a
limit to how many running sums you can specify in any one Report.
OK, here's a sample Table of donations that will serve as the basis for
our Queries.
[Donations] Table Datasheet View:
Donations_ID Date Amount DonorName
------------ ---------- ------ ---------
-1594720383 10/5/2005 $10.01 Barb
-102283622 10/17/2005 $10.01 Jim
-102125155 10/30/2005 $10.02 Jim
-1188645492 10/17/2005 $10.04 Jim
1268755840 10/30/2005 $15.08 Jim
679105943 10/17/2005 $10.01 Sue
1148080046 10/5/2005 $10.02 Sue
First Query is simple, just a total of the amounts each person donated.
(Is this what you meant when you said you wanted to "filter by
selection using the DonorID as the primary key to show the total"?)
[Q_TotalsByDonor] SQL:
SELECT T.DonorName, Sum(T.Amount) AS SumOfAmount
FROM Donations AS T
GROUP BY T.DonorName
ORDER BY T.DonorName;
[Q_TotalsByDonor] Query Datasheet View:
DonorName SumOfAmount
--------- -----------
Barb $10.01
Jim $45.15
Sue $20.03
We'll use it later.
Next Query lists the amount of money in our piggy bank, by date, from
whoever gives it, along with the donors and amounts.
[Q_RunningDonationsByDateThenDonor] SQL:
SELECT Count(T2.Amount) AS Seq, T1.Date,
T1.DonorName, T1.Amount AS Amount,
Sum(T2.Amount) AS [Running Total]
FROM Donations AS T1, Donations AS T2
WHERE (((T2.Date)=[T1].[Date])
AND ((T2.Donations_ID)<=[T1].[Donations_ID]))
OR (((T2.Date)<[T1].[Date]))
GROUP BY T1.Date, T1.DonorName, T1.Amount, T1.Donations_ID
ORDER BY T1.Date, T1.Donations_ID;
[Q_RunningDonationsByDateThenDonor] Query Datasheet View:
Seq Date DonorName Amount Running Total
--- ---------- --------- ------ -------------
1 10/5/2005 Barb $10.01 $10.01
2 10/5/2005 Sue $10.02 $20.03
3 10/17/2005 Jim $10.04 $30.07
4 10/17/2005 Jim $10.01 $40.08
5 10/17/2005 Sue $10.01 $50.09
6 10/30/2005 Jim $10.02 $60.11
7 10/30/2005 Jim $15.08 $75.19
The [Seq] field is there to keep the records in order in a Report, as
the Report pretty much ignores the order in which records are presented
in a Query. You have to set the Sorting and Grouping properties all
over again, and without something like [Seq], the records for 10/17/2005
could appear in any order in the Report, making the [Running Total]
field for that day look kind of silly.
The next Query sorts the same data differently, and it also includes the
total for each donor. These values are repeated from one record to the
next, but in a Report you can choose the repeated value from any of the
records in a group and display it in the group's header or footer band
in your Report.
[Q_RunningDonationsByDonor] SQL:
SELECT T1.DonorName, T1.Date, T1.Amount,
Count(T2.Amount) AS Seq,
Sum(T2.Amount) AS [Running Total by Donor],
[Q_TotalsByDonor].[SumOfAmount] AS [Total By Donor]
FROM (Donations AS T1
INNER JOIN Donations AS T2
ON T1.DonorName=T2.DonorName)
INNER JOIN Q_TotalsByDonor
ON T1.DonorName=[Q_TotalsByDonor].[DonorName]
WHERE (((T2.DonorName)=T1.DonorName)
And ((T2.Date)<T1.Date)) Or (((T2.DonorName)<T1.DonorName))
Or (((T2.DonorName)=T1.DonorName) And ((T2.Date)=T1.Date)
And ((T2.Donations_ID)<=T1.Donations_ID))
GROUP BY T1.DonorName, T1.Date, T1.Amount,
[Q_TotalsByDonor].[SumOfAmount], T1.Donations_ID
ORDER BY T1.DonorName, T1.Date, T1.Donations_ID;
(I know, the SQL looks kinda hairy, but it's not quite so bad in Query
Design View. That complex-looking WHERE clause makes sure that only the
records up to and including the current one, in the current sorting
order, are included in the running total. If you change the sorting
order you'll probably want to change the WHERE to match it.)
Notice that in this case, [Seq] starts over with each new donor, and the
[Total By Donor] is the same for all records with the same [DonorName].
[Q_RunningDonationsByDonor] Query Datasheet View:
DonorName Date Amount Seq Running Total Total
by Donor By Donor
--------- ---------- ------ --- ------------- --------
Barb 10/5/2005 $10.01 1 $10.01 $10.01
Jim 10/17/2005 $10.04 1 $10.04 $45.15
Jim 10/17/2005 $10.01 2 $20.05 $45.15
Jim 10/30/2005 $10.02 3 $30.07 $45.15
Jim 10/30/2005 $15.08 4 $45.15 $45.15
Sue 10/5/2005 $10.02 1 $10.02 $20.03
Sue 10/17/2005 $10.01 2 $20.03 $20.03
Now we use this Query as the data source for a Report, and the Report
allows us to format the values a little better. Although you don't see
it there, the [Seq] field is one of the sorting keys in this Report:
[R_RunningDonationsByDonor] Report printed output:
+-------------------------------------------------------
| R_RunningDonationsByDonor
|
| Total By Donor
|
| Running Total
| DonorName Date Amount by Donor
| --------- ----- --------- --------------
|
| Barb
| 10/5/2005 $10.01 $10.01
| -------
| Total from Barb $10.01
|
|
|
| Jim
| 10/17/2005 $10.04 $10.04
| 10/17/2005 $10.01 $20.05
| 10/30/2005 $10.02 $30.07
| 10/30/2005 $15.08 $45.15
| -------
| Total from Jim $45.15
|
|
|
| Sue
| 10/5/2005 $10.02 $10.02
| 10/17/2005 $10.01 $20.03
| -------
| Total from Sue $20.03
|
|
|
+-------------------------------------------------------
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
John said:
Is there a way or what is the expression used if I want to do a running
total in a query under a field called "Amount Donated". In addition, I
would like to filter by selection using the DonorID as the primary key
to show the total.
Thanks.
A Query can't easily display both detail values and grand totals; I'd
suggest using a Form (for onscreen use) or Report (for printing). On a
Report you can simply add a textbox with its Running Sum property set
to Over All. In either case you can put a textbox on the Footer (form
or report footer, not page footer) with a control source
=Sum([Amount Donated])
to display the total.
To get a running sum in a Query, you need some field within the query
which is strictly ascending. Assuming you have a DateDonated, try
=DSum("[Amount Donated]", "[Donations]", "[DateDonated] <= #" &
[DateDonated] & "# AND [DonorID] = " & [DonorID])
Your Query can use a criterion such as
=[Forms]![frmCrit]![cboDonorID]
to reference the value of the combo box named cboDonorID on form
frmCrit.
John W. Vinson[MVP]
