Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    17

    Unanswered: average time to first appointment

    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.

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I have enclosed a db that contains those two table names and a query with the example

    Note the following:
    1) relationships under - tools/relationships
    2) I changed the fieldname of "date " to "calldate" - date is a reserved word and can create problems

    HTH
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •