Help needed for my paremeter query! 1

[ychousa]

Hi. Here's my parameter query to get some statistics.

SELECT MAIN.[Distributer Name] AS Distributer, MAIN.State AS State, Count(MAIN.ID) AS Leads, Round(Count(ID)/(select count(*) from MAIN)*100) AS Percentage, MAIN.Date
FROM MAIN
GROUP BY MAIN.[Distributer Name], MAIN.State, MAIN.Date
HAVING (((MAIN.Date) Between [Start Date] And [End Date]))
ORDER BY Count(MAIN.[Distributer Name]);

The result looks something like this:

Distributer State Leads Percentage
Bestbuy CA 2 14%

But actually the percentage should be 100% because there are only 2 results in the term set by start date and end date input.

I think the SQL statement gets the percentage out of the whole record, not from the record by selected term.

What is wrong with my statement?

Thanks for your help in advance.

 

[mpastore]

Your sub-query:

select count(*) from MAIN

Needs to have the same where clause as the having clause of the outter query, it's picking up count of all records as written.

Something like:

select count(*) from MAIN
where (((MAIN.Date) Between [Start Date] And [End Date]))


Mike Pastore

Hats off to (Roy) Harper

 

[ychousa]

Mike, it worked great!

How fool was I not to detect that!

Thanks.