SQL Question followup

[bcblair]

I still cant get this to work right. listed below is the sql. I am trying to filter to the latest EFF_Date so long as it is not after the DATE MOLDED. The following returns only six 9 records. and I can look at the original query and see that more are in that meet the criteria. If you want you can email me and I will call you I have nationwide free long distance. My Email is <A HREF="mailto:bcblair@home.com">bcblair@home.com</A>.<br>
<br>
SELECT [test query].CODE, [test query].[TICKET#], [test query].MASTERMX1_ITEMNO, [test query].MASTERMX1_PLANT_NO, [test query].CMT1, [test query].EFF_DATE, [test query].[DATE MOLDED], [test query].[TEST BREAKS 2000_PLANT_NO], [test query].[MIX DESIGNS IN TEST BREAKS_ITEMNO], [test query].[MIX DESIGN], [test query].CONTRACTOR, [test query].PROJECT, [test query].[TICKET#]<br>
FROM [test query]<br>
<br>
!!!!!!below seems to be the problem!!!!!<br>
WHERE ((([test query].EFF_DATE) In (Select Max([test query].eff_date) from [test query]<br>
Where [test query].eff_date &lt; [test query].[date molded])));<br>
<br>
<br>

 

[JimHorton]

blcair,<br>
I guess I'm not sure of what you want as a result. So, you have, in the same table, a field Eff_date and a field Date_molded? You want to return the record(s) with the latest Eff_date that does not surpass it's own Date_molded? So, the result is going to be *one* record, or group of records all having the *same* date. If that's the case, my original sql should do (in your example you didn't have the first part of the Where clause in the main query):<br>
<br>
SELECT blah,blah, from [test query] where eff_date &lt; date_molded and eff_date in<br>
(select max(eff_date) from [test query] where eff_date &lt; date_molded)<br>
<br>
Let me know if you want some other result, or if I misunderstood your specs...<br>
--Jim