Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Dates (97)

    My database stores customer information in the TblCust table and the date we last visited them the TblCall table.

    The boss wants a report on all the customers that have not been visited for more than one month.

    So I've put a calculated field in a query which adds 30 days to the call date and put <Date() in the criteria row to show all the overdue appointments.

    How do I prevent the calls that are over 30 days old but have been followed up appearing in the report? The report shows all calls over 30 days old irrespective of whether or not another call has been made to that customer. I could probably do this by coding a tick box boolean return value but I would prefer for it to be automatic and only report on the latest call if over 30 days old.

    I hope this makes sense! If it does then please see customer b in my experimental db attached, two calls reported, first call not required.

    TAIA

    Regards

    Graeme
    Attached Files Attached Files

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

    Re: Calculated Dates (97)

    Take the qryCustCall query, and remove the sort order.
    Make it into a totals query.
    Leave the Total option for Cust as Group By.
    Set the Total option for CallDate to Max.
    Set the Criteria for CallDate to <Date()-30

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calculated Dates (97)

    I haven't looked at your sample database, but I believe you want to do a query based on a query. The first query should be the max of the visit date to get the most recent date a customer was visited, so for each customer you should get a most one record (you might also want to know about customers that have never been visited). Then the second query, based on the first would be all of those customers who had not been visited in the last 30 days, perhaps sorted in descending length of time since they were last visited. If I've gone off the track here, post back and I'll take a good look at your sample database.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Dates (97)

    Hans

    Succinct and to the point as ever!

    Queries all done. Reports for customers not visited for 90, 180 and 360 days all done, printed and on the bosses desk!

    Thanks


    Wendell

    I did an 'is null' instead of '<Date()-nn' as the criteria and found there was only one customer who has never been visited, so I phoned the business development manager for the area to sort.

    Thanks for thinking of that!


    Regards

    Graeme

Posting Permissions

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