troubleshooting Question

Select oldest date from Access table

Avatar of bdhtechnology
bdhtechnology asked on
Microsoft AccessSQL
10 Comments2 Solutions536 ViewsLast Modified:
I have an Access database that has a list of clients and ride schedules in it.  I want to create a query to find inactive clients based on if there last ride was more than 2 years ago.  The table tbl_entities_schedules has a field schedule_date that has the date of the ride, and a field client_id that references the ID of the client in the table tbl_entities_people.  In the table_entites_people the ID/AutoNumber field is ep_id.  The original query I had was the following:

SELECT tbl_entities.entity_id, tbl_entities_people.ep_last_name & ", " & tbl_entities_people.ep_first_name AS display, tbl_entities.disabled, tbl_entities_people.disabled, tbl_entities.ec_id, [user_lastname] & ", " & [user_firstname] AS [user], tbl_entities_people.control_number, tbl_entities_people.route_id, tbl_entities_routes.route_name, tbl_entities_people.ep_id, [address_1] & ", " & [address_2] AS address_plate, qry_entity_person_info.city, qry_entity_person_info.state, qry_entity_person_info.zipcode, tbl_mobility.mobility_name, qry_entity_person_info.et_phone1, qry_entity_person_info.et_phone3, IIf([paratransit_approved]=True,"YES","NO") AS PT
FROM ((((tbl_entities 
  INNER JOIN tbl_entities_people ON tbl_entities.entity_id = tbl_entities_people.entity_id) 
  INNER JOIN tbl_Users ON tbl_entities_people.created_by = tbl_Users.user_id) 
  INNER JOIN tbl_entities_routes ON tbl_entities_people.route_id = tbl_entities_routes.route_id) 
  LEFT JOIN qry_entity_person_info ON tbl_entities.entity_id = qry_entity_person_info.tbl_entities.entity_id) 
  LEFT JOIN tbl_mobility ON tbl_entities_people.mobility_id = tbl_mobility.mobility_id
WHERE (((tbl_entities.disabled)<>True) AND ((tbl_entities_people.disabled)<>True) AND ((tbl_entities.ec_id)=9))
ORDER BY tbl_entities_people.ep_last_name & ", " & tbl_entities_people.ep_first_name;

I want the selection fields to be the same, with the exception of adding the last ride date determined.  I have done some reasearch and it appears I need to use the DateDiff function to get the number of days,  DateDiff("d", [schedule_date], Date())>=730.  My problem is that I am not sure how to get only the last ride date.

I can post the full table definitions if that is helpful as well.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros