selecting one row from multiple rows based on a date calculation 1

[KMAC54]

I'm doing a query on two tables linked by an ID number.

Table 1 has one line per ID number that contains an event date we'll call majordate.
Table 2 has multiple lines per ID number that contains multiple event dates we'll call minordate and another field we'll call parameter1.

Can anyone tell me how to select parameter1 from Table 2 from the row that yields the smallest positive DateDiff("d", [majordate],[minordate])?

Thank you in advance for your time and skill.

 

[larryww]

Create a field DateDifference at the time each minor entry is made; sort; and use "TOP 1"

 

[WildHare]

SELECT TOP 1 TableA.ID, TableA.MajorDate, TableB.MinorDate, TableB.Parameter DateDiff("d",[MajorDate],[MinorDate]) AS DD
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.ID
ORDER BY DateDiff("d",[MajorDate],[MinorDate]);
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[KMAC54]

thanks; I'm a little unclear on how to implement the "TOP 1" instruction. This sounds like a setting for a sort command, but I'm not familiar with it.

thanks

 

[WildHare]

Design your query as LarryWW and I suggest - then go in to SQL view , and add "TOP 1" just where you see it, after the Select keyword.

The " TOP n " modifier doesn't sort, it skims - sort by DateDifference, in Ascending order, and skim off the TOP 1. Thus, the smallest difference...

You get this via that tiny little combo box in the Query Design toolbar - it normally reads "100%" or something. But you can change it.



Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com