×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Get Top1 or Max of subquery
2

Get Top1 or Max of subquery

Get Top1 or Max of subquery

(OP)
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.

CODE --> 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...

RE: Get Top1 or Max of subquery

How does the table tblBookEdition relate to tblRevisionLog 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.

RE: Get Top1 or Max of subquery

(OP)
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...

RE: Get Top1 or Max of subquery

I would try something like this (not tested):

Select pk_RevisionLogID, DateTimeCreated,
(Select Top 1 BookEdition
From tblBookEdition
Where EditionPubDate < tblRevisionLog.DateTimeCreated
Order By DateTimeCreated Desc)
as BookEdition
From tblRevisionLog
Order By 1

but first I would make sure the Blue part works with the tblRevisionLog.DateTimeCreated hard-coded with the date of 12/16/1999 (following your example of records 38)


---- Andy

There is a great need for a sarcasm font.

RE: Get Top1 or Max of subquery

(OP)
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.

RE: Get Top1 or Max of subquery

Try to play with Max(), maybe....:

Select Max(BookEdition)
From tblBookEdition
Where EditionPubDate < #12/16/1999#


---- Andy

There is a great need for a sarcasm font.

RE: Get Top1 or Max of subquery

(OP)
This SQL worked. Thanks for the ideas.

CODE --> 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; 

RE: Get Top1 or Max of subquery

Try this SQL:

CODE --> 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

RE: Get Top1 or Max of subquery

I think our postings crossed in compositionland.

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

RE: Get Top1 or Max of subquery

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close