Select Records by Most Recent Date

[Grimjack]

Hello,


I am trying to get my query to select only those records with the most current date. The date spans 2 tables and they are linked. Below is an image displaying how my database is constructed and how I built my query.

Each Doc can have multiple Planned_Sub_Dates (PSD) and each PSD can have Rev, Sub, Return and Code data associated with it but not always. I have used both the 'Max' and 'Last' criteria in the query. It still displays multiple instances of documents and it will not return any doc which only has a PSD (with no ancillary info). I am not a skilled user of Access, I know enough to get myself into trouble. Any help/ideas would be appreciated.

Grimjack

[Bob Fitz]

Can you post a copy of the db

[Welshgasman]

The far right join looks incorrect?

[Micron]

You will get multiple records with repeating data because of grouping. Taking the first of the last 4 fields shown as an example, for every revision date you will get a record with the Doc_ID. It could be that a Totals query is not the way to go here - maybe a sub query is needed. See if this meter example looks like what you are dealing with.

http://allenbrowne.com/subquery-01.html

[Grimjack]

Willow WCF - 2024-04-17.zip
Here is my db. I would be interested in your thoghts.

[Grimjack]

You are correct. I fixed that join but it still displays too many records.

[June7]

Look at Allen Browne TOP N example http://allenbrowne.com/subquery-01.html#TopN

SELECT Planned_Sub_tbl.ID, Document_ID_tbl.Doc_ID, Document_ID_tbl.Title, Document_ID_tbl.[Tag_Number(s)], Document_ID_tbl.[Add_VDR_Code(s)], Planned_Sub_tbl.Planned_Sub_Date, Rev_Return_tbl.Submittal_Date
FROM Rev_Return_tbl RIGHT JOIN (Document_ID_tbl INNER JOIN Planned_Sub_tbl ON Document_ID_tbl.Doc_ID = Planned_Sub_tbl.Doc_ID) ON Rev_Return_tbl.Planned_Sub_Date = Planned_Sub_tbl.ID
WHERE (((Planned_Sub_tbl.[ID]) In (SELECT TOP 1 ID FROM Planned_Sub_tbl AS Dupe WHERE Dupe.Doc_ID=Planned_Sub_tbl.Doc_ID ORDER BY Dupe.Planned_Sub_Date DESC)));



Advise not to use exact same field name in multiple tables. Planned_Sub_Date in Rev_Return_tbl would be better as Planned_Sub_fk.

[madpiet]

GrimJack,
Could you please explain *which* record the code or query should return for a given record or records? (Pick one from the database or whatever.) "Most recent" I get, but based on which date field(s)?

Thanks!
Pieter

[Micron]

I would be interested in your thoghts.

Should not have delimited values in the same field unless you have a valid reason for doing so.
You are using real data as the primary key (PK) which is usually not a good idea.

Does this give you what you need?

SELECT Document_ID_tbl.Doc_ID, Document_ID_tbl.Title, Max(Planned_Sub_tbl.Planned_Sub_Date) AS MaxOfPlanned_Sub_Date
FROM (Document_ID_tbl INNER JOIN Planned_Sub_tbl ON Document_ID_tbl.Doc_ID = Planned_Sub_tbl.Doc_ID) 
INNER JOIN Rev_Return_tbl ON Planned_Sub_tbl.ID = Rev_Return_tbl.Planned_Sub_Date
GROUP BY Document_ID_tbl.Doc_ID, Document_ID_tbl.Title;

[Grimjack]

I want the query to return the Doc_ID & Title for only the most recent Planned_Sub_Date, as well as any associated Rev_Return data for that Planned_Sub_Date (if any). Right now, the query returns every Planned Sub Date, except those which do not have any corresponding data in the Rev_Return table. Does this make sense?

Larry

[madpiet]

Final Query:
SELECT Document_ID_tbl.Doc_ID, Document_ID_tbl.Title, Document_ID_tbl.[Tag_Number(s)], Document_ID_tbl.[Add_VDR_Code(s)], MostRecentSubDate.LatestPlanned_Sub_DateFROM Document_ID_tbl INNER JOIN (Planned_Sub_tbl INNER JOIN MostRecentSubDate ON (Planned_Sub_tbl.Planned_Sub_Date = MostRecentSubDate.LatestPlanned_Sub_Date) AND (Planned_Sub_tbl.Doc_ID = MostRecentSubDate.Doc_ID)) ON Document_ID_tbl.Doc_ID = Planned_Sub_tbl.Doc_ID;


MostRecentSubDate:
SELECT Planned_Sub_tbl.Doc_ID, Max(Planned_Sub_tbl.Planned_Sub_Date) AS LatestPlanned_Sub_Date
FROM Planned_Sub_tbl
GROUP BY Planned_Sub_tbl.Doc_ID;

Is this what it's supposed to return? (This is the first few records)

