Average Time Difference

[Sussex]

I work for a hospital. The problem is we want to calculate the difference between the Sign in Time and the Appointment time. Here's the problem. Some people arrive prior to there appointment time and some arrive after. I want the average of both, but, I can't seem to get access to work with positive and negative times(based on appt time). Example I have a 9:00am appt.,I arrive at 8:40 20 minutes prior. The next person 9:00am appt, arrives at 9:10 10 minutes later. The average should be -5(meaning 5 minutes prior) for both. How do I do this? It is driving me crazy. Thank you for your help.
Sussex

 

[MichaelRed]

Two queries:

Individual differences between the arrival and the appointment
SELECT tblAvgTimeDiff.Id, tblAvgTimeDiff.Appt, tblAvgTimeDiff.ArrTime, DateDiff("n",[Appt],[ArrTime]) AS Delt
FROM tblAvgTimeDiff;


The aggregate to get the average of the differences
SELECT Avg(qryDeltTime.Delt) AS AvgOfDelt
FROM qryDeltTime;


Of course this is just a sample and you will need to do something using table and feled names from your db, as well as include criteria ...

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over