Need results without prompts

G

Guest

I have a table that lists appeals based on responsibility (5 different
options). I am trying to get the query to list number of appeals, percentage
of appeal, cost of appeals and percentage of cost of appeals bassed on the
responsibility options. I have canabalized a previous SQL but I have done
something wrong. I am prompted 5 times for parameters and it does not
seperate or calculate properly.

Here is the SQL: SELECT ([Appeals specific]![Resolved by Hospice Date
Worked]![Responsibility]) AS Expr1, ([Appeal
specifics]!CountOfResponsibilty/Totals![Total Appeals]) AS [Percentage of
Appeals], ([Appeal specifics]![Total Appeal In Dollars]/Totals![Total
Appealed Money]) AS [Percentage of Appealed Money]
FROM [Resolved by Hospice Date Worked], [Total Appeals by Date worked];

I appreciate any help that you can provide. Thank you in advance.
 
G

Guest

Its difficult to work out from your post what the table is called and what
the column (field) names in your table are, so lets assume the table is
called Appeals, the responsibilities column is called Responsibility and the
Cost column is called Cost.

You need to group the query by the Responsibility column so that you can
aggregate the values per responsibility. To get the percentages you need to
divide by the total number or cost of all rows in the table, which you can do
with subqueries, so the query would be something like this:

SELECT [Responsibility],
COUNT(*) AS [Number of Appeals],
COUNT(*)/(SELECT COUNT(*)
FROM [Appeals]) * 100
AS [Percentage of All Appeals],
SUM([Cost]) AS [Cost of Appeals],
SUM([Cost])/(SELECT SUM([Cost])
FROM [Appeals]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Appeals]
GROUP BY [Responsibility];

The percentages will be shown as such, not as fractional values, by virtue
of multiplying the result of the divisions by 100.

Ken Sheridan
Stafford, England
 
G

Guest

Following the SQL given below, with the corrections that the table name is
[Resolved by Hospice Date Worked] and the Cost Column is Called [Credit
Amount], I have the following SQL that gives me the error "Syntax error
(missing Operator) in Querey expression 'SUM ([Credit Amount]
AS [Cost of Appeals],
Sum ([Credit Amount]/ SELECT SUM ([Credit Amount]) From [Resolved by Hospice
Date Worked]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Responsibility];'.

