What's wrong with this SQL?

F

Frustrated in AL

Why does the SQL below return accurate data for the COUNT portion for
specific date range, but for the percentage portion, it divides the accurate
COUNT by the total from the entire table instead of the total for the date
range?

I have tried various suggestions, but cannot understand what is wrong. Can
someone explain it to me iin beginner terms, please? Thank you so much for
your time and patience.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date
Worked]) AS [% of All Appeals], Sum([Resolved by Hospice Date Worked].[Sales
Amount]) AS [Cost of Appeals], Sum([Sales Amount])/(SELECT SUM ([Sales
Amount]) From [Resolved by Hospice Date Worked]) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]))
GROUP BY [Resolved by Hospice Date Worked].Responsibility;

I have the properties set for percentage so I do not need to multiply by 100
as I have seen done on previous suggestions.
 
F

Frustrated in AL

Thank you for taking a look at this. I must admit I am still confused
because I only see one WHERE clause. And here is where I really show my
newness, what is considered to be the main query and what are the subqueries?

Are the COUNT queries the main queries - because they are working within the
date parameters? Meaning the percentage queries are the subqueries - becasue
they are taking data from the entire table instead of from the date range?

How do I have the subqueries use the same WHERE clause?

Marshall Barton said:
Frustrated said:
Why does the SQL below return accurate data for the COUNT portion for
specific date range, but for the percentage portion, it divides the accurate
COUNT by the total from the entire table instead of the total for the date
range?

I have tried various suggestions, but cannot understand what is wrong. Can
someone explain it to me iin beginner terms, please? Thank you so much for
your time and patience.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date
Worked]) AS [% of All Appeals], Sum([Resolved by Hospice Date Worked].[Sales
Amount]) AS [Cost of Appeals], Sum([Sales Amount])/(SELECT SUM ([Sales
Amount]) From [Resolved by Hospice Date Worked]) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]))
GROUP BY [Resolved by Hospice Date Worked].Responsibility;
Click to expand...


The subqueries need to use the same Where clause as the main
query.
Click to expand...
 
M

Marshall Barton

Frustrated said:
Why does the SQL below return accurate data for the COUNT portion for
specific date range, but for the percentage portion, it divides the accurate
COUNT by the total from the entire table instead of the total for the date
range?

I have tried various suggestions, but cannot understand what is wrong. Can
someone explain it to me iin beginner terms, please? Thank you so much for
your time and patience.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date
Worked]) AS [% of All Appeals], Sum([Resolved by Hospice Date Worked].[Sales
Amount]) AS [Cost of Appeals], Sum([Sales Amount])/(SELECT SUM ([Sales
Amount]) From [Resolved by Hospice Date Worked]) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]))
GROUP BY [Resolved by Hospice Date Worked].Responsibility;
Click to expand...


The subqueries need to use the same Where clause as the main
query.
 
F

Frustrated in AL

Thank you so much. I will have to review what is different in this Monday to
learn from it but it seems to have done the trick.

Thanks again.

Marshall Barton said:
The subqueries are the ones used in your calculated fields.

PARAMETERS [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT T.Responsibility,
Count(*) AS [Number of Appeals],
Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals],
Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] As T
WHERE T.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
GROUP BY T.Responsibility
--
Marsh
MVP [MS Access]

Thank you for taking a look at this. I must admit I am still confused
because I only see one WHERE clause. And here is where I really show my
newness, what is considered to be the main query and what are the subqueries?

Are the COUNT queries the main queries - because they are working within the
date parameters? Meaning the percentage queries are the subqueries - becasue
they are taking data from the entire table instead of from the date range?

How do I have the subqueries use the same WHERE clause?

Frustrated in AL wrote:
Why does the SQL below return accurate data for the COUNT portion for
specific date range, but for the percentage portion, it divides the accurate
COUNT by the total from the entire table instead of the total for the date
range?

I have tried various suggestions, but cannot understand what is wrong. Can
someone explain it to me iin beginner terms, please? Thank you so much for
your time and patience.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date
Worked]) AS [% of All Appeals], Sum([Resolved by Hospice Date Worked].[Sales
Amount]) AS [Cost of Appeals], Sum([Sales Amount])/(SELECT SUM ([Sales
Amount]) From [Resolved by Hospice Date Worked]) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]))
GROUP BY [Resolved by Hospice Date Worked].Responsibility;
Click to expand...
Marshall Barton said:
The subqueries need to use the same Where clause as the main
query.
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

The subqueries are the ones used in your calculated fields.

PARAMETERS [Enter Start Date] DateTime,
[Enter End Date] DateTime;
SELECT T.Responsibility,
Count(*) AS [Number of Appeals],
Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals],
Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] As T
WHERE T.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
GROUP BY T.Responsibility
--
Marsh
MVP [MS Access]

Thank you for taking a look at this. I must admit I am still confused
because I only see one WHERE clause. And here is where I really show my
newness, what is considered to be the main query and what are the subqueries?

Are the COUNT queries the main queries - because they are working within the
date parameters? Meaning the percentage queries are the subqueries - becasue
they are taking data from the entire table instead of from the date range?

How do I have the subqueries use the same WHERE clause?

Frustrated said:
Why does the SQL below return accurate data for the COUNT portion for
specific date range, but for the percentage portion, it divides the accurate
COUNT by the total from the entire table instead of the total for the date
range?

I have tried various suggestions, but cannot understand what is wrong. Can
someone explain it to me iin beginner terms, please? Thank you so much for
your time and patience.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date
Worked]) AS [% of All Appeals], Sum([Resolved by Hospice Date Worked].[Sales
Amount]) AS [Cost of Appeals], Sum([Sales Amount])/(SELECT SUM ([Sales
Amount]) From [Resolved by Hospice Date Worked]) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]))
GROUP BY [Resolved by Hospice Date Worked].Responsibility;
Click to expand...
Click to expand...
Marshall Barton said:
The subqueries need to use the same Where clause as the main
query.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Parameters not working consistently 4
A different Date parameter question 3
Adding a parameter to an existing query 8
Getting dates of parameters to appear in the query/report 5
Need results without prompts 3
Subtraction using 3 fields 1
Error message on Where statement 6
Crosstab Query Troubles Again 5

Top