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