CountIf

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I'm attempting to generate a total based on these criterion;

Time Interval is > 10 minutes (I'm willing to add a yes/no control instead
of having Access figure time differences)

ID # is ??? (Would like to make for example 3 Totals of 3 different ID
numbers all used on the same form)

Score entered

If the time interval is > 10 minutes, I would like to add the "Scores"
individually for each ID number.

I can accomplish this in Excel, but cannot in Access. Any help would be
much appreciated.
 
If the IDnumbers you want to choose are random, then add a yes/no tick field
(Choose) to your table to allow you to choose which ones you want to include
Have a look in Help at DSum and DCount which work similarly to SumIf .


To your query's design view add a field
SumIf: IIF([Time Interval]>10 AND [Choose = True],[Score],0)
If you just want to add all the scores where the timer interval is >10 then
omit the AND [Choose] = True
Sum the SumIF field

To total the fields for each person
Put this query into another query, change it into a Totals query omitting
all the fields that make each row different except the ID field and SumIF


Access can work out time differences down to seconds

using
DateDiff("s",TimeStart,TimeEnd)
Or minutes
DateDiff("n",TimeStart,TimeEnd)

but (unless Acc2007 has sorted this) there is no format for milliseconds,
as there is in Excel which could be a drawback if you are measuring some
kind of timed event.
(note that in Access there are 2 ffs in IFF and in DateDiff)


If you want to see instant results in your form then, if it isn't a
datasheet form, embed the query into the form as a second subform.

You will need to code

Me.YourSubform.Requery

into the AfterUpdate Event of one of the fields in your form so that you
will get an instant table of results. If your form is a form with a subform
and the fields are in your subform, then
Me.Parent.YourSubform.Requery
Evi
 
Back
Top