Get Top1 or Max of subquery 2

waubain · Jan 3, 2019

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

Code:

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

Code:

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

Code:

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.

SQL:

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 · Jan 3, 2019

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 · Jan 3, 2019

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 · Jan 3, 2019

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 · Jan 3, 2019

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 · Jan 3, 2019

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 · Jan 3, 2019

This SQL worked. Thanks for the ideas.

SQL:

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 · Jan 3, 2019

Try this SQL:

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 · Jan 3, 2019

I think our postings crossed in compositionland.

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

waubain · Jan 4, 2019

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Get Top1 or Max of subquery 2

waubain

Technical User

Andrzejek

Programmer

waubain

Technical User

Andrzejek

Programmer

waubain

Technical User

Andrzejek

Programmer

waubain

Technical User

dhookom

Programmer

dhookom

Programmer

waubain

Technical User

Similar threads

Part and Inventory Search

Sponsor