Doc_ID	Title	Tag_Number(s)	Add_VDR_Code(s)	LatestPlanned_Sub_Date
24425402-132.01-A01-0001-00	Schedule	AHU-6900100, AHU-6900200, AHU-6900600, AHU-6900700, AHU-6901100, AHU-6901200, AHU-6901600, AHU-6901700, AHU-6902100, AHU-6902200, AHU-6902600, AHU-6902700, AHU-6903100, AHU-6903200, AHU-6903600, AHU-6903700, AHU-6904100, AHU-6904200, AHU-6904600, AHU-6904700, AHU-6905100, AHU-6905200, AHU-6905600, AHU-6905700, AHU-6910100, AHU-6910200, AHU-6910600, AHU-6910700, AHU-6920100, AHU-6920200, AHU-6920600, AHU-6920700, AHU-6930100, AHU-6930200, AHU-6930600, AHU-6930700, AHU-6906100, AHU-6906200, AHU-6907100, AHU-6907200		4/1/2024
24425402-132.01-A04-0001-00	Major Sub-Vendor List & Component Description	AHU-6900100, AHU-6900200, AHU-6900600, AHU-6900700, AHU-6901100, AHU-6901200, AHU-6901600, AHU-6901700, AHU-6902100, AHU-6902200, AHU-6902600, AHU-6902700, AHU-6903100, AHU-6903200, AHU-6903600, AHU-6903700, AHU-6904100, AHU-6904200, AHU-6904600, AHU-6904700, AHU-6905100, AHU-6905200, AHU-6905600, AHU-6905700, AHU-6910100, AHU-6910200, AHU-6910600, AHU-6910700, AHU-6920100, AHU-6920200, AHU-6920600, AHU-6920700, AHU-6930100, AHU-6930200, AHU-6930600, AHU-6930700, AHU-6906100, AHU-6906200, AHU-6907100, AHU-6907200		4/1/2024
24425402-132.01-A05-0001-00	Major Sub-Vendor Purchase Orders (Unpriced)	AHU-6900100, AHU-6900200, AHU-6900600, AHU-6900700, AHU-6901100, AHU-6901200, AHU-6901600, AHU-6901700, AHU-6902100, AHU-6902200, AHU-6902600, AHU-6902700, AHU-6903100, AHU-6903200, AHU-6903600, AHU-6903700, AHU-6904100, AHU-6904200, AHU-6904600, AHU-6904700, AHU-6905100, AHU-6905200, AHU-6905600, AHU-6905700, AHU-6910100, AHU-6910200, AHU-6910600, AHU-6910700, AHU-6920100, AHU-6920200, AHU-6920600, AHU-6920700, AHU-6930100, AHU-6930200, AHU-6930600, AHU-6930700, AHU-6906100, AHU-6906200, AHU-6907100, AHU-6907200		4/28/2024
24425402-132.01-A06-0001-00	Status/Progress Report-Engineering/ Fabrication/ Buyout Items/Weight	AHU-6900100, AHU-6900200, AHU-6900600, AHU-6900700, AHU-6901100, AHU-6901200, AHU-6901600, AHU-6901700, AHU-6902100, AHU-6902200, AHU-6902600, AHU-6902700, AHU-6903100, AHU-6903200, AHU-6903600, AHU-6903700, AHU-6904100, AHU-6904200, AHU-6904600, AHU-6904700, AHU-6905100, AHU-6905200, AHU-6905600, AHU-6905700, AHU-6910100, AHU-6910200, AHU-6910600, AHU-6910700, AHU-6920100, AHU-6920200, AHU-6920600, AHU-6920700, AHU-6930100, AHU-6930200, AHU-6930600, AHU-6930700, AHU-6906100, AHU-6906200, AHU-6907100, AHU-6907200		4/1/2024
24425402-132.01-A07-0001-00	ACG Vendor Document List (VDL)	AHU-6900100, AHU-6900200, AHU-6900600, AHU-6900700, AHU-6901100, AHU-6901200, AHU-6901600, AHU-6901700, AHU-6902100, AHU-6902200, AHU-6902600, AHU-6902700, AHU-6903100, AHU-6903200, AHU-6903600, AHU-6903700, AHU-6904100, AHU-6904200, AHU-6904600, AHU-6904700, AHU-6905100, AHU-6905200, AHU-6905600, AHU-6905700, AHU-6910100, AHU-6910200, AHU-6910600, AHU-6910700, AHU-6920100, AHU-6920200, AHU-6920600, AHU-6920700, AHU-6930100, AHU-6930200, AHU-6930600, AHU-6930700, AHU-6906100, AHU-6906200, AHU-6907100, AHU-6907200		4/30/2024

[Grimjack]

Does this give you what you need?

