RE: Counting based on a conditional expression

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Fons Ponsioen (FonsPonsioen_at_discussions.microsoft.com)
Date: 01/27/05 

Date: Thu, 27 Jan 2005 09:43:10 -0800

Tina,
I would try:
=Sum(ABS([Adjusted bill time]<[Productivity Expectation])
Each time the adjusted BillTime is less than Productivity Expectation this
would result in
=Sum(ABS(-1) and if not =Sum(ABS(0)
The ABS function than changes the -1 to a 1
Than Sum() wil summarize the data for whatever group you set in the query or
report.
Than group your query or report by [Week Of]
Hope this helps.
Fons

"Tina" wrote:

> Fons, I tried doing this...
>
> =Sum(Abs([Week of]="[Adjusted bill time]<[Productivity Expectation]"))
>
> I received a "data type mismatch" error?????
> Also, should it be count instead of sum?
>
> "Fons Ponsioen" wrote:
>
> > Hi Tina.
> > What you could try is:
> > =Sum (Abs([YourField]="TheConditionYouWantToCount"))
> > Hope this helps.
> > Fons
> >
> > "Tina" wrote:
> >
> > > I am trying to do a count based on a conditional expression in my report, if
> > > that's possible.
> > >
> > > I want to do a count on my "Week of" field if ([Adjusted bill
> > > time]<[Productivity Expectation]). I'm getting "overflow" and all sorts of
> > > other errors.
> > >
> > > Can this be done? Thanks for any help.

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Tips on domain aggregate replacements
    ... My initial thinking would be to use a query that SELECTS the records to be totaled, loops through them getting the SUMS, and then store the data in a temporary table that is then used for the final report. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months and present these as fields on each line of the report. ... Month1Total -> DSum (blah, blah, blah where date is within next month from today ...
    (microsoft.public.access.modulesdaovba)
  • Re: Report is making too many numbers
    ... My report is grouped by Vendor, then Project, ... source query before putting it in the report so every record shows). ... A Running Sum text box accumlates its total. ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... My report is based on a query. ... I put the running sum text box in there. ... My report is grouped by Vendor, then Project, ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)
  • Re: How to calculate column total
    ... should be in the query unless you are grouping on another field. ... You can sum a field on a form or report by creating a textbox control on the ...
    (microsoft.public.access.gettingstarted)