Records with date nearest today

[NT100]

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]

Create another field with the difference in days between today and that date, then sort ascending on that field.?

 

[Minty]

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

 

[Mark_]

Do you need to worry about future dates?

 

[NT100]

Do you need to worry about future dates?

No, The query is just for getting the past dates which nearest to today.

 

[jdraw]

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

SELECT Max(E_Date) AS MaxOfE_Date, PK
FROM YourTableName
GROUP BY PK;

 

[NT100]

PK means primary key

 

[jdraw]

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]

Yes, it's.

 

[NT100]

PK means primary key

Sorry for my careless mistake. "PK" is a foreign key in this table.

 

[pbaldy]

How about:

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

 

[NT100]

How about:

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

Perfect work!

 

[pbaldy]

Happy to help!

 

[NT100]

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]

That's what the second query in the link does.

 

[NT100]

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.

 

[arnelgp]

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]

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]

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]

Did you get this sorted? I got out of the way but it looks like you've been left in the lurch.