Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-26-2018, 05:54 AM   #16
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 113
Thanks: 8
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

Quote:
Originally Posted by pbaldy View Post
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.
Attached Images
File Type: png qryTutor_Rank_NearestToday.PNG (22.1 KB, 11 views)

NT100 is offline   Reply With Quote
Old 07-26-2018, 05:55 AM   #17
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Records with date nearest today

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-26-2018, 02:21 PM   #18
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 113
Thanks: 8
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 07-27-2018, 04:13 AM   #19
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 113
Thanks: 8
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: Records with date nearest today

Quote:
Originally Posted by arnelgp View Post
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.
NT100 is offline   Reply With Quote
Old 08-06-2018, 02:54 PM   #20
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,941
Thanks: 9
Thanked 3,845 Times in 3,788 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

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

__________________
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 02:59 AM.


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