Trying to create a query that returns only the newest records based on one date field

[pbDudley]

Hello

I have a query in access that can have multiple records for one unique id. Each time the record is modified, it saves that record. I do have a field called: LAST_MODIFIED_DATE with values similar to what is below

LAST_MODIFIED_DATE

3/7/2024 11:30

3/7/2024 11:27

3/7/2024 13:36

3/13/2024 9:01

If I try doing a count on this query and use MAX for the LAST_MODIFIED_DATE, I thought it would just return the records with 3/13/2024 9:01, but it is not, I still get all of the records.
Is there other criteria that I need to use to return the newest record? I did add IS NOT NULL to not return the blank field as shown but the COUNT and selecting MAX does not return the one record for some reason

thanks

[Micron]

I think you want to use DMax, not Max.

EDIT - or a sub query. Seeing some data would probably be a big help.

[pbDudley]

Ok, I see the DMax as a criteria option but I dont think Im writing it correctly, or perhaps it cant be used in a query design view

But I have it as such

MOST_UPDATED: DMax("LAST_MODIFIED_DATE", "TABLE_NAME")

Where LAST_MODIFIED_DATE is the field, and then the table name, but I get syntax errors

Im sorry I cant put the database, I assume I could do fake tables but I can try writing this correctly first

And I tried it with [] instead of (), it recognizes the field and table names when i use the build tool in query design, but it doesnt like it

[Welshgasman]

You can create a new db and import just what is needed?
I doubt very much a date and time field is going to be confidential?

Or you can obfuscate the data with https://www.access-programmers.co.uk...ed-dbs.306745/

[June7]

Your table is named "TABLE_NAME"? Domain aggregates will certainly work in a query and can build expression in design view.

If you want to return records that have maximum date, need to exclude time parts.

SELECT * FROM TABLE_NAME WHERE Int(LAST_MODIFIED_DATE) = DMax("Int(LAST_MODIFIED_DATE)", "TABLE_NAME");

or

SELECT * FROM TABLE_NAME WHERE Int(LAST_MODIFIED_DATE) = (SELECT Max(Int(LAST_MODIFIED_DATE)) FROM TABLE_NAME);

The latter may perform faster.

Advise not to use all upper case in naming convention - it is harder to read.

[pbDudley]

Alright, I'm not even sure if this is enough of an example with the data but Ill try.

Everything that I have is tied to the plan number, a form

A lot of the values are null in the time table, but some aren't. that's just the way it is.

I will probably have to create my own table so I should be able to edit the date/time in the table properties, but I didn't see that option to remove the time, fi you are saying that the timestamp wont allow this to work, so I can convert it to just the date, but some have the same date and newer time so that might not be an option either.

If this isn't enough info, then I will attempt a few more ways of doing this, and then my last resort will be a combo box showing the fields I need, with the time stamp and users can pick the newest dated one, but I was trying to avoid that

I tried one of those lines of code that u gave but it gave an error that it was expecting a number, not a date, or something like that

thank you. And again, if this isn't enough, it's hard for me to create a bunch of fake data, but this might work

[mike60smart]

Here is an example of query

[pbDudley]

Oh, I messed up., its tied to other tables that make the time field technically not unique, I forgot I tested it with just the columns that were unique. I thought i would need to somehow add that max or Dmax then use the PLAN ID to narrow it down to just those records, but I have other tables connected, so just using the max like that doesnt work for me


But if I add all of the other fields in that tbl_Time_Stamp_Example I still don't get just the one record per plan using the max

But Im also wrong again, there are multiple records for some of these

N-3456
This one has 4 timestamps but im trying tro get it to show just the newest.
Im going to have more than one record per plan, I knew that, it was more of narrowing this part down
And I could still do the combo box, that would show the fields I need with the time stamp then we would chose the newest one

[mike60smart]

If you run the query I suggested then it produces the following result.

If you also add Is Not Null to the criteria for Max date then the results are as shown.

[June7]

You tried SQL statement I suggested? I tested against your sample data and it returns 1 record. I suggested removing time parts because I thought all plans would have a value in LAST_MODIFIED_DATE and multiple plans could have the same date but time parts would always be different. And that you only wanted records that matched whatever was the maximum date in table.

Returning just the max date (or Null) for each plan is simple. If you want entire record per plan, that is another matter and gets complicated. Here is one approach that returns 3 records, 1 for each plan that has data in LAST_MODIFIED_DATE:

SELECT T.* FROM tbl_Time_stamp_Example AS T INNER JOIN (SELECT Plan, Max(LAST_MODIFIED_DATE) AS MAXD FROM tbl_TIME_stamp_Example GROUP BY Plan) AS Q
ON T.Plan=Q.Plan AND T.LAST_MODIFIED_DATE=Q.MAXD;

PLAN	NAME	TYPE	LOCATION	LAST_MODIFIED_DATE
N-3456	P	H_G	19234	3/13/2024 9:01
N-7777	PO	H_G	13000	3/8/2016 14:59
N-3830	14_P	H_G	15000	10/27/2016 9:49

Do you want to return a record for each plan even if does not have data in LAST_MODIFIED_DATE? Which one? For example N-1234 has two records without date values. I expect will need unique identifier (autonumber or combination of fields) and correlated subquery to accomplish this.