Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bring Forward System (2003)

    I need to build a query into our database that will return all of the files that have had no activity in the past two weeks and are not closed. This is complicated because it is possible for a file to be open by multiple counsellors at the same time or to be open by a few and closed by others. I need to be able to create a printable report that will list the files that are open and have had no activity for two weeks. I want to be able to have these displayed by Counsellor.

    Not really sure where to start on this one. I have looked at top value queries but im not sure how to program in my limitations.

    Any guidance is appreciated.

    I have included a very stripped down version of my database (hopefully not stripped down to far)

    K
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bring Forward System (2003)

    Could you put some dummy data in the tables? Without records it is impossible to test anything.

    Also: what are the relevant fields for your question?

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> PS: the Activity and Close File tables have Cousellor instead of Counsellor

  3. #3
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bring Forward System (2003)

    I have added in some data.

    I was thinking more about this and I realized it is possible to have a file closed and then later re-open which I believes complicates this. There right now is no indication of wether a file is opened or closed other than if there is activity data entered for it. The file close information is to satisfy a statistical reporting requirment whereby any file that has had no activity for two weeks is closed or if an immediate issue is resolved, etc.

    Thanks
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bring Forward System (2003)

    In the attached version, I have created two queries:
    qryLastSession is a totals query based on the Activity table that returns the most recent session for each client (if the client has any sessions). Note: the session date field is a text field! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    qryNoActivity is based on this query and on the Client etc. table; it returns the clients that either have no activities or the last activity was more than 2 weeks ago.
    I don't know what defines a file being closed, so I couldn't take that into account.
    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
  •