Records with date nearest today (1 Viewer)

NT100

Registered User.
Local time
Today, 17:54
Joined
Jul 29, 2017
Messages
148
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:54
Joined
Sep 21, 2011
Messages
14,038
Create another field with the difference in days between today and that date, then sort ascending on that field.?
 

Minty

AWF VIP
Local time
Today, 09:54
Joined
Jul 26, 2013
Messages
10,353
What happens if there is a tie, which date wins ??
 

Mark_

Longboard on the internet
Local time
Today, 02:54
Joined
Sep 12, 2017
Messages
2,111
Do you need to worry about future dates?
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:54
Joined
Jan 23, 2006
Messages
15,361
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;
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 05:54
Joined
Jan 23, 2006
Messages
15,361
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?
 

NT100

Registered User.
Local time
Today, 17:54
Joined
Jul 29, 2017
Messages
148
I have another question that i want to get the rest columns like 'Rank' below with the nearest today script provided.

PK E_date, Rank
3 1/10/2013, 3
3 4/15/2017, 4
3 7/01/2018, 4
9 3/6/2015, 2
9 5/22/2018, 3
11 11/21/2017,5

How do I get RANK's values as well?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:54
Joined
Aug 30, 2003
Messages
36,118
That's what the second query in the link does.
 

NT100

Registered User.
Local time
Today, 17:54
Joined
Jul 29, 2017
Messages
148
That's what the second query in the link does.

qryApptT_NearestToday
SELECT tblTAppt.TRef, Max(tblTAppt.ApptEnd_Dt) AS Nearest_Dt
FROM tblTAppt
GROUP BY tblTAppt.TRef;

qryTutor_Rank_NearestToday
SELECT qryApptT_NearestToday.TRef, qryApptT_NearestToday.Nearest_Dt, tblTutor.LastName, tblTutor.FirstName, tblTRank.HDesc, tblTAppt.ApptEnd_Dt
FROM tblTRank INNER JOIN (tblTAppt INNER JOIN (qryApptT_NearestToday INNER JOIN tblTutor ON qryApptT_NearestToday.TRef = tblTutor.TRef) ON tblTAppt.TRef = qryApptT_NearestToday.TRef) ON tblTRank.ID = tblTAppt.RankCode;

Once I added "tblTAppt.ApptEnd_Dt = qryApptT_NearestToday.Nearest_Dt" into the above query and the query is shown below. It runs into error of "Syntax error in JOIN operation". I am stuck the query construct.

qryTutor_Rank_NearestToday
SELECT qryApptT_NearestToday.TRef, qryApptT_NearestToday.Nearest_Dt, tblTutor.LastName, tblTutor.FirstName, tblTRank.HDesc, tblTAppt.ApptEnd_Dt
FROM tblTRank INNER JOIN (tblTAppt INNER JOIN (qryApptT_NearestToday INNER JOIN tblTutor ON qryApptT_NearestToday.TRef = tblTutor.TRef) ON tblTAppt.TRef = qryApptT_NearestToday.TRef) ON tblTRank.ID = tblTAppt.RankCode AND tblTAppt.ApptEnd_Dt = qryApptT_NearestToday.Nearest_Dt;


tblTutor (TRef is the primary key)
tblTAppt (TRef is a foreign key)
qryAppt_NearestToday (TRef is a foreign key)

Please see the attached file for the tables relationship.

Would you suggest for the query construct.

Thank you.
 

Attachments

  • qryTutor_Rank_NearestToday.PNG
    qryTutor_Rank_NearestToday.PNG
    22.1 KB · Views: 42

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:54
Joined
May 7, 2009
Messages
19,169
how do you want it to rank?
group by PK, then by E_Date?, eg:

PK E_date, Rank
3 1/10/2013, 1
3 4/15/2017, 2
3 7/01/2018, 3
9 3/6/2015, 1
9 5/22/2018, 2
11 11/21/2017, 1
 

NT100

Registered User.
Local time
Today, 17:54
Joined
Jul 29, 2017
Messages
148
how do you want it to rank?
group by PK, then by E_Date?, eg:

PK E_date, Rank
3 1/10/2013, 1
3 4/15/2017, 2
3 7/01/2018, 3
9 3/6/2015, 1
9 5/22/2018, 2
11 11/21/2017, 1

The "Rank" is a code, it represents job titles (e.g. Assistant Professor, Associate Professor, Professor, etc.). The Rank code will be linked to "tblRank" to look up its job title. The query output needs to report the job titles based on the Rank code.
 

NT100

Registered User.
Local time
Today, 17:54
Joined
Jul 29, 2017
Messages
148
how do you want it to rank?
group by PK, then by E_Date?, eg:

PK E_date, Rank
3 1/10/2013, 1
3 4/15/2017, 2
3 7/01/2018, 3
9 3/6/2015, 1
9 5/22/2018, 2
11 11/21/2017, 1

I dont want to rank them, i just need to list the field (Rank) value with Tutor's primary key(PK) and the date which is the nearest to Today.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:54
Joined
Aug 30, 2003
Messages
36,118
Did you get this sorted? I got out of the way but it looks like you've been left in the lurch.
 

Users who are viewing this thread

Top Bottom