0

I have a table for which I want to return the top 3 records by ErrorMargin per week commencing in Access 2010.

The problem I'm having is that 0 values are being ignored and I'd like to see just 1 record in the case of ties where the tie would take the total number of records over 3.

The table I have is:
NB: VBA at bottom of post to create the table.

 TMID   WeekCommencing  ErrorMargin
    1   05-Oct-15   0
    1   12-Oct-15   2
    3   05-Oct-15   1
    3   12-Oct-15   1
    8   12-Oct-15   2
    9   05-Oct-15   0.333333333
    9   12-Oct-15   4
    12  05-Oct-15   0
    12  12-Oct-15   1.5

The SQL I have at the moment is:

SELECT      T1.TMID,
            T1.WeekCommencing,
            T1.ErrorMargin,
            COUNT(*)
FROM        qry_REP_ErrorMargin T1 INNER JOIN qry_REP_ErrorMargin T2 ON
                T1.ErrorMargin <= T2.ErrorMargin AND
                T1.WeekCommencing = T2.WeekCommencing
GROUP BY    T1.TMID,
            T1.WeekCommencing,
            T1.ErrorMargin
HAVING      COUNT(*) <= 3
ORDER BY    T1.WeekCommencing,
            T1.ErrorMargin

This returns the following table, which is only showing two records for the 5/10/2015 - there are two further records with a 0 ErrorMargin and I'd like it to return one of those as well. It doesn't matter which. The TMID and WeekCommencing fields would make up the key field for the table.

TMID    WeekCommencing  ErrorMargin Expr1003
9       05/10/2015      0.33        2
3       05/10/2015      1           1
1       12/10/2015      2           3
8       12/10/2015      2           3
9       12/10/2015      4           1

I've had a play around with other solutions, but haven't managed to get anything to work yet - MS Access Select top n query grouped by multiple fields

VBA Code to create the table:

Sub Create()

    Dim db As DAO.Database
    Set db = CurrentDb

    db.Execute "CREATE TABLE qry_REP_ErrorMargin" & _
        "(TMID LONG, WeekCommencing DATE, ErrorMargin Double)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (1,42282,0)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (1,42289,2)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (3,42282,1)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (3,42289,1)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (8,42289,2)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (9,42282,0.333333333333333)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (9,42289,4)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (12,42282,0)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (12,42289,1.5)"

End Sub
Community
  • 1
  • 1
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • why `9 | 05/10/2015 | 0.33 | 2` have count = 2 ?? – Juan Carlos Oropeza Oct 27 '15 at 17:47
  • btw your query return a different result using your data http://sqlfiddle.com/#!6/00f48/1 – Juan Carlos Oropeza Oct 27 '15 at 17:48
  • What mean `top 3 for group` because you say doesnt care wich 0 go first. and you should have double 0. and then 0.333 – Juan Carlos Oropeza Oct 27 '15 at 17:50
  • Thanks for your comments Juan. I don't see what you mean by that record having a count of 2 and I don't think sqlfiddle is executing the SQL the same way that Access would as it's giving different results. You're correct - for the 5th October should have 0, 0 and 0.333 but as I said in my post - it's ignoring 0's and it shouldn't be. – Darren Bartrup-Cook Oct 28 '15 at 09:09

1 Answers1

2

The following may do what you want:

SELECT em.*
FROM qry_REP_ErrorMargin as em
WHERE em.TMID IN (SELECT TOP 3 TMID
                  FROM qry_REP_ErrorMargin as em2
                  WHERE em2.WeekCommencing = em.WeekCommencing
                  ORDER BY em2.ErrorMargin
                 );

Note that in the case of ties, MS Access might return more than three rows. It you do not want duplicates, then include an id column in the ORDER BY to prevent ties:

ORDER BY em2.ErrorMargin, em2.TMID
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. With the extra `ORDER BY` on the ID column it's returning exactly what I want - with the example data. Just need to test it with various data sets and I'll post back in a couple of hours to accept the answer. – Darren Bartrup-Cook Oct 28 '15 at 09:19