Need results without prompts

[Frustrated in AL]

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.

 

[Ken Sheridan]

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]

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];

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

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.

 

[Frustrated in AL]

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?

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];

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

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.