Query: Filtering Duplicate Records to show last entry

[cp3]

Hi All,
I have been having some trouble creating a query that pulls the last entry per specific ID so I don't have duplicates. For example: I have a database of check-ins whereby an individual is evaluated multiple times per year on housing status and so for each ID there are multiple rows of data since housing status is recorded at each check-in. I want to make a query that gives me the housing status at last/most current entry for each specific ID. I tried using DISTINCT but this still gives me duplicates sometimes (i.e. someone can have many check-ins for multiple kinds of housing status, and it just narrows it down to show one of each type of entry that is different per ID.....which I suppose is the purpose of DISTINCT haha).

Below is the SQL code I have written. I am mainly using the data from one table but needed to filter for individuals that have been marked STATUS = "Active" (which is coded as 1) which is stored in a different table hence the inner join. I also removed the records where housing status was blank/missing with "is not null" for housing status.


SELECT DISTINCT tbl_CheckIn2.ID, tbl_CheckIn2.[Housing Status]
FROM tbl_Interviews2 INNER JOIN tbl_CheckIn2 ON tbl_Interviews2.ID = tbl_CheckIn2.ID
WHERE (((tbl_CheckIn2.[Housing Status]) Is Not Null) AND ((tbl_Interviews2.STATUS)=1));


Any help is so much appreciated I have been struggling with this all day and for the life of me cant figure it out!

Thanks!!

[ano]

i would try to use top 1 where the sort make sure the latest is on top

[cp3]

Thak you but I dont think that answers my question. I just want to have the query report the last entered Housing Status for each individual ID.

[ano]

Thak you but I dont think that answers my question. I just want to have the query report the last entered Housing Status for each individual ID.

i guess we need the tables descriptions like is there a date max(date) is then an option
where date = (select max(date) from .. where ...)

[madpiet]

Thak you but I dont think that answers my question. I just want to have the query report the last entered Housing Status for each individual ID.

If all you want is the most recent status for each individual, that's simple. You'd just group by [Person]ID, and then get MAX([Date]) in one query.

In a second query, add the query you just created, and join it to the table you based the previous query on. Join on both PersonID and whatever the Max([Date]) column is called.

Query 1: LatestCheckInDatePerPerson
SELECT Sheet1.PersonID, Max(Sheet1.[Check-in Date]) AS [MaxOfCheck-in Date]
FROM Sheet1
GROUP BY Sheet1.PersonID;

Query2:
SELECT Sheet1.PersonID, Sheet1.[Housing Status], Sheet1.[Check-in Date]
FROM LatestCheckInDatePerPerson INNER JOIN Sheet1 ON (LatestCheckInDatePerPerson.[MaxOfCheck-in Date] = Sheet1.[Check-in Date]) AND (LatestCheckInDatePerPerson.PersonID = Sheet1.PersonID);

If that's not what you want, could you post a spreadsheet with some data?

[ano]

select r.a,r.b,r.c from r where r.c in  (
SELECT  max( s.c) as sel FROM r as s
group by s.a)
;

see table pic and result pic

[Micron]

grouping by "a" omits record with 3 r. Does that matter to OP?

[madpiet]

One way:
SELECT f1 AS PersonID, max(f3) as LastDate
GROUP BY f1

then inner join that in a new query to the original table and add the f2 column.

[ano]

just put s.b in the group.
btw then it is realy needed to create extra table with the values possible in b and r.b change from text to number to link the new table