Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-10-2018, 07:29 AM   #1
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 99
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Records with date nearest today

Hi,

I intend to build a query to get the records with the dates nearest today.

Below is the table with a few samples

PK E_date
3 1/10/2018
3 4/15/2018
3 7/01/2018
9 3/6/2018
9 5/22/2018
11 11/21/2017

The query output should be
3 7/01/2018
9 5/22/2018
11 11/21/2017

Welcome any suggestions.

NT100 is offline   Reply With Quote
Old 07-10-2018, 07:45 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,039
Thanks: 259
Thanked 307 Times in 292 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: Records with date nearest today

Create another field with the difference in days between today and that date, then sort ascending on that field.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-10-2018, 08:07 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,345
Thanks: 126
Thanked 1,460 Times in 1,432 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Records with date nearest today

What happens if there is a tie, which date wins ??

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-10-2018, 09:21 AM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,216
Thanks: 13
Thanked 230 Times in 228 Posts
Mark_ will become famous soon enough
Re: Records with date nearest today

Do you need to worry about future dates?
Mark_ is offline   Reply With Quote
Old 07-11-2018, 02:32 PM   #5
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 99
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

Quote:
Originally Posted by Mark_ View Post
Do you need to worry about future dates?
No, The query is just for getting the past dates which nearest to today.
NT100 is offline   Reply With Quote
Old 07-11-2018, 02:38 PM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,359
Thanks: 49
Thanked 1,843 Times in 1,793 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Records with date nearest today

What does PK mean in this??
PK E_date
3 1/10/2018
3 4/15/2018
3 7/01/2018
9 3/6/2018
9 5/22/2018
11 11/21/2017

Sounds like you want
Code:
SELECT Max(E_Date) AS MaxOfE_Date, PK
FROM YourTableName
GROUP BY PK;
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 07-11-2018 at 02:44 PM.
jdraw is online now   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
NT100 (07-12-2018)
Old 07-11-2018, 02:43 PM   #7
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 99
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

PK means primary key

NT100 is offline   Reply With Quote
Old 07-11-2018, 02:45 PM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,359
Thanks: 49
Thanked 1,843 Times in 1,793 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Records with date nearest today

How do you have multiple records in the table with the same Primary Key?

Does nearest to today mean the latest Date per PK in the table?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 07-12-2018, 02:15 PM   #9
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 99
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

Yes, it's.
NT100 is offline   Reply With Quote
Old 07-12-2018, 02:27 PM   #10
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 99
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

Quote:
Originally Posted by NT100 View Post
PK means primary key
Sorry for my careless mistake. "PK" is a foreign key in this table.
NT100 is offline   Reply With Quote
Old 07-12-2018, 03:47 PM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,530
Thanks: 9
Thanked 3,777 Times in 3,720 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Records with date nearest today

How about:

http://www.baldyweb.com/LastValue.htm

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
today or before today date criteria antonyx Queries 3 02-23-2007 12:27 PM
Update all records with today's date mariaw Forms 4 10-11-2006 05:10 AM
How do I get nearest start date and nearest end date? Rebel Queries 7 06-27-2005 06:42 AM
Find records using today's date rglance9 Queries 1 06-13-2004 04:08 PM
report only today date&today events celosia Reports 3 03-20-2002 10:43 PM




All times are GMT -8. The time now is 12:51 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World