Here is the entire SQL:
SELECT[Responsibility],
COUNT(*) AS [Number of Appeals],
COUNT (*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date Worked]) * 100
As [Percentage of All Appeals],
SUM ([Credit Amount]
AS [Cost of Appeals],
Sum ([Credit Amount]/ SELECT SUM ([Credit Amount]) From [Resolved by Hospice
Date Worked]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Responsibility];

Ken Sheridan said:
Its difficult to work out from your post what the table is called and what
the column (field) names in your table are, so lets assume the table is
called Appeals, the responsibilities column is called Responsibility and the
Cost column is called Cost.

You need to group the query by the Responsibility column so that you can
aggregate the values per responsibility. To get the percentages you need to
divide by the total number or cost of all rows in the table, which you can do
with subqueries, so the query would be something like this:

SELECT [Responsibility],
COUNT(*) AS [Number of Appeals],
COUNT(*)/(SELECT COUNT(*)
FROM [Appeals]) * 100
AS [Percentage of All Appeals],
SUM([Cost]) AS [Cost of Appeals],
SUM([Cost])/(SELECT SUM([Cost])
FROM [Appeals]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Appeals]
GROUP BY [Responsibility];

The percentages will be shown as such, not as fractional values, by virtue
of multiplying the result of the divisions by 100.

Ken Sheridan
Stafford, England

Frustrated in AL said:
I have a table that lists appeals based on responsibility (5 different
options). I am trying to get the query to list number of appeals, percentage
of appeal, cost of appeals and percentage of cost of appeals bassed on the
responsibility options. I have canabalized a previous SQL but I have done
something wrong. I am prompted 5 times for parameters and it does not
seperate or calculate properly.

Here is the SQL: SELECT ([Appeals specific]![Resolved by Hospice Date
Worked]![Responsibility]) AS Expr1, ([Appeal
specifics]!CountOfResponsibilty/Totals![Total Appeals]) AS [Percentage of
Appeals], ([Appeal specifics]![Total Appeal In Dollars]/Totals![Total
Appealed Money]) AS [Percentage of Appealed Money]
FROM [Resolved by Hospice Date Worked], [Total Appeals by Date worked];

I appreciate any help that you can provide. Thank you in advance.
Click to expand...
Click to expand...
 
G

Guest

Ken,

By adding a closing parenthesis, I have reduced my error message to 'Syntax
error
(missing Operator) in Querey expression 'SUM ([Credit Amount]
AS [Cost of Appeals],'. As you might guess, I put the closing parenthasis
at the end of this statement.

Can you help me?

Frustrated in AL said:
Following the SQL given below, with the corrections that the table name is
[Resolved by Hospice Date Worked] and the Cost Column is Called [Credit
Amount], I have the following SQL that gives me the error "Syntax error
(missing Operator) in Querey expression 'SUM ([Credit Amount]
AS [Cost of Appeals],
Sum ([Credit Amount]/ SELECT SUM ([Credit Amount]) From [Resolved by Hospice
Date Worked]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Responsibility];'.

Here is the entire SQL:
SELECT[Responsibility],
COUNT(*) AS [Number of Appeals],
COUNT (*)/(SELECT COUNT(*) FROM [Resolved by Hospice Date Worked]) * 100
As [Percentage of All Appeals],
SUM ([Credit Amount]
AS [Cost of Appeals],
Sum ([Credit Amount]/ SELECT SUM ([Credit Amount]) From [Resolved by Hospice
Date Worked]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Responsibility];

Ken Sheridan said:
Its difficult to work out from your post what the table is called and what
the column (field) names in your table are, so lets assume the table is
called Appeals, the responsibilities column is called Responsibility and the
Cost column is called Cost.

You need to group the query by the Responsibility column so that you can
aggregate the values per responsibility. To get the percentages you need to
divide by the total number or cost of all rows in the table, which you can do
with subqueries, so the query would be something like this:

SELECT [Responsibility],
COUNT(*) AS [Number of Appeals],
COUNT(*)/(SELECT COUNT(*)
FROM [Appeals]) * 100
AS [Percentage of All Appeals],
SUM([Cost]) AS [Cost of Appeals],
SUM([Cost])/(SELECT SUM([Cost])
FROM [Appeals]) * 100
AS [Percentage of Cost of All Appeals]
FROM [Appeals]
GROUP BY [Responsibility];

The percentages will be shown as such, not as fractional values, by virtue
of multiplying the result of the divisions by 100.

Ken Sheridan
Stafford, England

Frustrated in AL said:
I have a table that lists appeals based on responsibility (5 different
options). I am trying to get the query to list number of appeals, percentage
of appeal, cost of appeals and percentage of cost of appeals bassed on the
responsibility options. I have canabalized a previous SQL but I have done
something wrong. I am prompted 5 times for parameters and it does not
seperate or calculate properly.

Here is the SQL: SELECT ([Appeals specific]![Resolved by Hospice Date
Worked]![Responsibility]) AS Expr1, ([Appeal
specifics]!CountOfResponsibilty/Totals![Total Appeals]) AS [Percentage of
Appeals], ([Appeal specifics]![Total Appeal In Dollars]/Totals![Total
Appealed Money]) AS [Percentage of Appealed Money]
FROM [Resolved by Hospice Date Worked], [Total Appeals by Date worked];

I appreciate any help that you can provide. Thank you in advance.
Click to expand...
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
What's wrong with this SQL? 4
A different Date parameter question 3
Adding to a query for % of count, $$ and % of $$ 1
Adding a parameter to an existing query 8
Getting dates of parameters to appear in the query/report 5
Subtraction using 3 fields 1
A quick query on calculating percentages 1

Top