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
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.