Omron Forums Support Posted March 8, 2022 Share Posted March 8, 2022 Query on the Reference field and Date (last 30 days) sorted by Date (Newest to Oldest) Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where Reference="9580-KLS" AND LclTime >= #2019-10-02 16:26:38# Order By LclTime DESC Query on the DecodedText field and Date (last 30 days) sorted by Date newest to oldest Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="RGT23244037" AND LclTime >= #2019-10-02 16:31:55# Order By LclTime DESC Query (DecodedText field and Date range) sorted by Date (Newest to Oldest) Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01 16:31:55# AND #2019-01-01 16:31:55# Order By LclTime DESC Or sorted by ReportID (Descending) Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01 16:31:55# AND #2019-01-01 16:31:55# Order By ReportID DESC This works also if you don’t want to specify the time of day. Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText="012345678905" AND LclTime BETWEEN #2019-11-01# AND #2019-01-01# Order By ReportID DESC Query covering data from November 1, 2019 back to November 1, 2018 is: Select ReportID, SectorID, LclTime, Reference, OverallGrade, DecodedText From Reports Where DecodedText= "12345678" and LclTime>=#2018-11-01# Order By ReportID JOINING TWO DATA TABLES Report joining two data tables for filtering by Reference (Report table) and Additional reference (Report.Data table) Select Reports.ReportID, SectorID, LclTime, Reference, ReportData.ParameterValue as AddtionalReference, OverallGrade,DecodedText From Reports INNER JOIN ReportData ON Reports.ReportID = ReportData.ReportID Where ReportData.ParameterName = "Additional reference" AND ReportData.ParameterValue="xxx" AND Reports.Reference ="xxx" Order By Reports.ReportID Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.