SQL Question 2

[bcblair]

I am trying to write a query that selects the latest eff_date that is not greater than the date molded i have notes below on what i have written so far. it will select the ones that are not greater than date molded now i need to get it to narrow it down to the latest eff_date only.<br>
<br>
SELECT [test query].[TICKET#],<br>
[test query].MASTERMX1_ITEMNO,<br>
[test query].MASTERMX1_PLANT_NO, <br>
[test query].CMT1, <br>
[test query].EFF_DATE, <br>
[test query].[DATE MOLDED], <br>
[test query].[TEST BREAKS 2000_PLANT_NO], <br>
[test query].[MIX DESIGNS IN TEST BREAKS_ITEMNO], <br>
[test query].[MIX DESIGN], <br>
[test query].CONTRACTOR, <br>
[test query].PROJECT, <br>
[test query].[TICKET#]<br>
FROM [test query]<br>
WHERE ((([test query].EFF_DATE)&lt;([TEST QUERY].[DATE MOLDED])));<br>
<br>
!!!!!I need to add statement to narrow down to the latest eff_date that meets the rest of the criteria!!!!!!<br>

 

[JimHorton]

Try this:<br>
Select<br>
blah,<br>
blah,<br>
Where effdate &lt; datemolded<br>
AND<br>
effdate IN<br>
(Select Max(effdate) from testquery<br>
Where effdate &lt; datemolded)<br>
<br>
--Jim

 

[elizabeth]

BTW, if you wanted to build this kind of thing in the query grid, you would build the Select Max... query first, separately, and then use that query as the datasource for the second query, the one you wrote yourself. I find this helpful if all the parenthesis start getting to me.