average duration to first appointment

[sametch]

I am designing a relational database for keeping appointment details. I have a parent

table with person details and a child page with appointment details. A simplistic

design of the two tables is shown below:

tblPersonDetails

PersonID:
FirstName:
LastName:
InitialContactDate:

tblAppointments

Date:
WithWho:
Outcome:
PersonID: (foriegn field)

I need to be able to calculate the average time taken from InitialContactDate to the

first appointment that occurs.

So for example if I have the following appointments:

person 1 24th may 2003
person 1 28th may 2003
person 2 25th may 2003
person 2 30th may 2003

If the InitialContactDate for person 1 was 23rd May 2003 and Person 2 was 22nd May

2003. The result should be average time to contact is 2 days (the average of 24th -

23rd and 25th - 22nd).

I need to be able to write a query that would give this result ie ignore all

appointments for a given person unless it is the first appointment (the lowest date)

and take the difference from the InitialContactDate for each person.

Can anyone advise a good way to acheive this.

 

[dcx693]

I did it with two queries, but maybe some clever person will post a solution with just one.

First, I created a totals query, which I called qryFirstAppt, grouped by personID, finding the first appointment for them. Here's the SQL for it:
SELECT tblPersonDetails.PersonID, First(tblAppointments.Date) AS FirstAppt
FROM tblPersonDetails INNER JOIN tblAppointments ON tblPersonDetails.PersonID=tblAppointments.PersonID
GROUP BY tblPersonDetails.PersonID;

Then I created a 2nd query, called qryAvgTime, to find the average time between the first contact and first appointment. Here's the SQL:
SELECT Avg([InitialContactDate]-[FirstAppt]) AS AvgTime
FROM qryFirstAppt INNER JOIN tblPersonDetails ON qryFirstAppt.PersonID = tblPersonDetails.PersonID;

sametch, if you're not sure how to use the above code: Create a new query in design view, don't add any tables to the query, go to the View menu, choose SQL View, cut and paste one of the above SQL statements (starting from SELECT and going until and including the semi-colon) into the SQL view window. Save the query with the name I mentioned above. Do the same with the other SQL code. You can run them by double-clicking them. You can edit them by changing the code in the SQL view, or by switching to design view once the code is pasted into the SQL window.

 

[sametch]

dcx693

Thanks for your help. I tried it out and it does just what I am looking for. I just had to reverse the Avg([InitialContactDate]-[FirstAppt]) else it gave a negative number.

sametch

 

[dcx693]

Yeah, mine did that too. I was too lazy to switch them.

 

[sametch]

The query suggested by dcx693 works great.

Is there anyway to modify it so that it creates the average time to first appointment for each calendar month?