UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query On The Dates Which Nearest To Today, Access 2016    
 
   
NT100
post Jul 10 2018, 11:06 PM
Post#1



Posts: 16
Joined: 11-January 18



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.
Go to the top of the page
 
theDBguy
post Jul 10 2018, 11:15 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,704
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I suppose you could add a DateDiff() column in your query and then use it to determine the Min() record to return per PK.


Just a thought...
Go to the top of the page
 
projecttoday
post Jul 11 2018, 05:25 AM
Post#3


UtterAccess VIP
Posts: 10,245
Joined: 10-February 04
From: South Charleston, WV


Are you disregarding the year for this calculation? So 7/12/1999 would be closer that 1/1/2018?
Go to the top of the page
 
RJD
post Jul 11 2018, 12:18 PM
Post#4


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


Hi: Looking at the data you posted and trying to interpret your requirement, it looks to me like (and, of course, I could well be wrong) all the dates are in the past and you want the latest date (closest to the current date) for each PK. If this is correct, then you can get this with a simple Totals (Group By) query like this ...

SELECT PK, Max(E_date) FROM yourtablename GROUP BY PK;

(untested)

Is this what you are trying to do? If not, perhaps you could explain a bit more the requirement.

HTH
Joe
Go to the top of the page
 
projecttoday
post Jul 11 2018, 01:27 PM
Post#5


UtterAccess VIP
Posts: 10,245
Joined: 10-February 04
From: South Charleston, WV


I think you're right, Joe. NT100 should have noted that it is by id (assuming that's it).
Go to the top of the page
 
NT100
post Jul 11 2018, 05:35 PM
Post#6



Posts: 16
Joined: 11-January 18



With "SELECT PK, Max(E_date) FROM yourtablename GROUP BY PK", I will get all E_date, this is not what I wanted. I just wanted to get E_date which is/are the nearest to today
Go to the top of the page
 
RJD
post Jul 11 2018, 06:30 PM
Post#7


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


QUOTE
I will get all E_date, this is not what I wanted.

Not so. This is not correct.

QUOTE
I just wanted to get E_date which is/are the nearest to today

That's what you get with the suggested query.

See the demo attached, using your posted records. Verify the table records and see the results of the query. The results are exactly as you posted as your requirement.

HTH
Joe

Attached File(s)
Attached File  NearestDates.zip ( 17.84K )Number of downloads: 6
 
Go to the top of the page
 
John Vinson
post Jul 12 2018, 04:04 PM
Post#8


UtterAccess VIP
Posts: 4,273
Joined: 6-January 07
From: Parma, Idaho, US


PK is obviously not the PK (Primary Key) of this table, since it's not unique; however, if you did group by a table's Primary Key you would indeed get all the records.

Clarification needed?
Go to the top of the page
 
NT100
post Jul 12 2018, 05:23 PM
Post#9



Posts: 16
Joined: 11-January 18



Perfect! Yes, it's what I wanted. Thank you.
Go to the top of the page
 
NT100
post Jul 12 2018, 05:24 PM
Post#10



Posts: 16
Joined: 11-January 18



No, it's not the primary key of the table but a foreign key. Sorry for my careless mistake.
Go to the top of the page
 
RJD
post Jul 12 2018, 06:47 PM
Post#11


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are all happy to help.

No problem with the pk. I guessed what you meant.

Regards
Joe

From phone
Go to the top of the page
 
John Vinson
post Jul 12 2018, 06:55 PM
Post#12


UtterAccess VIP
Posts: 4,273
Joined: 6-January 07
From: Parma, Idaho, US


I should know better than to second-guess Joe: his telepathy is a lot better than mine! smile.gif
Go to the top of the page
 
RJD
post Jul 12 2018, 08:15 PM
Post#13


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


Must be an age thing, John, now that I don't have to take off my shoes going through airport security! ohyeah.gif

Joe
Go to the top of the page
 
John Vinson
post Jul 12 2018, 08:30 PM
Post#14


UtterAccess VIP
Posts: 4,273
Joined: 6-January 07
From: Parma, Idaho, US


Hey, youngster... some respect for your elders here please! pullhair.gif
Go to the top of the page
 
RJD
post Jul 12 2018, 08:43 PM
Post#15


UtterAccess VIP
Posts: 9,148
Joined: 25-October 10
From: Gulf South USA


notworthy.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2018 - 11:39 PM