Hello
I have a form that has a search button. Search button calls on a query.
SELECT DISTINCTROW Info.InfoEntryID, Info.InfoBarrelNum, Info.InfoPh, Info.InfoSalinity, Info.InfoAcid, Info.InfoDip, Info.InfoText, Info.InfoMRU, Info.InfoBarrelID, Max(Info.InfoDate) AS MaxOfInfoDate
FROM Info
GROUP BY Info.InfoEntryID, Info.InfoBarrelNum, Info.InfoPh, Info.InfoSalinity, Info.InfoAcid, Info.InfoDip, Info.InfoText, Info.InfoMRU, Info.InfoBarrelID
HAVING (((Info.InfoPh)>=[Forms]![frmInfoBatchEntry]![phLow] And (Info.InfoPh)<=[Forms]![frmInfoBatchEntry]![phHigh]) AND ((Info.InfoSalinity)>=[Forms]![frmInfoBatchEntry]![SaltLow] And (Info.InfoSalinity)<=[Forms]![frmInfoBatchEntry]![SaltHigh]) AND ((Info.InfoAcid)>=[Forms]![frmInfoBatchEntry]![AcidLow] And (Info.InfoAcid)<=[Forms]![frmInfoBatchEntry]![AcidHigh]) AND ((Info.InfoDip)>=[Forms]![frmInfoBatchEntry]![DipLow] And (Info.InfoDip)<=[Forms]![frmInfoBatchEntry]![DipHigh]) AND ((Max(Info.InfoDate))>=[Forms]![frmInfoBatchEntry]![DateFrom] And (Max(Info.InfoDate))<=[Forms]![frmInfoBatchEntry]![DateTo]));
The outcome of the query is below but incorrect:
Range 1/1/2010 - 2/1/2010
Date Barrel Acid
1/1/2010 5 6
1/2/2010 5 7
1/1/2010 6 8
5/1/2010 7 1
However the correct out put should be 3 records, first record is gone because it is not the latest input for that barrel.
Date Barrel Acid
1/2/2010 5 7
1/1/2010 6 8
5/1/2010 7 1
For any given date range (((Info.InfoDate)>=[Forms]![frmInfoBatchEntry]![DateFrom] And (Info.InfoDate)<=[Forms]![frmInfoBatchEntry]![DateTo])
if there are multiple barrels select the barrel with the latest date. So it is date range with maximum date if there are multiple identical barrels.
Any ideas or examples would be greatly appreciated.
Thank you