Sql Statement to filter range.

[HansV]

So you're not interested in 1 to 9 and in 19 to 65?
And between 13 to 19 overlaps with both between 10 to 15 and between 16 to 18. Is that intentional?

[adam]

Sorry for that. Age category could be like;

Less than 1 Year, Between 1 AND 4 Years, Between 5 AND 9 Years, Between 10 AND 14 Years, Between 15 AND 17 Years, Between 18 AND 24 Years, Between 25 AND 34 Years, Between 35 AND 49 Years, Between 50 AND 64 Years, 65 and above.

[HansV]

Try this:

Code: Select all

    strSQL = "SELECT [Product Name], SUM(([Gender]='Male')*(PAge<1)), SUM(([Gender]='Female')*(PAge<1)), " & _
    "SUM(([Gender]='Male')*(PAge Between 1 And 4)), SUM(([Gender]='Female')*(PAge Between 1 And 4)), " & _
    "SUM(([Gender]='Male')*(PAge Between 5 And 9)), SUM(([Gender]='Female')*(PAge Between 5 And 9)), " & _
    "SUM(([Gender]='Male')*(PAge Between 10 And 14)), SUM(([Gender]='Female')*(PAge Between 10 And 14)), " & _
    "SUM(([Gender]='Male')*(PAge Between 15 And 17)), SUM(([Gender]='Female')*(PAge Between 15 And 17)), " & _
    "SUM(([Gender]='Male')*(PAge Between 18 And 24)), SUM(([Gender]='Female')*(PAge Between 18 And 24)), " & _
    "SUM(([Gender]='Male')*(PAge Between 25 And 34)), SUM(([Gender]='Female')*(PAge Between 25 And 34)), " & _
    "SUM(([Gender]='Male')*(PAge Between 35 And 49)), SUM(([Gender]='Female')*(PAge Between 35 And 49)), " & _
    "SUM(([Gender]='Male')*(PAge Between 50 And 64)), SUM(([Gender]='Female')*(PAge Between 50 And 64)), " & _
    "SUM(([Gender]='Male')*(PAge>=65)), SUM(([Gender]='Female')*(PAge>=65)) " & _
    "FROM Ptable WHERE [Product Name] IN (select [Product Result] from Criteria) AND Format([Sale Date],'mmmm')='" & _
    ws.Range("O1").Value & "' AND YEAR([Sale Date])=" & ws.Range("N1").Value & " GROUP BY [Product Name]"

[adam]

Thankyou so much Hans. It worked very well.

[adam]

I'm using the following SQL statement, to sum up, the total text "Discount" from the column "Type". The statement works fine but it gives negative results. Let's say if the total mangoes with a discount are 9, the result is -9. How could I overcome this?

Code: Select all

 strSQL = "SELECT [Product Name], SUM([Type]='Discount') " & _
    ""FROM Ptable WHERE [Product Name] IN (select [Product Result] from Criteria) AND Format([Sale Date],'mmmm')='" & _
    ws.Range("O1").Value & "' AND YEAR([Sale Date])=" & ws.Range("N1").Value & " GROUP BY [Product Name]"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    ws.Range("D5").CopyFromRecordset rst
    rst.Close

[HansV]

[Type]='Discount' returns True if Type equals 'Discount, and False otherwise.
In calculations, True is equivalent to -1 and False is equivalent to 0.

So in SUM([Type]='Discount'), each record for which Type equals 'Discount' contributes -1 to the sum. If there are 9 such records, the sum is -9.

Solution: use -SUM([Type]='Discount')

In my previous reply that was not necessary since we multiplied two such expressions, and -1*-1 = +1.

[adam]

Thankyou so much Hans. It worked well.