Crystal,
Thanks for the pointers, but these are more basic than what I need.
I did a bit more trying, and remembering earlier help with DSum syntax,
came up with the solution:
=IIf([NPAmt]>DSum("Actual","qryForRpt","Cat = 'N' AND GtID = " &
[GtID]),DSum("Actual","qryForRpt","Cat = 'N' AND GtID = " &
[GtID]),[NPAmt])+IIf([PerAmt]>DSum("Actual","qryForRpt","Cat = 'P' AND
GtID = " & [GtID]),DSum("Actual","qryForRpt","Cat = 'P' AND GtID = " &
[GtID]),[PerAmt])
Sigurd
On 2017-07-13 08:49, crystal 8 strive4peace2008@yahoo.com
[MS_Access_Professionals] wrote:
> Hi Sigurd,
>
> perhaps these videos will be helpful:
>
> GROUPED REPORT USING WIZARD IN ACCESS THEN MODIFY
>
> https://www.youtube.com/watch?v=5kB3qISqhPI
>
> ACCESS - POLISH REPORTS
>
> https://www.youtube.com/watch?v=5V-o7llsKHo
>
> respectfully,
> crystal
>
> http://www.MsAccessGurus.com
> connect to me, let's build it together
>
> ~ have an awesome day ~
> On 7/12/17 4:31 PM, sigurd@solbakkn.com [MS_Access_Professionals]
> wrote:
>
>> To head off a possible question: the records for this report are
>> from a
>> query - the underlying tables do NOT have redundant entries for
>> NPAmt
>> and PerAmt.
>> Sigurd
>>
>> On 2017-07-12 14:45, sigurd@solbakkn.com [MS_Access_Professionals]
>> wrote:
>>
>>> I have a report for which I want to calculate some totals, and
>>> don't
>>> know how to put together the proper equations to do that.
>>> The individual records include fields GID, Cat, and Actual.
>>> The report is grouped on GID (Grant ID) and Cat (Category). I have
>>> the
>>>
>>> report calculating the sum for Actual in the footer for Category.
>>> There
>>> are two categories, N (Non-Personnel) and P (Personnel). Each
>>> category
>>>
>>> has a maximum billable, NPAmt and PerAmt, respectively. (Each
>>> grant
>>> has
>>> a single value for each maximum; those values are repeated in
>>> every
>>> record, hence available for use in calculations.
>>>
>>> In the footer for the category, I show, in addition to
>>> Sum(Actual),
>>> the
>>> appropriate maximum, and then the lower of the two figures as the
>>> amount
>>> actually billed for that category.
>>>
>>> I would like to have an accurate grand total billed for each
>>> grant,
>>> but
>>> need to compare the individual subtotals against the appropriate
>>> max,
>>> rather than comparing Sum(Amount) for the whole grant agains the
>>> sum
>>> of
>>> the category maximums. The calculation would be on the order of:
>>> Min(Sum(Amount) for category N,NPAmt) + Min (Sum(Amount for
>>> category
>>> P,
>>> PerAmt).
>>>
>>> Any help on the syntax for this calculation would be greatly
>>> appreciated.
>>> Sigurd Andersen
>>>
>>
>> ------------------------------------
>> Posted by: sigurd@solbakkn.com
>> ------------------------------------
>>
>> ------------------------------------
>
> -------------------------
> Posted by: crystal 8 <strive4peace2008@yahoo.com>
> -------------------------
> ...
Posted by: sigurd@solbakkn.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar