Ok, I think I see now. Seems like there's two ways to go
here. A quick and dirty approach (not very flexible for
future needs) would be to use 8 text boxes in the report's
footer section. Each text box would use an expression like:
=Sum(IIf(chgnum = "123", chgamount, null))
The other, more general, approach would be to create a table
for all the chgnum values and a YesNo field to indicate
which ones to total in the report. (You probably already
have a table with all the chgnum values, if so, just add the
YesNo field to that table.) Then create a crosstab query to
calculate the totals for each chgnum with the YesNo field
set to True. The query's SQL will be something like:
TRANSFORM Sum(chgamount) AS SumOfchgamount
SELECT "Totals" AS Expr1
FROM table
WHERE YesNo
GROUP BY "Totals"
PIVOT chgnum
After you create a report based on the query, you can then
display the totals in your report by using that report as a
subreport. This might get a little tricky getting the
subreport to bind to the fields selected in the YesNo field,
so come on back after you've got all the pieces and we'll
try to help you put it all together.
--
Marsh
MVP [MS Access]
Sorry. Using 0 instead of null might be my answer. Let me try again on my
explanation...
I have a table with [charge number] and [charge amount]. I want to pull
[charge amount] on 8 different [charge number]s and total them in a report.
Using iif to create separate fields in my query to identify each specific
[charge number] and then totalling those 8 new fields seemed cumbersome and
the wrong way to achieve my goal.
Marshall Barton said:
Sorry, but I don't understand what you're trying to achieve
here.
However, your statements above are missing the parenthesis
and should be:
IIf(chgnum = "123", chgamount, null) as amt1
You may not want Null if you're going to add the two values,
maybe 0 would be more appropriate??
I have a field chgnum. I want to select a different field chgamount for
certain chgnum. My first thought was to do the following in a query and
total them in a report, i.e. =([amt1] + [amt2])
iif chgnum = "123", chgamount, null as amt1
iif chgnum = "234", chgamount, null as amt2
The above doesn't work and I really think there has to be a better way to do
this.
