SQL Conversion Help Needed

[scott_minor]

This is the sql statement I wrote to get the records in MySQL. Now I need to do the same thing in Access.



SELECT SUM(s_series), bowled FROM (
SELECT *
FROM
(SELECT bowled, setnr, s_series,
@country_rank := IF(@current_country = bowled, @country_rank + 1, 1) AS country_rank,
@current_country := bowled
FROM scores
WHERE serno = 'T1'
ORDER BY bowled, setnr, s_series
) ranked
WHERE country_rank <= 2
) a GROUP BY bowled ORDER BY SUM(s_series) DESC LIMIT 1

I have read and tried I don't know how many different ideas on the web to make the "RANKING" work with Access but I can't get it to work.

Here is what the data looks like

SERNO	TeamNr	Bowled	SetNr	GM1	GM1H	GM2	GM2H	GM3	GM3H	S_Series	H_Series
T1	1	8 /5 /2012	1	984	1011	940	972	1016	1046	2940	3029
T1	1	8 /5 /2012	2	1103	1103	1056	1056	953	953	3112	3112
T1	1	8 /5 /2012	3	963	981	984	1007	987	998	2934	2986
T1	1	8 /26/2012	4	986	986	1001	1001	989	989	2976	2976
T1	1	8 /26/2012	5	1040	1040	1055	1055	988	988	3083	3083
T1	1	8 /26/2012	6	955	955	979	979	916	916	2850	2850
T1	1	9 /16/2012	7	986	1037	1105	1150	1017	1064	3108	3251
T1	1	9 /16/2012	8	910	910	887	887	1006	1006	2803	2803
T1	1	9 /16/2012	9	1019	1048	1038	1067	971	976	3028	3091
T1	1	10/14/2012	10	972	972	887	887	940	940	2799	2799
T1	1	10/14/2012	11	941	941	925	925	895	895	2761	2761
T1	1	10/14/2012	12	853	853	938	938	974	974	2765	2765
T1	1	10/28/2012	13	966	966	996	996	1169	1169	3131	3131
T1	1	10/28/2012	14	859	922	961	1012	947	987	2767	2921
T1	1	10/28/2012	15	848	869	1061	1070	1076	1085	2985	3024
T1	1	11/18/2012	16	938	938	1016	1016	949	949	2903	2903
T1	1	11/18/2012	17	998	998	1060	1060	1017	1017	3075	3075
T1	1	11/18/2012	18	979	979	961	961	991	991	2931	2931
T1	1	12/9 /2012	19	866	893	906	916	868	906	2640	2715
T1	1	12/9 /2012	20	923	930	871	915	996	1015	2790	2860
T1	1	12/9 /2012	21	857	916	922	982	902	978	2681	2876
T1	1	1 /6 /2013	22	932	932	1029	1029	1003	1003	2964	2964
T1	1	1 /6 /2013	23	1104	1115	1083	1094	1036	1055	3223	3264
T1	1	1 /6 /2013	24	1021	1022	1091	1092	1050	1051	3162	3165
T1	1	1 /27/2013	25	903	903	958	964	986	1001	2847	2868
T1	1	1 /27/2013	26	992	992	981	981	971	971	2944	2944
T1	1	1 /27/2013	27	1049	1077	955	982	979	1006	2983	3065
T1	1	2 /17/2013	28	1171	1171	905	905	956	956	3032	3032
T1	1	2 /17/2013	29	960	960	951	951	1047	1047	2958	2958
T1	1	2 /17/2013	30	1026	1026	1016	1016	1000	1000	3042	3042
T1	1	3 /3 /2013	31	1060	1060	868	868	1009	1009	2937	2937
T1	1	3 /3 /2013	32	976	1021	1082	1134	979	1037	3037	3192
T1	1	3 /3 /2013	33	1055	1075	1194	1226	1015	1038	3264	3339
T1	1	3 /24/2013	34	972	972	941	941	987	987	2900	2900
T1	1	3 /24/2013	35	1049	1049	991	991	963	963	3003	3003
T1	1	3 /24/2013	36	921	976	1036	1079	971	1014	2928	3069

I need only the first two records from each date. You see will see there are 3.

The rank should look something like this

T1 8/3/2012 1
T1 8/3/2012 2
T1 8/26/2012 4
T1 8/26/2012 5

And then I can sum the series and get the largest for the date.

Thanks for your help...

[June7]

Start with review of

http://allenbrowne.com/ranking.html

http://allenbrowne.com/subquery-01.html#TopN

[scott_minor]

Start with review of

http://allenbrowne.com/ranking.html

http://allenbrowne.com/subquery-01.html#TopN

Thanks I have already been here looking for help. I haven't been able to make it work with the help on these pages.

[ItsMe]

Have you considered using the SQL query builder within access?

Since you need to use a subquery, perhaps building a couple of Access queries would offer some insight. Using the GUI and saving a query may give you the result you need and then you can go back and use SQL view to place that SQL within VBA.

[June7]

If you just want the top 2 for each date, that would be 'TOP N records per group'.

What are @current_country and @country_rank?

With Allen's help, I get this:
SELECT * FROM scores WHERE SetNr IN (SELECT TOP 2 SetNr FROM scores AS Dupe WHERE Dupe.Bowled = scores.Bowled ORDER BY Dupe.Bowled DESC, Dupe.SetNr);