How to build SQL Query executed in VBA?

Question

I made the following group by query :

SQL = " SELECT DISTINCTROW [A], [B], [C],[D], Sum([E]) AS [Sum E]" & _
      " FROM [SheetName$RangeAddress]" & _
      " GROUP BY [A], [B], [C],[D];"

This returns a too large amount of data where I wish to have only the TOP 5 so I tried the following:

SQL = " SELECT TOP 5 FROM (SELECT DISTINCTROW [A], [B], [C],[D], Sum([E]) AS [Sum E]" & _
      " FROM [SheetName$RangeAddress]" & _
      " GROUP BY [A], [B], [C],[D]) ORDER BY [Sum E] DESC;"

This throws the following error:

The SELCT Statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect

Gustav · Accepted Answer · 2022-11-09 17:52:05Z

1

Try this:

SQL = " SELECT TOP 5 * FROM" & _
      "     (SELECT DISTINCTROW [A], [B], [C], [D], Sum([E]) AS [Sum E]" & _
      "     FROM [SheetName$RangeAddress]" & _
      "     GROUP BY [A], [B], [C], [D]) AS T"
      " ORDER BY T.[Sum E] DESC;"

answered Nov 9, 2022 at 17:52

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to build SQL Query executed in VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related