The query pulls the total customer quotes for each community in a management company and loads my DataGrid:
This query works fine for individual community totals
"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _
" Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _
" (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _
"(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"
At first glance you would think that the following query would return the Grand Total for all communities in the management company:
Current Grand Total Query
"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE
cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session
("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "
But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.
Thanks in advance.The simple answer is to not use DISTINCT. Why is it that you "have" to use that keyword in your grand total query?
Terri|||In my application not using DISTINCT is not an option right now. I plan on changing a lot about this piece, but the president is breathing down my neck so I had to get it working post haste.
The reason for DISTINCT is there are multiple quote lines for a given cust Id. I did end up making it work however. I had to count phone and email in individual arrays and then combine the totals. The problem being that if a cust contacted us by both Email and Phone, the Grand Total query only pick them up based off the last quote entry, of course that is because of using distinct. So it did not match the totals that we report on, which are Phone and Email.
Thanks.
