Sql Statement to filter range.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Then it is not suitable for your criteria. It should be a number field.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Ok. I'll change it to a number field.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

The first SELECT should be TRANSFORM (you had that in the previous version). You now have two SELECTs in the SQL.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

Thanks so much for the help Hans.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

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".

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

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]?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

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.

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]"
The Product Name comes from another table.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

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.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

HansV wrote:
19 Apr 2022, 11:47
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.
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]"
if so how may I proceed?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

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!
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

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.

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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

That would be a crosstab query again, with Gender in the PIVOT clause instead of in the WHERE clause.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

As with your suggestion, the following modification does what I want.

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
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.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

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?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

HansV wrote:
20 Apr 2022, 17:47
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?
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

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

How many age categories do you have, and what are they? Is PAge the age field?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

PAge, Product Name ( that is collected from criteria as product result) and Gender.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sql Statement to filter range.

Post by HansV »

Thanks. I repeat my question: "How many age categories do you have, and what are they?"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Sql Statement to filter range.

Post by adam »

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.
Best Regards,
Adam