[AccessD] Same report 2 different summary cases

Nancy
I'm sure there'll be other ideas but could you take the Current
Authorisation out of the query, leaving the query to list all the purchase
requests, and put it into the report as an unbound calculated field with a
Dsum?

Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Nancy J Lytle
> Sent: 27 November 2003 16:45
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Same report 2 different summary cases
> 
> 
> 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 - each trans has initial funding 
> plus changes in funding that equal the current authorization
>                 Purchase Request1 
>                 Purchase Request2
> 
>             Transaction 2
>                 PurchaseRequest3
> 
> Category Summary:   CurrentAuthorization: Initial Authorization +
> Changes. The problem is that if the Transaction has more than 
> one PR it adds a Current Authorization amount for each PR 
> there is, which means in this case the Current Authorization 
> would be twice what it should be.
> 
> Program 2
>         Category 1
>             Transaction 1
>                 Purchase Request1
>                 
>             Transaction 2
>                 PurchaseRequest1
> 
>    
> Sum for Category - this is the problem area, I can get it 
> work or either multiple PR's per Transaction or  one PR per 
> Transaction but not for both situations.
> 
> Actually I am doing sums for Transaction, Category and 
> Program, but it is only the Category one I am having problems 
> with at this point
> 
> I would like to use something like the below code as the 
> Control Source for the Category Summary to account for the 
> two different situations but can't seem to get it to work, 
> the error message 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
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/a> ccessd
> Website: 
> http://www.databaseadvisors.com
> 
>