Query or report

[Need Help]

I have a table setup with the following structure
HoundID pk
JudgeID
TimeScore
Score
I need to be able to query or build a report to be able to figure out time
intervals per HoundID and Score Total
Sample
Start time 6:00am end time 11:00am
HoundID JudgeID TimeScore Score
333 2 9:00 35 keep and count
333 4 9:03 30 keep and do not count
333 3 9:10 25 keep and count
333 2 9:50 15 keep and count
333 9 10:00 35 keep and count
333 4 10:15 20 keep and count
333 3 10:09 25 keep and do not count
323 2 10:10 35 keep and count
343 1 10:34 20 keep and count
343 10 10:45 15 keep and count
343 2 9:00 35 keep and count
Need total for each hounded
Per each HoundID all scores much be counted if over a 10 minute time
interval from each time HoundID was scored. No to scores can be within a 10
minute time interval from each other per hound ID.
For example HoundID 333
9:00 35
9:10 25
9:50 15
10:00 35
10:15 20
Total 130

 

[Matt]

Are you trying to auto generate the "Score" then get a total? If so, how
exactly do you come up with the "Score"? Is it based off of times entered
into a table?

-Matt

"The only thing worse than an employee that just quits and leaves, is an
employee that quits and doesn''t leave."

 

[Need Help]

Scores are based off of 35, 30,25,20, and 15 i enter ther score and time
directly, for and example if the hound 333 was in first place he gets 35
points for score, if he was in second place he gets 30 points for score and
so on, i have been trying to figure this out for over a year but to no luck
have been able to figure this out how to get access to do this.

Thanks
Larry

 

[Matt]

So just to be clear, what you need specifically is a query and/or form in
which you can put in times and scores, and have it automatically generate the
total scores for each houndID but only count points scored further than ten
minutes apart?

Are you entering the "Keep and Count" into your database? If that is
entered manually, you could use the data in that cell to determine which
scores are tallied. That would be much simpler than having it auto calculate
time differences then totalling scores. But it CAN be done either way.
Please let me know if I'm on the right track for what you need.

 

[Need Help]

You are correct, i enter times and scores then need it to count total for all
score over the 10 minute limit, and total them.
thanks for you help on this.

 

[Matt]

I posted a similar question under the coding section, and got this reply:

"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"