Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Red face Unanswered: Finding last service date

    I'm having trouble creating a report based on the service history of customers in a service table.

    There are 2 tables involved; Customers and xServices with CustomerID in each table being the link.

    The xServices table has all service records for all customers (ie each customer will appear a number of times depending on their service history.)

    I want to create a report of all customers that have not had a service in the the last 12 months based on their last service date. I only want the last service date for matching records to be displayed.

    So I am working with xServices table and LastService (Date/Time) field.

    I know that I need a query to do this but I haven't been able to get any to work!

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Click the Totals button, and select Max under the date/time field when creating the query.

  3. #3
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Thanks for that however it doesn't give what I want. I need to have the query show ONLY those customers that have not had a service in the last 12 months.

    I am only new to this so my knowledge is limited.

  4. #4
    Join Date
    May 2010
    Posts
    601
    The last service could also be called the most recent service.

    See:
    Get Information Associated with the Most Recent Entry From a Table

    You could use DateDiff() by months using the most recent service data and today's date ( use Date() ). You want the records where the DateDiff) in months is > 12
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Here is an example I did that seems to do what you want.
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Thanks for all the replies.

    The ZIP file seems to be corrupt. Can you re-post it please.

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by sheusz View Post
    Thanks for all the replies.

    The ZIP file seems to be corrupt. Can you re-post it please.
    I was able to download teh ZIp and view the .accdb.

    What version of Access are you using (2007 or 2010)?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Nope, still can't get it. May be my AntiVirus software at work.

    I should have stated from the start that I am using Access 2003!

  9. #9
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by sheusz View Post
    Nope, still can't get it. May be my AntiVirus software at work.

    I should have stated from the start that I am using Access 2003!
    Ah that is why. I am at work and we have Access 2007 which uses .accdb. I have 2003 at home, I'll duplicate this tonight then post it up as an .mdb.

  10. #10
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by chris07tibgs View Post
    Ah that is why. I am at work and we have Access 2007 which uses .accdb. I have 2003 at home, I'll duplicate this tonight then post it up as an .mdb.
    Actually 2007 will also use the .mdb format. You can even set the default format for new databases created to be the older JET/.mdb format not the new ACE/ .accdb format.

    With 2007 you can convert the .accdb database to the 2000 format.
    Last edited by HiTechCoach; 12-03-10 at 11:52.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    Oct 2009
    Posts
    204
    Hmm this is strange. The file is corrupt for me at work too. I don't know why, maybe the network firewalls keep us from downloading zip files at work? Maybe I'll try an .mdb at work.

  12. #12
    Join Date
    Oct 2009
    Posts
    204
    Ok I made another one. Here is the database in .mdb format. Hope you are able to download a non-corrupted file...
    Attached Files Attached Files

  13. #13
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Chris07

    Thanks for all your trouble. The new file is downloading as corrupt at work as well. I'l try it at home tonight and advise. I have access 2010 on a laptop at home but work won't for out to upgrade from 2003. Too many licences required for too little return apparently.

    Once again many thanks for your patience and efforts so far.

  14. #14
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Tried to downlaod the zip file. Still no good, it comes up as corrupt.

  15. #15
    Join Date
    Oct 2009
    Posts
    204
    ...yeah, idk. Comes up corrupt when I try it at work also, but it works for me from home. I emailed it to you, hope that works

Posting Permissions

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