Date Parameters not working consistently

F

Frustrated in AL

I have a query based on a table that will continue to have data added to it.
The query in question is based on date parameters. The actual "Count" is
accurate for number of appeals and cost of appealsbroken down by different
responsibility. The problem is with the percentages. I am not getting the
percentages for the specified date range [count by responsiblitity for date
range/date range total]. Instead, the query is using the totals for the
entire table [count by responsiblitity for date range/total table count].

Can someone please assist me, once again, in understanding where I am going
wrong? I have included the SQL and a query where the date range excludes the
last week in the query, showing correct totals for the time period but
percentatges based on all data on the table.

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;

Resp #Appeals % Appeals Cost of Appeals % of $$ All Appeals
? 1 0.07%
CON 8 0.59% $122.00 0.12%
CSR 84 6.19% $3,205.24 3.19%
HOS 58 4.27% $2,744.20 2.73%
NOC 701 51.66% $32,583.29 32.44%
OTH 72 5.31% $4,501.57 4.48%
 
K

KARL DEWEY

Try using these two queries.
Frustrated_AL ---
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked].Responsibility) AS Appeals,
Sum([Resolved by Hospice Date Worked].[Sales Amount]) AS [Total Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/[Appeals]*100 AS [% of All Appeals], Sum([Resolved by
Hospice Date Worked].[Sales Amount]) AS [Cost of Appeals], Sum(([Sales
Amount])/[Total Appeals]*100) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked], Frustrated_AL
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,
Frustrated_AL.Appeals;

--
KARL DEWEY
Build a little - Test a little


Frustrated in AL said:
I have a query based on a table that will continue to have data added to it.
The query in question is based on date parameters. The actual "Count" is
accurate for number of appeals and cost of appealsbroken down by different
responsibility. The problem is with the percentages. I am not getting the
percentages for the specified date range [count by responsiblitity for date
range/date range total]. Instead, the query is using the totals for the
entire table [count by responsiblitity for date range/total table count].

Can someone please assist me, once again, in understanding where I am going
wrong? I have included the SQL and a query where the date range excludes the
last week in the query, showing correct totals for the time period but
percentatges based on all data on the table.

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;

Resp #Appeals % Appeals Cost of Appeals % of $$ All Appeals
? 1 0.07%
CON 8 0.59% $122.00 0.12%
CSR 84 6.19% $3,205.24 3.19%
HOS 58 4.27% $2,744.20 2.73%
NOC 701 51.66% $32,583.29 32.44%
OTH 72 5.31% $4,501.57 4.48%
Click to expand...
 
F

Frustrated in AL

Karl,

Thank you for your input on this issue I am having. I ran the first query
andfound that it partially duplicates a query that I already have: total
appeals, total amount appealed, pharmacy credits, hospice credits, &
non-credits with the dates included.

On to the second query for the percentages broken down by responsibility.
This query first gave me an error message for "Invalid bracketing of name
'Number of Appeals'." I tried a few different changes, (), "", and then cut
and pasted from my original Percentage query. I was prompted to enter the
start and end date, and then prompted for Appeal and Total Appeals. The
query returned errror messages if I put dates in both parameter sets while
still counting the numbers factually, and if I left the second parameter set
blank, the % fields returned blank.

I must admit that I am very confused as to why my original percentage query,
as shown below, is not giving back the corrrect percentages, but is counting
corrrectlly. I look forward to your insight on what I have done wrong and
how I can correct it.

Thank you again for your time and patience.

KARL DEWEY said:
Try using these two queries.
Frustrated_AL ---
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked].Responsibility) AS Appeals,
Sum([Resolved by Hospice Date Worked].[Sales Amount]) AS [Total Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/[Appeals]*100 AS [% of All Appeals], Sum([Resolved by
Hospice Date Worked].[Sales Amount]) AS [Cost of Appeals], Sum(([Sales
Amount])/[Total Appeals]*100) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked], Frustrated_AL
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,
Frustrated_AL.Appeals;

--
KARL DEWEY
Build a little - Test a little


Frustrated in AL said:
I have a query based on a table that will continue to have data added to it.
The query in question is based on date parameters. The actual "Count" is
accurate for number of appeals and cost of appealsbroken down by different
responsibility. The problem is with the percentages. I am not getting the
percentages for the specified date range [count by responsiblitity for date
range/date range total]. Instead, the query is using the totals for the
entire table [count by responsiblitity for date range/total table count].

Can someone please assist me, once again, in understanding where I am going
wrong? I have included the SQL and a query where the date range excludes the
last week in the query, showing correct totals for the time period but
percentatges based on all data on the table.

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;

Resp #Appeals % Appeals Cost of Appeals % of $$ All Appeals
? 1 0.07%
CON 8 0.59% $122.00 0.12%
CSR 84 6.19% $3,205.24 3.19%
HOS 58 4.27% $2,744.20 2.73%
NOC 701 51.66% $32,583.29 32.44%
OTH 72 5.31% $4,501.57 4.48%
Click to expand...
Click to expand...
 
K

KARL DEWEY

The first query pulls the total number of appeals and the total cost of the
appeals for the requested time period.

That first query is used in the second so as to do the percent calculation.
Your query did not limit the totals to your time period.

The error about bracketing probably stems from the pasting into the post and
coping again to your query. The post sometime adds returns where there are
spaces in table and field names. These have to be edited out to make the
query run.

--
KARL DEWEY
Build a little - Test a little


Frustrated in AL said:
Karl,

Thank you for your input on this issue I am having. I ran the first query
andfound that it partially duplicates a query that I already have: total
appeals, total amount appealed, pharmacy credits, hospice credits, &
non-credits with the dates included.

On to the second query for the percentages broken down by responsibility.
This query first gave me an error message for "Invalid bracketing of name
'Number of Appeals'." I tried a few different changes, (), "", and then cut
and pasted from my original Percentage query. I was prompted to enter the
start and end date, and then prompted for Appeal and Total Appeals. The
query returned errror messages if I put dates in both parameter sets while
still counting the numbers factually, and if I left the second parameter set
blank, the % fields returned blank.

I must admit that I am very confused as to why my original percentage query,
as shown below, is not giving back the corrrect percentages, but is counting
corrrectlly. I look forward to your insight on what I have done wrong and
how I can correct it.

Thank you again for your time and patience.

KARL DEWEY said:
Try using these two queries.
Frustrated_AL ---
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked].Responsibility) AS Appeals,
Sum([Resolved by Hospice Date Worked].[Sales Amount]) AS [Total Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/[Appeals]*100 AS [% of All Appeals], Sum([Resolved by
Hospice Date Worked].[Sales Amount]) AS [Cost of Appeals], Sum(([Sales
Amount])/[Total Appeals]*100) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked], Frustrated_AL
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,
Frustrated_AL.Appeals;

--
KARL DEWEY
Build a little - Test a little


Frustrated in AL said:
I have a query based on a table that will continue to have data added to it.
The query in question is based on date parameters. The actual "Count" is
accurate for number of appeals and cost of appealsbroken down by different
responsibility. The problem is with the percentages. I am not getting the
percentages for the specified date range [count by responsiblitity for date
range/date range total]. Instead, the query is using the totals for the
entire table [count by responsiblitity for date range/total table count].

Can someone please assist me, once again, in understanding where I am going
wrong? I have included the SQL and a query where the date range excludes the
last week in the query, showing correct totals for the time period but
percentatges based on all data on the table.

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;

Resp #Appeals % Appeals Cost of Appeals % of $$ All Appeals
? 1 0.07%
CON 8 0.59% $122.00 0.12%
CSR 84 6.19% $3,205.24 3.19%
HOS 58 4.27% $2,744.20 2.73%
NOC 701 51.66% $32,583.29 32.44%
OTH 72 5.31% $4,501.57 4.48%
Click to expand...
Click to expand...
Click to expand...
 
F

Frustrated in AL

Karl,

This continues to baffle me. I have been working on this all morning, using
your suggestions, making changes and beating my head against my desk.
Nothing is working.

I changed divisional portion to include my query that broke things down by
date and I had 3 sets of parameters to input.

There must be an easier way. I know I'm been terribly dense, but this is
not making sense to my adled mind.

KARL DEWEY said:
The first query pulls the total number of appeals and the total cost of the
appeals for the requested time period.

That first query is used in the second so as to do the percent calculation.
Your query did not limit the totals to your time period.

The error about bracketing probably stems from the pasting into the post and
coping again to your query. The post sometime adds returns where there are
spaces in table and field names. These have to be edited out to make the
query run.

--
KARL DEWEY
Build a little - Test a little


Frustrated in AL said:
Karl,

Thank you for your input on this issue I am having. I ran the first query
andfound that it partially duplicates a query that I already have: total
appeals, total amount appealed, pharmacy credits, hospice credits, &
non-credits with the dates included.

On to the second query for the percentages broken down by responsibility.
This query first gave me an error message for "Invalid bracketing of name
'Number of Appeals'." I tried a few different changes, (), "", and then cut
and pasted from my original Percentage query. I was prompted to enter the
start and end date, and then prompted for Appeal and Total Appeals. The
query returned errror messages if I put dates in both parameter sets while
still counting the numbers factually, and if I left the second parameter set
blank, the % fields returned blank.

I must admit that I am very confused as to why my original percentage query,
as shown below, is not giving back the corrrect percentages, but is counting
corrrectlly. I look forward to your insight on what I have done wrong and
how I can correct it.

Thank you again for your time and patience.

KARL DEWEY said:
Try using these two queries.
Frustrated_AL ---
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Count([Resolved by Hospice Date Worked].Responsibility) AS Appeals,
Sum([Resolved by Hospice Date Worked].[Sales Amount]) AS [Total Appeals]
FROM [Resolved by Hospice Date Worked]
WHERE ((([Resolved by Hospice Date Worked].[Date Worked on]) Between [Enter
Start Date] And [Enter End Date]));

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT [Resolved by Hospice Date Worked].Responsibility, Count(*) AS [Number
of Appeals], Count(*)/[Appeals]*100 AS [% of All Appeals], Sum([Resolved by
Hospice Date Worked].[Sales Amount]) AS [Cost of Appeals], Sum(([Sales
Amount])/[Total Appeals]*100) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked], Frustrated_AL
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,
Frustrated_AL.Appeals;

--
KARL DEWEY
Build a little - Test a little


:

I have a query based on a table that will continue to have data added to it.
The query in question is based on date parameters. The actual "Count" is
accurate for number of appeals and cost of appealsbroken down by different
responsibility. The problem is with the percentages. I am not getting the
percentages for the specified date range [count by responsiblitity for date
range/date range total]. Instead, the query is using the totals for the
entire table [count by responsiblitity for date range/total table count].

Can someone please assist me, once again, in understanding where I am going
wrong? I have included the SQL and a query where the date range excludes the
last week in the query, showing correct totals for the time period but
percentatges based on all data on the table.

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;

Resp #Appeals % Appeals Cost of Appeals % of $$ All Appeals
? 1 0.07%
CON 8 0.59% $122.00 0.12%
CSR 84 6.19% $3,205.24 3.19%
HOS 58 4.27% $2,744.20 2.73%
NOC 701 51.66% $32,583.29 32.44%
OTH 72 5.31% $4,501.57 4.48%
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

A different Date parameter question 3
What's wrong with this SQL? 4
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 with multiple fields 2

Top