Te4t0n Posted December 16, 2008 Share Posted December 16, 2008 TBL_camp_total (Table)Mill Roll Order: Textline: TextMinOfDate: Date/TimeMaxOfDate: Date/TimeType/Micron: TextHandover_corr: NumberPlannedEventsMR Number: TextQuerry:SELECT Max(TBL_camp_total.MinOfDate) AS MaxOfMinOfDate, TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51"));When i run this at current i get 69 records. It works fine.I would like to also extract the Handover_corrBut if i just add it and group by i get 1000's of records...I tried you usingSELECT Handover_corr FROM TBL_camp_totalWHERE (TBL_camp_total.MinOfDate, TBL_camp_total.[Type/Micron]) IN(SELECT Max(TBL_camp_total.MinOfDate) AS MaxOfMinOfDate, TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51")))andSELECT Handover_corr FROM TBL_camp_totalWHERE (TBL_camp_total.MinOfDate+TBL_camp_total.[Type/Micron]) IN(SELECT Max(TBL_camp_total.MinOfDate) AS MaxOfMinOfDate+TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51")))andSELECT * FROM TBL_camp_totalWHERE (SELECT TBL_camp_total.[Type/Micron]FROM Plannedevents RIGHT JOIN TBL_camp_total ON Plannedevents.[MR Number] = TBL_camp_total.[Mill Roll Order]WHERE (((TBL_camp_total.MaxOfDate) Is Not Null))GROUP BY TBL_camp_total.[Type/Micron], TBL_camp_total.lineHAVING (((TBL_camp_total.line)="D51"))) as SQL1 INNER JOIN TBL_camp_total ON (SQL1.MaxOfMinOfDate = TBL_camp_total.MinOfDate) AND (SQL1.[Type/Micron] = TBL_camp_total.[Type/Micron]);i want the handover_corr for every Type/Micron which my sub query returns. I Also don't know how to convert a datetime into a varchar. I'm using sql within Access btw. thks Link to comment Share on other sites More sharing options...
Te4t0n Posted December 17, 2008 Author Share Posted December 17, 2008 Can an Admin Remove this please. Thks Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.