glad you figured it out although ...
>> more basic than what I need <<
with all due respect, I don't think so ... watch later on (started easy
so beginners can follow too) -- unless perhaps I misunderstood what you
are after (you said, "compare the individual subtotals against the
appropriate max -- hence eliminate DSum) ... are you not wanting to
perform calculations in a section (such as Detail) using a calculated
value(s) from a different section (ie: group/report) level? Controls can
have Visible = No (false) if they are only used to calculate something else.
~crystal
On 7/13/17 10:58 AM, sigurd@solbakkn.com [MS_Access_Professionals] wrote:
> 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
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
Posted by: crystal 8 <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar