Stumped on SQL

sirpsycho

I'm trying to use a subquery in the sql below but not having any luck. Anyone spot my error?

Basically, the subquery will return something like:

loc1 dirs1 2
loc2 dirs2 1
loc1 dirs1 3

and then I want the main query to give:

loc1 5
loc2 1

sSQL = "SELECT [mainlist].Location, Sum(CountofNCP) AS SumofNCP FROM (SELECT [mainlist].Location, dirs.directions, Count(mainlist.ncp_tt) AS CountOfNCP FROM mainlist INNER JOIN dirs ON [mainlist].Location = dirs.areadirs GROUP BY 'Location, [mainlist].Level, dirs.directions HAVING [mainlist].Level = " & mylevel_form & " OR [mainlist].Level = " & mylevel_form -1 & " OR [mainlist].Level = " & mylevel_form +1 & ") ;"

Seth Brundle

As you don't say what is going wrong, I'm just guessing the following:
The single quote at 'Location is the source.

sSQL = "
SELECT [mainlist].Location, Sum(CountofNCP) AS SumofNCP 
FROM (
     SELECT [mainlist].Location, dirs.directions, Count(mainlist.ncp_tt) AS CountOfNCP 
     FROM mainlist 
     INNER JOIN dirs ON [mainlist].Location = dirs.areadirs 
     GROUP BY [B]'Location[/B], [mainlist].Level, dirs.directions 
     HAVING [mainlist].Level = " & mylevel_form & " 
     OR [mainlist].Level = " & mylevel_form -1 & " 
     OR [mainlist].Level = " & mylevel_form +1 & ") ;"

sirpsycho

The quote at 'Location was part of the problem but I forgot to add in the GROUP BY for the main query.