Get Top1 or Max of subquery 2

[waubain]

I am working with a db that tracks revisions to a book. Revisions are captured in the Revisions table with a timestamp. I need to show in a form which Book Edition the revision affected (which is always the next published edition).

The 2 tables with data that look like this:

dimBookEdition

pk_BookEditionID	BookEdition	EditionPubDate
1			1st Edition	3/1/1997
2			2nd Edition	1/15/2000
3			3rd Edition	11/3/2003
4			4th Edition	1/1/2008
5			5th Edition	11/28/2012
6			6th Edition	1/29/2018
7			7th Edition	12/31/2023

tblRevisionLog

pk_RevisionLogID	DateTimeCreated
36			01/16/1996
37			02/10/1997
38			12/16/1999
39			06/26/2005
40			04/25/2009
41			12/06/2018

The desired output

pk_RevisionLogID	DateTimeCreated		BookEdition
36			01/16/1996		1st Edition
37			02/10/1997		1st Edition
38			12/16/1999		2nd Edition
39			06/26/2005		4th Edition
40			04/25/2009		5th Edition
41			12/06/2018 		7th Edition

This query will give me the list, but I cannot figure out how to get the MAX DiffPubDate for each group of revision ID's or Row 1.

SELECT pk_RevisionLogID, DateTimeCreated, Edition, DateDiff("d",[dimBookEdition].[EditionPubDate],[tblRevisionLog].[DateTimeCreated]) AS DiffPubDate
FROM dimBookEdition, tblRevisionLog
ORDER BY pk_RevisionLogID, DateDiff("d",[dimBookEdition].[EditionPubDate],[tblRevisionLog].[DateTimeCreated]) DESC

Thank you for any help.



You don't know what you don't know...

 

[Andrzejek]

How does the table [tt]tblBookEdition[/tt] relate to [tt]tblRevisionLog[/tt] table?
How do you know which record(s) from one table relate to which record(s) from another?


---- Andy

There is a great need for a sarcasm font.

 

[waubain]

Andy,

The tables are not related to each other.

Basically I am trying to find where DateTimeCreated falls between which 2 EditionPubDate dates. So in the case of tblRevisionLog.pk_RevisionLogID #38 date falls between the 1st and 2nd Edition, so the revision appeared in the 2nd Edition.

I hope this makes sense.


You don't know what you don't know...

 

[Andrzejek]

I would try something like this (not tested):
[tt]
Select pk_RevisionLogID, DateTimeCreated, [blue]
(Select Top 1 BookEdition
From tblBookEdition
Where EditionPubDate < tblRevisionLog.DateTimeCreated
Order By DateTimeCreated Desc) [/blue]as BookEdition
From tblRevisionLog
Order By 1
[/tt]
but first I would make sure the [blue]Blue[/blue] part works with the [tt]tblRevisionLog.DateTimeCreated[/tt] hard-coded with the date of [tt]12/16/1999[/tt] (following your example of records 38)


---- Andy

There is a great need for a sarcasm font.

 

[waubain]

Andy,

The Blue part does not work, even after hard coding. It does not choose a Top 1, but gives all Editions < the hard coded date. I did change the < to > as I need the next Edition after the DateTimeCreated. but the Top 1 did not give the desired result. I will keep trying, Thanks for the ideas.

 

[Andrzejek]

Try to play with Max(), maybe....:
[tt]
Select Max(BookEdition)
From tblBookEdition
Where EditionPubDate < #12/16/1999#[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[waubain]

This SQL worked. Thanks for the ideas.

SELECT tblRevisionLog.pk_RevisionLogID, tblRevisionLog.DateTimeCreated, Min(Edition) AS BookEdition
FROM dimBookEdition, tblRevisionLog
WHERE (([dimBookEdition].[EditionPubDate]>[tblRevisionLog].[DateTimeCreated]))
GROUP BY tblRevisionLog.pk_RevisionLogID, tblRevisionLog.DateTimeCreated;

 

[dhookom]

Try this SQL:

SELECT tblRevisionLog.pk_RevisionLogID, tblRevisionLog.DateTimeCreated, 
(SELECT TOP 1 BookEdition
 FROM dimBookEdition
 WHERE EditionPubDate >= DateTimeCreated ORDER BY EditionPubDate) AS BookEdition
FROM tblRevisionLog;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

I think our postings crossed in compositionland.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[waubain]

Duane,
Yours worked also. Thanks for providing an alternate solution.