SQL Division Question

  • Thread starter Thread starter shane
  • Start date Start date
S

shane

I'm looking to find percent compliance with a query. I want to divide the
count of records that have a response time of 3 days or less by the count of
the records from a given date range. Below is the SQL I've tried to use but
does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
 
Shane -

Here is the select statement you need to count the responses and customer
dates.

SELECT Sum(iif([responsesent]-[dateopen]<=3,1,0)) AS CountOfResponseSent,
Count(main.customerdate) AS CountOfCustomerDate,
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))

I have not tested this, just changed the syntax to a correct statement.

To get the percent compliance, you can run a query based on this query to
give you the percent you want.
 
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery where
you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I get the error message "Syntax Error in Union Query".

John Spencer said:
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery where
you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm looking to find percent compliance with a query. I want to divide the
count of records that have a response time of 3 days or less by the count of
the records from a given date range. Below is the SQL I've tried to use but
does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
Click to expand...
.
Click to expand...
 
When I click "OK" the cursor moves to the division sign. Help files and
searches have not been helpful.

shane said:
I get the error message "Syntax Error in Union Query".

John Spencer said:
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery where
you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm looking to find percent compliance with a query. I want to divide the
count of records that have a response time of 3 days or less by the count of
the records from a given date range. Below is the SQL I've tried to use but
does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
Click to expand...
.
Click to expand...
Click to expand...
 
Please post the ENTIRE SQL that you are attempting to use.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
When I click "OK" the cursor moves to the division sign. Help files and
searches have not been helpful.

shane said:
I get the error message "Syntax Error in Union Query".

John Spencer said:
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery where
you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

shane wrote:
I'm looking to find percent compliance with a query. I want to divide the
count of records that have a response time of 3 days or less by the count of
the records from a given date range. Below is the SQL I've tried to use but
does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
.
Click to expand...
Click to expand...
Click to expand...
 
Shane -

You can't divide one query by another. You can only UNION them if you want
to get multiple rows. You can create each query separately, then use a third
query to divide the results of the first two queries.

--
Daryl S


shane said:
When I click "OK" the cursor moves to the division sign. Help files and
searches have not been helpful.

shane said:
I get the error message "Syntax Error in Union Query".

John Spencer said:
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery where
you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

shane wrote:
I'm looking to find percent compliance with a query. I want to divide the
count of records that have a response time of 3 days or less by the count of
the records from a given date range. Below is the SQL I've tried to use but
does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
.
Click to expand...
Click to expand...
Click to expand...
 
I need to verify but believe this is working.

SELECT DCount("[responsesent]","[main]","[responsesent]-[dateopen]<='3'
")/Count([customerdate]) AS percentcompliance
FROM main
WHERE ((main!customerdate Between forms!poly6main!sdate And
forms!poly6main!edate))
HAVING ((main!customerdate Between forms!poly6main!sdate And
forms!poly6main!edate));

shane said:
I get the error message "Syntax Error in Union Query".

John Spencer said:
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery where
you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm looking to find percent compliance with a query. I want to divide the
count of records that have a response time of 3 days or less by the count of
the records from a given date range. Below is the SQL I've tried to use but
does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
Click to expand...
.
Click to expand...
Click to expand...
 
ã©ã‚“ãªã€è³ªå•ã§ã‚‚ã€æ‰¿ã‚Šã¾ã™ãŒã€ç§ã®ã€å¿ƒã‚’ã€ãƒœãƒ­ãƒœãƒ­ã«ã¯ã€ã—ãªã„ã§ã€ä¸‹ã•ã„ã­ï½žï½žï½žâ˜†


ã“れ以上ã€è‹¦ã—ã¿ãŸãã¯ã€ç„¡ã„ã§ã™ã‹ã‚‰ãƒ»ãƒ»ãƒ»ãƒ»ãƒ»ãƒ»ãƒ»ãƒ»ï¼ï¼

ç§ã¯ã€æ—¥æœ¬äººã§ã™ã‚ˆï½žï½žâ˜†

中国ã€éŸ“国ã€ã‚¸ãƒ£ãƒžã‚¤ã‚«ã€ãƒ»ãƒ»ãƒ»å‹é”ã„ã¾ã™ã‚ˆï¼ï¼ï¼

ã ã£ã¦ã€æ¤¿ã¡ã‚ƒã‚“ã§ã™ã‚ˆï½žâ™ª

John Spencer said:
You need to return one row per subquery so you need to remove the GROUP BY
clause in both subqueries. You also have a typo in the second subquery
where you refer to Main.Are instasted of Main.Area

Try the following

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE [responsesent]-[dateopen]<=3
AND main.class="Complaint"
AND main.area)="Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate]) /
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
WHERE main.class="Complaint"
AND main.area= "Poly 6"
AND main.customerdate Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm looking to find percent compliance with a query. I want to divide
the count of records that have a response time of 3 days or less by the
count of the records from a given date range. Below is the SQL I've
tried to use but does not work. Thanks in advanced.

(SELECT Count(main.responsesent) AS CountOfresponsesent
FROM main
WHERE (([responsesent]-[dateopen]<=3))
GROUP BY main.class, main.area, main.customerdate
HAVING (((main.class)="Complaint") AND ((main.area)="Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])))/
(SELECT Count(main.customerdate) AS CountOfcustomerdate
FROM main
GROUP BY main.class, main.area, main.customerdate
Having (((main.class)="Complaint") AND (((main.are)= "Poly 6") AND
((main.customerdate) Between [forms]![poly6main]![sdate] And
[forms]![poly6main]![edate])));
Click to expand...
Click to expand...
 
Back
Top