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: 72,726
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...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Jul 11 2018, 05:25 AM
Post#3


UtterAccess VIP
Posts: 9,820
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?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
RJD
post Jul 11 2018, 12:18 PM
Post#4


UtterAccess VIP
Posts: 8,473
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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Jul 11 2018, 01:27 PM
Post#5


UtterAccess VIP
Posts: 9,820
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).

--------------------
Robert Crouser

My company's website
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: 8,473
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: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
John Vinson
post Jul 12 2018, 04:04 PM
Post#8


UtterAccess VIP
Posts: 4,243
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?

--------------------
John W. Vinson
Wysard of Information
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: 8,473
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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
John Vinson
post Jul 12 2018, 06:55 PM
Post#12


UtterAccess VIP
Posts: 4,243
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

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
RJD
post Jul 12 2018, 08:15 PM
Post#13


UtterAccess VIP
Posts: 8,473
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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
John Vinson
post Jul 12 2018, 08:30 PM
Post#14


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


Hey, youngster... some respect for your elders here please! pullhair.gif

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
RJD
post Jul 12 2018, 08:43 PM
Post#15


UtterAccess VIP
Posts: 8,473
Joined: 25-October 10
From: Gulf South USA


notworthy.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th July 2018 - 07:20 AM