bdhtechnology
asked on
Select oldest date from Access table
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:
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This looks weird:
INNER JOIN qry_clients_inactive1 AS Max_Date ON tbl_entities_schedules.cli
Should probably read:
INNER JOIN qry_clients_inactive1 ON qry_clients_inactive.clien
But why not use the GUI designer and just pull in the query and create the join? Often much faster.
/gustav
INNER JOIN qry_clients_inactive1 AS Max_Date ON tbl_entities_schedules.cli
Should probably read:
INNER JOIN qry_clients_inactive1 ON qry_clients_inactive.clien
But why not use the GUI designer and just pull in the query and create the join? Often much faster.
/gustav
INNER JOIN qry_clients_inactive1 AS Max_Date
ON Max_Date.client_id = tbl_entities_people.ep_id
If you are supplying an alias, use that alias in the join too
----
it isn't necessary to work with datediff (forcing a calculation on every row)
SELECT client_id, MAX(schedule_date)
FROM tbl_entities_schedules
GROUP BY client_id
HAVING MAX([schedule_date] <= DateAdd ( "d", -730, Date() )
will provide the same result but with a single calculation compared to the stored values
ON Max_Date.client_id = tbl_entities_people.ep_id
If you are supplying an alias, use that alias in the join too
----
it isn't necessary to work with datediff (forcing a calculation on every row)
SELECT client_id, MAX(schedule_date)
FROM tbl_entities_schedules
GROUP BY client_id
HAVING MAX([schedule_date] <= DateAdd ( "d", -730, Date() )
will provide the same result but with a single calculation compared to the stored values
I might suggest using 'yyyy' as the interval and -2 as the addition to cover the leap year anomaly. No points please!
ASKER
@Gustav Brock:
I have the AS in there because I want to use the computed max date to display in the form. I use the SQL view because I don't work in Access often and SQL is more familiar to me :)
@PortletPaul:
I made those changes to qry_clients_inactive1 as you suggested, that makes perfect sense to me. The changes to the main query still do not work. It seems to be complaining about the LEFT JOIN ahead of the INNER JOIN added. Are order of operations at play here?
@awking00:
That makes sense as well, however I do not need to be precise, so about 2 years is good enough for me.
I have the AS in there because I want to use the computed max date to display in the form. I use the SQL view because I don't work in Access often and SQL is more familiar to me :)
@PortletPaul:
I made those changes to qry_clients_inactive1 as you suggested, that makes perfect sense to me. The changes to the main query still do not work. It seems to be complaining about the LEFT JOIN ahead of the INNER JOIN added. Are order of operations at play here?
@awking00:
That makes sense as well, however I do not need to be precise, so about 2 years is good enough for me.
I guess it should read .ep_id, not .ep_i
/gustav
/gustav
Perhaps Access is gettingpernickety about the parentheses in the FROM clause. Instead of using an alias let's try just referencing the qry name in the join
INNER JOIN qry_clients_inactive1
ON qry_clients_inactive1.clie
and adding yet one more layer of silly parentheses (line 20 & 30)
INNER JOIN qry_clients_inactive1
ON qry_clients_inactive1.clie
and adding yet one more layer of silly parentheses (line 20 & 30)
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)
INNER JOIN qry_clients_inactive1
ON qry_clients_inactive1.client_id = tbl_entities_people.ep_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;ASKER
It was either the order of the joins or the parenthasis, either way it's working now:
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, Max_Date.Last_Ride
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)
[b]INNER JOIN qry_clients_inactive1 AS Max_Date ON Max_Date.client_id = tbl_entities_people.ep_id[/b])
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;
ASKER
Open in new window
That query works great and shows the date as well as the client_id, which is exactly what I need. Also FYI I am using the number of days because they may want to adjust this to 2.5 years or some other strange value.
Then I added it as an inner join to the query:
Open in new window
However that gives me the following error:
Syntax error (missing operator) in query expression 'tbl_entities_people.mobil
I double checked the syntax and it looks right to me. Any suggestions?