[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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.
0
bdhtechnology
Asked:
bdhtechnology
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
PortletPaulCommented:
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
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now