Different sum formula for different situations

  • Thread starter Thread starter Nancy Lytle
  • Start date Start date
N

Nancy Lytle

I have a report that is driving me crazy and I know it is something easy I
am just not getting.

The report is a summary of purchase requests that have been processed. The
information is summarized by FY; ProgramCode; Category and TransactionCode.
Each programCode has up to 3 categories and many transactioncodes. Each
transactioncode in a category in a program can have more than one purchase
request.

The problem is in summarizing when there is more than one purchase order
within a single transactioncode within a single category within a
programcode within a single year.



FY 1
Program 1
Category 1
Transaction 1
Purchase Request1
Purchase Request2
Sum for Transaction
Transaction 2
PurchaseRequest3
Sum for Transaction
Sum for Category - this is the problem area, I can get it work or either
Program 1or Program 2 but not for both situations
Sum for Program

Program 2
Category 1
Transaction 1
Purchase Request1
Sum for Transaction
Transaction 2
PurchaseRequest1
Sum for Transaction

Sum for Category - this is the problem area, I can get it work or either
Program 1or Program 2 but not for both situations
Sum for Program

As a note, the information about the ProgramCode, Category and
TransactionCode and Funding comes from one query based on one table and the
information on PurchaseRequests and PurchaseRequest Amounts come from
another query based on a different table, they join together on the
transactioncode and programcode and FY.

I would like to use something like the below code as the Control Source for
the Category Summary but can't seem to get it to work, it says missing
operator:

IIf((Count(qryCSPEL_rptELTEST.PR_PANumber) AS CountOfPR_PANumber FROM
qryCSPEL_rptELTEST GROUP BY qryCSPEL_rptELTEST.Transaction,
qryCSPEL_rptELTEST.Category, qryCSPEL_rptELTEST.ProgramCode) =1),
[CurrentAuth],Sum([CurrentAuth]))

Any ideas about the code or how else I could approach the problem

TIA,
Nancy
 
The Sum for Category should be in the Category Footer Section. All you
should need is an expression like:
=Sum([YourField])
 
Back
Top