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:

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;

Open in new window


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.
LVL 1
bdhtechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
tbl_entities_schedules has a field schedule_date, It probably also has a field for entity_id
so you need a subquery something like this:

select
  entity_id
, max(schedule_date)
from tbl_entities_schedules
group by
  entity_id
having
 max(schedule_date) <= {2 years ago}

and you include it into the larger query - as an INNER JOIN

You can place whatever you like into the "{2 years ago}", it could be a date literal or a calculation or a form variable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
I would first filter the records on schedule_date older than exactly two years, then find the newest of these dates for each client:

Select
    client_id,
    Max([schedule_date])
From
    tbl_entities_schedules
Where
    DateDiff("d", DateAdd("yyyy", 2, [schedule_date]), Date()) > 0
Group By
    client_id;

Now, inner join this query on table_entites_people.ep_id in a copy of your existing query.

/gustav
0
bdhtechnologyAuthor Commented:
OK so I created the following query and saved it as qry_clients_inactive1:
SELECT client_id, MAX(schedule_date)
FROM tbl_entities_schedules
GROUP BY client_id
HAVING DateDiff("d", MAX([schedule_date]), Date())>=730

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:
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 AS Max_Date ON tbl_entities_schedules.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;

Open in new window


However that gives me the following error:
Syntax error (missing operator) in query expression 'tbl_entities_people.mobility_id = tbl_mobility.mobility_id INNER JOIN qry_clients_inactive1 AS Max_Date ON tbl_entities_schedules.client_id = tbl_entities_people.ep_i'.

I double checked the syntax and it looks right to me.  Any suggestions?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
This looks weird:

INNER JOIN qry_clients_inactive1 AS Max_Date ON tbl_entities_schedules.client_id = tbl_entities_people.ep_id  

Should probably read:

INNER JOIN qry_clients_inactive1 ON qry_clients_inactive.client_id = tbl_entities_people.ep_id

But why not use the GUI designer and just pull in the query and create the join? Often much faster.

/gustav
0
PortletPaulfreelancerCommented:
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
0
awking00Commented:
I might suggest using 'yyyy' as the interval and -2 as the addition to cover the leap year anomaly. No points please!
0
bdhtechnologyAuthor Commented:
@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?
error.png
@awking00:
That makes sense as well, however I do not need to be precise, so about 2 years is good enough for me.
0
Gustav BrockCIOCommented:
I guess it should read .ep_id, not .ep_i

/gustav
0
PortletPaulfreelancerCommented:
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.client_id = tbl_entities_people.ep_id

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;

Open in new window

If this doesn't work I think I'm out of ideas.
0
bdhtechnologyAuthor Commented:
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;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.