Sql Statement to filter range.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
Then it is not suitable for your criteria. It should be a number field.
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.
Here's how I've modified the code. But I'm getting an error message saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect". What am I doing wrong in here?
Code: Select all
strSQL = "SELECT COUNT([Gender]) AS total SELECT [Gender] FROM tblmytable WHERE [Gender] = 'Female' AND [PAge] <5 AND Format([CDate],'mmmm')='" & _
ws.Range("AE9").Value & "'AND YEAR([CDate])=" & ws.Range("AI9").Value & _
" GROUP BY [Gender] PIVOT Day([CDate]) In " & _
"(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
ws.Range("D23").CopyFromRecordset rst
rst.Close
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
The first SELECT should be TRANSFORM (you had that in the previous version). You now have two SELECTs in the SQL.
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.
The following query sum ups the Product Name of different dates of a month in different cells instead of "D5"
For example if the product name is from date 4/18/2022 it will sum up in "D5". If the product name from 4/19/2022 it will sum up in cell F6"
How could I make the code to sum up all the product name for the particular month in cell "D5".
For example if the product name is from date 4/18/2022 it will sum up in "D5". If the product name from 4/19/2022 it will sum up in cell F6"
How could I make the code to sum up all the product name for the particular month in cell "D5".
Code: Select all
strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
"FROM Ptable WHERE [Product Name] AND Format([Sale Date],'mmmm')='" & _
ws.Range("O1").Value & "' AND YEAR([Sale Date])=" & ws.Range("N1").Value & " GROUP BY [Product Name] PIVOT [Sale Date]"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
ws.Range("D5").CopyFromRecordset rst
rst.Close
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
I don't understand your question. Could you try to explain it more clearly?
Also, I don't understand the part ... WHERE [Product Name] AND ...
Shouldn't there be some condition on [Product Name]?
Also, I don't understand the part ... WHERE [Product Name] AND ...
Shouldn't there be some condition on [Product Name]?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
The code counts all the unique product names for the month and year mention in the date and month cells. And pastes the results in cell D5 of the active sheet.
if all the products are from the same date, it counts and pastes the product name and total amount in cell D5.
Where product name gets copied to D5 and the total for that product gets copied to D6 if its the same date.
But if its different dates of the same month and year, the code copies the product name to D5, and the count of all the product names in date 1 to D6 and date 2 to D7.
I want the code to copy the count of all the dates to D6. I hope I've made my question clear.
The code counts all the unique product names for the month and year mention in the date and month cells. And pastes the results in cell D5 of the active sheet.
if all the products are from the same date, it counts and pastes the product name and total amount in cell D5.
Where product name gets copied to D5 and the total for that product gets copied to D6 if its the same date.
But if its different dates of the same month and year, the code copies the product name to D5, and the count of all the product names in date 1 to D6 and date 2 to D7.
I want the code to copy the count of all the dates to D6. I hope I've made my question clear.
The Product Name comes from another table.
if all the products are from the same date, it counts and pastes the product name and total amount in cell D5.
Where product name gets copied to D5 and the total for that product gets copied to D6 if its the same date.
But if its different dates of the same month and year, the code copies the product name to D5, and the count of all the product names in date 1 to D6 and date 2 to D7.
I want the code to copy the count of all the dates to D6. I hope I've made my question clear.
The code counts all the unique product names for the month and year mention in the date and month cells. And pastes the results in cell D5 of the active sheet.
if all the products are from the same date, it counts and pastes the product name and total amount in cell D5.
Where product name gets copied to D5 and the total for that product gets copied to D6 if its the same date.
But if its different dates of the same month and year, the code copies the product name to D5, and the count of all the product names in date 1 to D6 and date 2 to D7.
I want the code to copy the count of all the dates to D6. I hope I've made my question clear.
Code: Select all
'strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
'"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] PIVOT [Sale Date]"
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
If I read the SQL correctly, I would expect product names in D5, D6, D7 etc., and the counts in the cells to the right of that.
If you don't want a count for each date, do not use a crosstab query but a simple totals query.
If you don't want a count for each date, do not use a crosstab query but a simple totals query.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
Yes the product names will be in D5, D6, D7 etc., and the counts in the cells to the right of that.
I have other criteria's in the query as following
Code: Select all
'strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
'"FROM Ptable WHERE [Gender] = 'Male' AND [PAge] >21 AND [Product Name] IN (select [Product Result] from tblCriteria) AND Format([Sale Date],'mmmm')='" & _
'ws.Range("O1").Value & "' AND YEAR([Sale Date])=" & ws.Range("N1").Value & " GROUP BY [Product Name] PIVOT [Sale Date]"
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
Don't use TRANSFORM and PIVOT. Just a simple SELECT query that groups by Product Name.
If you can create a complicated crosstab query, surely you can create a much simpler totals query!
If you can create a complicated crosstab query, surely you can create a much simpler totals query!
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
The following edition of code does what I want.
Meaning, it counts the total products by placing the product name in column D starting from D5, D6, D7 and onwards.
The counts of the product for the age and gender are placed in column E starting from E5, E6, E7 and onwards.
However, I need to add another sql statement where it counts all the "female" for the same age group and place the counts from G6,G7 and onwards.
Any help on this would be kindly appreciated.
Meaning, it counts the total products by placing the product name in column D starting from D5, D6, D7 and onwards.
The counts of the product for the age and gender are placed in column E starting from E5, E6, E7 and onwards.
However, I need to add another sql statement where it counts all the "female" for the same age group and place the counts from G6,G7 and onwards.
Any help on this would be kindly appreciated.
Code: Select all
strSQL = "SELECT [Product Name], COUNT([Product Name]) AS total " & _
"FROM Ptable WHERE [Gender] = 'Male' AND [PAge] <1 AND [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: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
That would be a crosstab query again, with Gender in the PIVOT clause instead of in the WHERE clause.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
As with your suggestion, the following modification does what I want.
But it does for one age group. I have a couple of age groups like let's say, [PAge] BETWEEN 15 AND 18, <70, and so on.
How could I further edit the code so that it will count the age groups by age and gender in columns at right?
For example;
Count of product names with age more than 21 will be on cell E5 and F5 where E4 is Male and F5 is Female.
and count of product names with age Between 15 and 18 will be on G5 and H5
and count of product names with age less than 70 will be on I5 and J5
I hope I've made my question clear.
Code: Select all
strSQL = "TRANSFORM count([Product Name]) AS total SELECT [Product Name] " & _
"FROM Ptable WHERE [PAge] >21 AND [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] PIVOT ([Gender]) In " & _
"(Male, Female)"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
ws.Range("D5").CopyFromRecordset rst
rst.Close
How could I further edit the code so that it will count the age groups by age and gender in columns at right?
For example;
Count of product names with age more than 21 will be on cell E5 and F5 where E4 is Male and F5 is Female.
and count of product names with age Between 15 and 18 will be on G5 and H5
and count of product names with age less than 70 will be on I5 and J5
I hope I've made my question clear.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
A crosstab query doesn't work that way. You can pivot on only one field: on gender, or on age group, but not on both.
Is your example realistic, or will you need many more categories?
Is your example realistic, or will you need many more categories?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
Do you mean age categories? if so I have more age categories. or else the only category is the product name, gender and different age categories.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
How many age categories do you have, and what are they? Is PAge the age field?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
PAge, Product Name ( that is collected from criteria as product result) and Gender.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sql Statement to filter range.
Thanks. I repeat my question: "How many age categories do you have, and what are they?"
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Sql Statement to filter range.
Less than 1, between 10 to 15, between 16 to 18 , between 13 to 19, above 65.
PAge is age.
I hope this clarifies your question.
PAge is age.
I hope this clarifies your question.
Best Regards,
Adam
Adam