SELECT Document_ID_tbl.Doc_ID, Document_ID_tbl.Title, Max(Planned_Sub_tbl.Planned_Sub_Date) AS MaxOfPlanned_Sub_Date
FROM (Document_ID_tbl INNER JOIN Planned_Sub_tbl ON Document_ID_tbl.Doc_ID = Planned_Sub_tbl.Doc_ID) 
INNER JOIN Rev_Return_tbl ON Planned_Sub_tbl.ID = Rev_Return_tbl.Planned_Sub_Date
GROUP BY Document_ID_tbl.Doc_ID, Document_ID_tbl.Title;

[/QUOTE]

This code gives me this dialog box.

[Micron]

No prompt for me. Here's some of the query results. Maybe I broke something when I posted, because I entered line feeds in the sql. Reposted below.

Doc_ID	Title	MaxOfPlanned_Sub_Date
24425402-132.01-A01-0001-00	Schedule	4/01/2024
24425402-132.01-A04-0001-00	Major Sub-Vendor List & Component Description	4/01/2024
24425402-132.01-A05-0001-00	Major Sub-Vendor Purchase Orders (Unpriced)	4/28/2024
24425402-132.01-A06-0001-00	Status/Progress Report-Engineering/ Fabrication/ Buyout Items/Weight	4/01/2024
24425402-132.01-A07-0001-00	ACG Vendor Document List (VDL)	4/16/2024
24425402-132.01-C03-0002-00	General Arrangement - WCF Production Module LER System - AHU-6900600 / AHU-6900700	4/26/2024
24425402-132.01-C03-0004-00	General Arrangement - WCF Export Module LER System - AHU-6901600 / AHU-6901700	4/26/2024
24425402-132.01-C03-0006-00	General Arrangement - WCF HP Compression Module LER System - AHU-6902600 / AHU-6902700	4/26/2024
24425402-132.01-C03-0008-00	General Arrangement - WCF LP Compression Module LER System - AHU-6903600 / AHU-6903700	4/26/2024
24425402-132.01-C03-0011-00	General Arrangement - WCF Utility Module Main System - AHU-6905100 / AHU-6905200	4/12/2024
24425402-132.01-C03-0012-00	General Arrangement - WCF Utility Module LER System - AHU-6905600 / AHU-6905700	4/12/2024
24425402-132.01-C03-0014-00	General Arrangement - BT1 Drillsite Module LER System - AHU-6910600 / AHU-6910700	4/12/2024
24425402-132.01-C03-0016-00	General Arrangement - BT2 Drillsite Module LER System - AHU-6920600 / AHU-6920700	4/26/2024
24425402-132.01-C03-0018-00	General Arrangement - BT3 Drillsite Module LER System - AHU-6930600 / AHU-6930700	4/26/2024
24425402-132.01-C03-0019-00	General Arrangement - WCF Divert Tank Pumps & Blowers Module HVAC System - AHU-6906100 / AHU-6906200	4/12/2024
24425402-132.01-C03-0020-00	General Arrangement - WCF Firewater Pumphouse HVAC System - AHU-6907100 / AHU-6907200	4/12/2024
24425402-132.01-E05-0011-00	HVAC Datasheet - WCF Utility Module Main System - AHU-6905100 / AHU-6905200	4/12/2024
24425402-132.01-E05-0012-00	HVAC Datasheet - WCF Utility Module LER System - AHU-6905600 / AHU-6905700	4/12/2024
24425402-132.01-E05-0014-00	HVAC Datasheet - BT1 Drillsite Module LER System - AHU-6910600 / AHU-6910700	4/12/2024

SELECT Document_ID_tbl.Doc_ID, Document_ID_tbl.Title, Max(Planned_Sub_tbl.Planned_Sub_Date) AS MaxOfPlanned_Sub_Date
FROM (Document_ID_tbl INNER JOIN Planned_Sub_tbl ON Document_ID_tbl.Doc_ID = Planned_Sub_tbl.Doc_ID) INNER JOIN Rev_Return_tbl ON Planned_Sub_tbl.ID = Rev_Return_tbl.Planned_Sub_Date
GROUP BY Document_ID_tbl.Doc_ID, Document_ID_tbl.Title;

[Grimjack]

No prompt for me. Here's some of the query results. Maybe I broke something when I posted, because I entered line feeds in the sql. Reposted below.

SELECT Document_ID_tbl.Doc_ID, Document_ID_tbl.Title, Max(Planned_Sub_tbl.Planned_Sub_Date) AS MaxOfPlanned_Sub_Date
FROM (Document_ID_tbl INNER JOIN Planned_Sub_tbl ON Document_ID_tbl.Doc_ID = Planned_Sub_tbl.Doc_ID) INNER JOIN Rev_Return_tbl ON Planned_Sub_tbl.ID = Rev_Return_tbl.Planned_Sub_Date
GROUP BY Document_ID_tbl.Doc_ID, Document_ID_tbl.Title;

I used your entire code and still received the prompt. Weird though, I could not switch to Design View after I entered the SQL code.

[Micron]

You are trying this in the same db copy that you posted, or in your original db? If in the original, that is not what I have to play with. That might explain why.