0

I have 2 tables in Access:

Table 1 has id, first_name, and last_name
Table 2 has city, date_recorded, and person_id

I'm trying run a query to display the first name, last name, and city for the earliest date.

I'm able to get to this with the following query, but all records are displayed when I try to add City to results.
How can I add City to the results, but still only have 1 row per person?

SELECT table1.first_name, table1.last_name, Min(table2.date_recorded) AS First_Occurrence
FROM table1 INNER JOIN table2 ON table1.id = table2.person_id
GROUP BY table1.first_name, table1.last_name
ORDER BY Min(table2.date_recorded);
duran2
  • 3
  • 2
  • 1
    Does this answer your question? [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) and https://stackoverflow.com/questions/67429285/select-records-with-nearest-expiration-date?noredirect=1#comment119360655_67429285 – June7 May 14 '21 at 18:02

1 Answers1

1

You can use a subquery to select the city for a person with minimum date_recorded as below.

  select id, first_name,last_name, First_Occurrence , (select city from table2 where person_id=t.id and date_recorded=first_occurrence ) as city 
from
(
    SELECT t1.id, t1.first_name, t1.last_name, min(t2.date_recorded) AS First_Occurrence
    FROM table1 as t1 INNER JOIN table2 as t2 ON t1.id = t2.person_id
    GROUP BY  t1.id,t1.first_name, t1.last_name
    order by min(t2.date_recorded) 
) t
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26