Multiple Queries in one report

[Guest]

Is there a way to use multiple Queries for one report? I tried to use 3
seperate queries from the same table and received an error message that
stated I could not use tables and queries.

I finally used in query for the report and sub-reports for the other 2
queries. Is this the only way?

Frustrated in AL

 

[John W. Vinson]

Is there a way to use multiple Queries for one report? I tried to use 3
seperate queries from the same table and received an error message that
stated I could not use tables and queries.

I finally used in query for the report and sub-reports for the other 2
queries. Is this the only way?

Frustrated in AL

Not necessarily. It depends completely on the nature of the queries
and of the report, which of course we cannot see.

Could you describe these queries? Do they all return the same number
of fields of matching datatypes? Do you want the report to show the
results separately, or as one continuous series of records?

John W. Vinson [MVP]

 

[Guest]

It is a research based on appeals submitted for credits (actual and
monetary), actual credits given by 5 responsible groups(actual and monetary),
and percentages of same.

 

[John W. Vinson]

It is a research based on appeals submitted for credits (actual and
monetary), actual credits given by 5 responsible groups(actual and monetary),
and percentages of same.

Fine, that describes what you're doing from a business standpoint.

It doesn't help me understand either your table structure or the
nature of the queries that you're using, or the appearance of the
report.

Could you please post the SQL view of two or three of these queries,
and answer my question about whether you want to see the results in
five separate "blocks" on the report, or as one continuous stream of
records?

John W. Vinson [MVP]

 

[Guest]

My apologies. I am fairly new at Access and on line assistance. a did learn
what the SQL View is on Friday so that will be one less frustration for you.

Here is the SQL for the break down of the 5 groups:
SELECT [Appeals proccessed 1/12/07 - 1/18/07].Responsibilty, Count([Appeals
proccessed 1/12/07 - 1/18/07].Responsibilty) AS CountOfResponsibilty,
Sum([Appeals proccessed 1/12/07 - 1/18/07].[Sales Amount]) AS [Total Appeal
In Dollars]
FROM [Appeals proccessed 1/12/07 - 1/18/07]
GROUP BY [Appeals proccessed 1/12/07 - 1/18/07].Responsibilty;

Here is the SQL for the percentage of the above break down:
SELECT [Appeal specifics]!Responsibilty 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 [Appeal specifics], Totals;

And this is the SQL for the over all totals:
SELECT Count([Appeals proccessed 1/12/07 - 1/18/07]!Responsibilty) AS [Total
Appeals], Sum([Appeals proccessed 1/12/07 - 1/18/07]![Sales Amount]) AS
[Total Appealed Money], Sum([Appeals proccessed 1/12/07 - 1/18/07]![Credit
Amount]) AS [Total Credits to Hospice], Sum([Appeals proccessed 1/12/07 -
1/18/07]![Pharmacy Credit]) AS [Total Pharmacy Credits]
FROM [Appeals proccessed 1/12/07 - 1/18/07];

As for the Report, I would like it in three blocks.

Thank you again for your help with this issue.

 

[John W. Vinson]

My apologies. I am fairly new at Access and on line assistance. a did learn
what the SQL View is on Friday so that will be one less frustration for you.

Here is the SQL for the break down of the 5 groups:
SELECT [Appeals proccessed 1/12/07 - 1/18/07].Responsibilty, Count([Appeals
proccessed 1/12/07 - 1/18/07].Responsibilty) AS CountOfResponsibilty,
Sum([Appeals proccessed 1/12/07 - 1/18/07].[Sales Amount]) AS [Total Appeal
In Dollars]
FROM [Appeals proccessed 1/12/07 - 1/18/07]
GROUP BY [Appeals proccessed 1/12/07 - 1/18/07].Responsibilty;

Here is the SQL for the percentage of the above break down:
SELECT [Appeal specifics]!Responsibilty 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 [Appeal specifics], Totals;

And this is the SQL for the over all totals:
SELECT Count([Appeals proccessed 1/12/07 - 1/18/07]!Responsibilty) AS [Total
Appeals], Sum([Appeals proccessed 1/12/07 - 1/18/07]![Sales Amount]) AS
[Total Appealed Money], Sum([Appeals proccessed 1/12/07 - 1/18/07]![Credit
Amount]) AS [Total Credits to Hospice], Sum([Appeals proccessed 1/12/07 -
1/18/07]![Pharmacy Credit]) AS [Total Pharmacy Credits]
FROM [Appeals proccessed 1/12/07 - 1/18/07];

As for the Report, I would like it in three blocks.

Since the three queries have different datatypes (counts vs
percentages vs dollars), you won't be able to use a UNION query; I'd
really just use three subreports. That will be easier than coming up
with a complicated unitary query that you'ld just have to break up
anyway.

John W. Vinson [MVP]

 

[Pat Hartman \(MVP\)]

I would also like to make an unsolicited comment. Naming objects with data
values indicates a certain lack of normalization in the structure of your
application. The name of the appeals processed query should probably be
"qryAppealsProcessed" and it should take two parameters so you can specify
the start and end of the date range. The path you're taking would mean a
separately named and saved query for each instance of a date range.

Also, avoid spaces and special characters such as /, #, -, etc. in your
object names.

My apologies. I am fairly new at Access and on line assistance. a did
learn
what the SQL View is on Friday so that will be one less frustration for
you.

Here is the SQL for the break down of the 5 groups:
SELECT [Appeals proccessed 1/12/07 - 1/18/07].Responsibilty,
Count([Appeals
proccessed 1/12/07 - 1/18/07].Responsibilty) AS CountOfResponsibilty,
Sum([Appeals proccessed 1/12/07 - 1/18/07].[Sales Amount]) AS [Total
Appeal
In Dollars]
FROM [Appeals proccessed 1/12/07 - 1/18/07]
GROUP BY [Appeals proccessed 1/12/07 - 1/18/07].Responsibilty;

Here is the SQL for the percentage of the above break down:
SELECT [Appeal specifics]!Responsibilty 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 [Appeal specifics], Totals;

And this is the SQL for the over all totals:
SELECT Count([Appeals proccessed 1/12/07 - 1/18/07]!Responsibilty) AS
[Total
Appeals], Sum([Appeals proccessed 1/12/07 - 1/18/07]![Sales Amount]) AS
[Total Appealed Money], Sum([Appeals proccessed 1/12/07 - 1/18/07]![Credit
Amount]) AS [Total Credits to Hospice], Sum([Appeals proccessed 1/12/07 -
1/18/07]![Pharmacy Credit]) AS [Total Pharmacy Credits]
FROM [Appeals proccessed 1/12/07 - 1/18/07];

As for the Report, I would like it in three blocks.

Thank you again for your help with this issue.

Fine, that describes what you're doing from a business standpoint.

It doesn't help me understand either your table structure or the
nature of the queries that you're using, or the appearance of the
report.

Could you please post the SQL view of two or three of these queries,
and answer my question about whether you want to see the results in
five separate "blocks" on the report, or as one continuous stream of
records?

John W. Vinson [MVP]