A different Date parameter question

[Guest]

With much help from this discussion group, I have a query based on a table
that I will continue to add to. I need to be able to query by specific
dates. The dates do not show up anywhere else in the query and I am not sure
how to set up the parameters for the start and end dates. Below is the SQL
and the Field that I need to pull the dates from is [Date Worked on]. Thank
you in advance for your assistance.

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 Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Resolved by Hospice Date Worked].Responsibility;

 

[Marshall Barton]

With much help from this discussion group, I have a query based on a table
that I will continue to add to. I need to be able to query by specific
dates. The dates do not show up anywhere else in the query and I am not sure
how to set up the parameters for the start and end dates. Below is the SQL
and the Field that I need to pull the dates from is [Date Worked on]. Thank
you in advance for your assistance.

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 Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Resolved by Hospice Date Worked].Responsibility;

I am going to use a table aloas because your long table name
adds so much clutter that the query is difficult to read and
edit:

SELECT T.Responsibility, Count(*) AS [Number of Appeals],
Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked]
) 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 [% of Cost of All Appeals]
FROM [Resolved by Hospice Date Worked] As T
WHERE [Date Worked on] Between [Start Date] Ans [End Date]
GROUP BY T.Responsibility

That will prompt you for the start and end date parameters,
which is only a quick and dirty example. You should
probably use a form with text boxes for those values. The
parameters would then look like:
Forms!theform.txtStartDate

You never did say if [Resolved by Hospice Date Worked] is a
table or a query. I can imagine situations where you want
to apply the date criteria to the subqueries as well as the
main query. If you do, then create a query (if it isn't
already one) and put the WHERE clause in that query instead
of the one you posted.

 

[Guest]

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]
WHERE [Resolved by Hospice Date Worked].[Date Worked on]
Between [Enter Start Date] And [Enter End Date])
AS [Cost of Appeals],
Sum([Sales Amount])/
(SELECT SUM ([Sales Amount])
FROM [Resolved by Hospice Date Worked]
WHERE [Resolved by Hospice Date Worked].[Date Worked on]
Between [Enter Start Date] And [Enter End Date])
AS [% of Cost 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;

Note that I didn't test the above SQL and there could be a lot of problems.
1. The last WHERE clause may need to be a HAVING.
2. I assumed that you needed the same date ranges in the subqueries.
3. I assumed that [Date Worked on] is an actual Date/Time field.
4. I assumed that [Date Worked on] only stores the date and not a time. If
it also stores the time, my sql statement will cut off records at midnight on
what you entered on [Enter End Date].
5. I assumed that the parameters will carry over to the subqueires. If the
boss wasn't looking over my shoulder, I'd test that.

Lots of assumptions on my part.

 

[Guest]

This did the trick with very little tweaking. Thank you so much for your
assistance.

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]
WHERE [Resolved by Hospice Date Worked].[Date Worked on]
Between [Enter Start Date] And [Enter End Date])
AS [Cost of Appeals],
Sum([Sales Amount])/
(SELECT SUM ([Sales Amount])
FROM [Resolved by Hospice Date Worked]
WHERE [Resolved by Hospice Date Worked].[Date Worked on]
Between [Enter Start Date] And [Enter End Date])
AS [% of Cost 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;

Note that I didn't test the above SQL and there could be a lot of problems.
1. The last WHERE clause may need to be a HAVING.
2. I assumed that you needed the same date ranges in the subqueries.
3. I assumed that [Date Worked on] is an actual Date/Time field.
4. I assumed that [Date Worked on] only stores the date and not a time. If
it also stores the time, my sql statement will cut off records at midnight on
what you entered on [Enter End Date].
5. I assumed that the parameters will carry over to the subqueires. If the
boss wasn't looking over my shoulder, I'd test that.

Lots of assumptions on my part.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

With much help from this discussion group, I have a query based on a table
that I will continue to add to. I need to be able to query by specific
dates. The dates do not show up anywhere else in the query and I am not sure
how to set up the parameters for the start and end dates. Below is the SQL
and the Field that I need to pull the dates from is [Date Worked on]. Thank
you in advance for your assistance.

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 Cost of All Appeals]
FROM [Resolved by Hospice Date Worked]
GROUP BY [Resolved by Hospice Date Worked].Responsibility;