I have a macro with the following code:
sSQL = "select [Folio Type], [Folio ID], [Departure Date]," & _
" Sum([Folio Total]), Count([Folio ID])" & _
" from [Individual Folios$B2:O150000]" & _
" group by [Folio Type], [Departure Date], [Folio ID], [Folio Total]" & _
" having Sum([Folio Total]) <> 0"
The code above gives me an incorrect count of 1 for each [Folio ID]. I want to get the total count of a given [Folio ID], regardless of [Departure Date]. When I remove some of the fields I get different results - the following code appears to be working correctly:
sSQL = "select [Folio ID], Count([Folio ID]), Sum([Folio Total])" & _
" from [Individual Folios$B2:O150000]" & _
" group by [Folio ID], [Folio Total]" & _
" having sum([Folio Total]) <> 0"
I need to figure out how to get the same results as above after adding the removed fields back in. My guess is that the query is counting occurrences by taking all the fields into consideration. If that's the case, is there any easy workaround?
You need to do this in two queries. I've put the second one as a subquery in the FROM clause.
SELECT
a.[Folio Type]
,a.[Folio ID]
,a.[Departure Date]
,b.FolioCount
,b.FolioSum
FROM
[Individual Folios$B2:O150000] a
INNER JOIN
(SELECT
[Folio ID], [Folio Type]
,Count([Folio ID]) As FolioCount
,SUM([Folio Total]) As FolioSum
FROM
[Individual Folios$B2:O150000]
GROUP BY
[Folio ID]
,[Folio Type]
HAVING SUM([Folio Total]) <> 0
) b
ON a.[Folio ID] = b.[Folio ID]
Make your string equal to this query:
sSQL = "SELECT a.[Folio Type],a.[Folio ID],a.[Departure Date]" _
& ",b.FolioCount,b.FolioSum" _
& " FROM [Individual Folios$B2:O150000] a" _
& " INNER JOIN" _
& " (SELECT [Folio ID], [Folio Type] " _
& " Count([Folio ID]) As FolioCount,SUM([Folio Total]) As FolioSum" _
& " FROM [Individual Folios$B2:O150000]" _
& " GROUP BY [Folio ID], [Folio Type]" _
& " HAVING SUM([Folio Total]) <> 0) b"_
& " ON a.[Folio ID] = b.[Folio ID]"