Ayaz49 Ayaz49 - 1 year ago 45
SQL Question

How can I get selective records from a table in access

Given below are the results of my query. But there are many redundant records, therefore, I want to filter out the results of this query. My goal is to extract only two records per angle, first and the last.
For example when the angle is 195,
I want to get its first record when the date=2/27/2017, time=2:00:00 AM and
the second record when the date is 2/27/2017 and time=9:00:00 AM.
Similarly when the angle is changed to 210 I want to get its first record when the date=2/27/2017 time=10:00:00 AM and the other record when the date and time is 2/27/2017 and 9:00:00 PM.
And similarly for all the records.
I tried to do it myself but it only returns one record per angle only the top one, dont know how do I get the last one.
I am doing it using two queries, (Query1)

SELECT final.Date, final.Angle
FROM final
GROUP BY final.Date, final.Angle


and second query is (fileredOUtput)

SELECT Query1.Date, Query1.Angle, (SELECT TOP 1 final.Date FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle)
AS NewDate,
(SELECT TOP 1 final.Angle FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewAngle,
(SELECT TOP 1 final.earthCol.Value FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewE_CV,
(SELECT TOP 1 final.earthCol.ColNu FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS New_E_CN,
(SELECT TOP 1 final.mars_Col.Value FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewM_CV,
(SELECT TOP 1 final.Col_apart FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS New_CApart,
(SELECT TOP 1 final.mars_Col.ColNu FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewM_CN,
(SELECT TOP 1 final.Time FROM final WHERE Query1.Date=final.Date AND Query1.Angle= final.Angle) AS NewTime
FROM Query1, final
WHERE (((Query1.Date) Between [Forms]![Query Form]![txtStartDate] And [Forms]![Query Form]![txtEndDate]));


Query results and results I need are marked with red.
Looking forward to hear from you.
Thank you.

Answer Source

It could be:

SELECT 
    * 
FROM 
    final,

    (SELECT 
        final.Date, Min(final.Time) As MinTime, Max(final.Time) As MaxTime, final.Angle
    FROM 
        final
    GROUP BY 
        final.Date, final.Angle) As T

WHERE
    final.Angle = T.Angle AND
    final.Date = T.Date AND
    ((final.Time = T.MinTime) OR (final.Time = T.MaxTime))
ORDER BY
    final.Date, final.Time, final.Angle
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download