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?
Sql Statement to filter range.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
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.
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.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
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]"
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
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
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
[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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07