Query or report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Need Help - 28 Apr 2008 13:55 GMT

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

Reply to this Message

John Spencer - 28 Apr 2008 15:25 GMT

I might try something like the following as the basis for a query that you
could use in a report.

SELECT A.HoundID
, A.TimeScore
, A.Score
Min(B.TimeScore) as PriorTime
FROM TheTable as A LEFT JOIN TheTableAs B
ON A.HoundID = B.HoundID
and A.TimeScore < B.TimeScore
WHERE A.TimeScore between #06:00:00# and #11:00:00#
GROUP BY A.HoundID, A.TimeScore
HAVING DateDiff("n",NZ(Min(B.TimeScore),#00:01:00#),A.TimeScore) > 9

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> I have a table setup with the following structure
> HoundID pk
[quoted text clipped - 28 lines]
> 10:15 20
> Total 130

Reply to this Message

Michel Walsh - 28 Apr 2008 16:13 GMT

Unfortunatley, the 09:03 will be a cause to reject the 09:10 record, while
it seems we have to keep the 09:10 record. Nothing in the query 'knows' that
09:03 has been itself rejected and thus, should NOT be considered in any 10
minutes span.

I think the easiest approach would be to LOOP over a recordset ordering the
records by hound and by date_time stamp:

==pseudo code==

actual_hound = rst.Fields("HoundID")
nextValidDateTimeStamp = #00:09:59# +
rst.Fields("DateTimeStamp")

--- do something with the actual record ---

do
rst.moveNext
while ((NOT rst.EOF)
AND actual_hound=rst.Fieds("HoundID")
AND nextValidDateTimeStamp < rst.Fields("DateTimeStamp")

Vanderghast, Access MVP

>I might try something like the following as the basis for a query that you
>could use in a report.
[quoted text clipped - 46 lines]
>> 10:15 20
>> Total 130

Reply to this Message

John Spencer - 28 Apr 2008 16:41 GMT

Good catch. I'm convinced there should be a way to do this using SQL, but at
present, I don't see the solution.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Unfortunatley, the 09:03 will be a cause to reject the 09:10 record, while
> it seems we have to keep the 09:10 record. Nothing in the query 'knows' that
[quoted text clipped - 19 lines]
>
> Vanderghast, Access MVP

Reply to this Message