Most recent dates-query

[Batta2019]

Hi guys
I have the following problem, my data is organized someway like this:

SUBJECT DATE REPLY S1 2/2/2012 N S1 3/3/2013 Y S2 4/4/2014 Y S2 5/5/2015 N S3 6/6/2016 N S3 7/7/2017 Y S4 8/8/2018 Y S4 9/9/2019 N I need a query that will give me the records with the most recent date that replied Y, meaning to look like either of these two outputs below: SUBJECT DATE REPLY S1 3/3/2013 Y S3 7/7/2017 Y or SUBJECT DATE REPLY S1 2/2/2012 N S1 3/3/2013 Y S3 6/6/2016 N S3 7/7/2017 Y Thanks a lot for all your help! Sincerely, Batta

[JoeM]

Do an Aggregate (Totals) Query, grouping on both the Subject and Reply fields, and taking the Maximum Date field.
To do this, add all 3 fields to your query, click on the Totals Icon (looks like a Sigma). This will add a Totals Rows with the phrase "Group By" under each of those three fields.
Then, change the "Group By" phrase under the Date field to "Max".

If you just want to see the "Y" records, then add "Y" to the Criteria line of the Reply field.

[Batta2019]

Thanks a lot JoeM, I've tried this before to my data set, but for some reason, Max grouping does no work.
So, this is before:

id	dbo_form_q_lost	MaxOfMaxOfdtcontact
45608	N	2003-10-26
45608	Y	2003-03-06
45700	N	2011-10-04
45700	Y	2006-04-28
45995	N	2004-09-15
45995	Y	2004-09-15

and this is after running the query

cid	dbo_form_q_lost	MaxOfMaxOfdtcontact
45608	Y	2003-03-06
45700	Y	2006-04-28
45995	Y	2004-09-15

(as you can see,

[Batta2019]

my reply got cut, sorry...
as you can see, 45608 us still there although N reply has more recent day
If you have any other idea, I am listening .

Thanks a lot for your time.
Batta

[Micron]

as you can see, 45608 us still there although N reply has more recent day

You are getting exactly what you asked for: max date where field is Y.

the most recent date that replied Y,

If you want max date only, remove criteria from y/n field.

[Batta2019]

Micron, I am sorry, What I need is the following: an output where the most recent date will have Y. In case the most recent date has N, and later date is Y, I don't want to see that record in the output. As you can see regarding the 45608, the most recent date has reply N but I still see the record. I hope this is clarified now. So, again the only records I want to see are those that have Y as a most recent date. Thanks again.

[JoeM]

OK, that is very different from how I first read your question.

One way to do this is to do it in a series of two queries.
Query 1: Add ID and Date field, make an Aggregate Query, grouping on ID and taking Max of Date
Query 2: Join Query1 to Table on box the ID and Date fields, show all three fields from Table, and put criteria on Reply field, only showing records where Reply is "Y"

That should do what you want.

If you are proficient at writing SQL code, you could do all this in a single query (using a Subquery), but there is nothing wrong with using two queries to do it.

[Batta2019]

Many thanks JoeM, I'll try this one.
I hope it'll works :-).

All the best

[Micron]

So if the most recent date for an id is N don't show the ID at all?
If so, and the 1st table in post 3 is your data, the query will return 0 records because for every id the max date has N for cbo field...

I think you will need a sub query because suggestion in post 2 will not provide what you seem to be asking for. Regardless, based on the supplied data and requirement I don't see how you'll get any records anyway.

[Batta2019]

Micron, you are right about this "If so, and the 1st table in post 3 is your data, the query will return 0 records because for every id the max date has N for cbo field"
but that's only part of my data, i just use it to show that originally suggested way by JoeM is not working.
His second suggestion should works, although I need to create a table, join it to mu data table an create another query, but I think it should work properly, or as both of you guys said, to write SQL using subquery.

Thanks a lot for joining the discussion.
Cheers!

[Micron]

Micron, you are right about this "If so, and the 1st table in post 3 is your data, the query will return 0 records because for every id the max date has N for cbo field"

Maybe I'm not. Access will give you a record for an ID where 2 dates are the same and you use the method suggested in post 7. I was pretty much guessing with the subquery method in mind. FYI - you can often use queries as tables rather than creating a table for part of the process.
Good luck!

[Batta2019]

Ah, sorry for my inexperience with Access: Can you please tell me how to use queries as tables without creating them. Noone ever show me that before... or please just post a link if it exist somewhere.

Again, thanks a lot for all the effort.

[JoeM]

His second suggestion should works, although I need to create a table, join it to mu data table an create another query, but I think it should work properly, or as both of you guys said, to write SQL using subquery.

There shouldn't be any need to create a new table using my second suggestion. You can join the first query to your original table (queries don't necessarily have to be created on Tables, they can be created on other Queries too!).

When you are building your Query, if your click on "Show Table" to add other objects, you will see that there is a Table AND a Query tab. Just click on the Query tab and add the query you just built.

[Batta2019]

MANY THANKS TO BOTH OF YOU GUYS! You are GREAT!

Cheers!

[Micron]

Can you please tell me how to use queries as tables without creating them

While technically true, that's not what I meant/said.
You said you'd have to create another table. I'm saying you can use your query instead. Joe said it better, but then I bet he's not struggling at the keyboard lately.