reports from queries with partial data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm producing reports from queries that sometimes return data from only some
of the fields (the others are null or empty) -- when I run the report it
gives me an error message that it doesn't recognize such and such a variable
(that's not produced any data from the query) and won't run. How can I change
that so that the report will run regardless?

Thanks,

Meredith
 
Meredith

Could you provide a bit more explanation? It's a little tough to offer
specific suggestions without specific description.

For example, can you post the SQL statement of your query?

Are you handling the potential nulls in your query? (see Access HELP for
Nz() function)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
I haven't addressed nulls in the query because it's been producing results
that are fine. Ex: A clinician has data to report for only 1 of a possible 4
quarters, so the query only returns data for that quarter. Other clinicians
might have data for all 4 quarters, and the query returns that data
correctly, as well. The report, however, doesn't seem to know what to do for
Clinician #1 because it gives the message that variables for the other
quarters aren't recognized ...
 
I checked out the Nz function -- where would that be inserted in a query? I
tried to put it as criteria in the Value row of a crosstab query but there's
another criteria -- making that an aggregate, which Access won't do. Is there
a better place to deal with potential null values? The report itself?

Thanks,

Meredith
 
TRANSFORM Avg([Subcategory Ratings by Clinician].[Avg Of Rating Assigned]) AS
[AvgOfAvg Of Rating Assigned]
SELECT [Subcategory Ratings by Clinician].[Variable Name]
FROM [Subcategory Ratings by Clinician]
WHERE ((([Subcategory Ratings by Clinician].[Date Rate By Quarter])>"Q1
2005") AND (([Subcategory Ratings by Clinician].[Clinicians Rated]) Like
"Agui*"))
GROUP BY [Subcategory Ratings by Clinician].[Variable Name]
PIVOT [Subcategory Ratings by Clinician].[Date Rate By Quarter];
 
Meredith

I stopped after reading the first line of your SQL. I may be
misinterpreting your naming convention, but it looks like you are taking an
average of averages.

From a statistical accuracy standpoint, this is the wrong thing to do.
Here's why -- if one average is based on a sample of size 3 and a second
average is based on a sample of size 3000, taking an average of averages
leaves out the fact one of the samples is a thousand times larger than the
other.

As for the Nz(), any time you are using the value in a field as part of a
calculation, any "null" value in any row will break the calculation. You
can either use Nz() to convert a null to some other value before
calculating, or you can elect to leave out rows that have nulls in any
critical fields.

I'm back to 0 again ... without an idea of what you are measuring, how you
want to aggregate, and how you intend to display results, specific
suggestions aren't appropriate.

I will point out that you don't have to make your query "display" the final
output. You can use a query to return the values you'll need, then use a
report for organizing and printing out the results.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


MeredithS said:
TRANSFORM Avg([Subcategory Ratings by Clinician].[Avg Of Rating Assigned]) AS
[AvgOfAvg Of Rating Assigned]
SELECT [Subcategory Ratings by Clinician].[Variable Name]
FROM [Subcategory Ratings by Clinician]
WHERE ((([Subcategory Ratings by Clinician].[Date Rate By Quarter])>"Q1
2005") AND (([Subcategory Ratings by Clinician].[Clinicians Rated]) Like
"Agui*"))
GROUP BY [Subcategory Ratings by Clinician].[Variable Name]
PIVOT [Subcategory Ratings by Clinician].[Date Rate By Quarter];


Jeff Boyce said:
Without a look at the SQL you are using, it is still tough to offer anything
specific.

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


query?
I Is
there HELP
for report
it
Click to expand...
Click to expand...
 
Maybe I'm doing something wrong with the query, but I'm trying to find an
average of values by quarters -- when I put that into a crosstab query, it
seems to require that I tell it to average the average which I understand
makes no sense... I'll start over there.

Appreciate your help; I think I'm way off base and had best start from
scratch...

Thanks,

Meredith


Jeff Boyce said:
Meredith

I stopped after reading the first line of your SQL. I may be
misinterpreting your naming convention, but it looks like you are taking an
average of averages.

From a statistical accuracy standpoint, this is the wrong thing to do.
Here's why -- if one average is based on a sample of size 3 and a second
average is based on a sample of size 3000, taking an average of averages
leaves out the fact one of the samples is a thousand times larger than the
other.

As for the Nz(), any time you are using the value in a field as part of a
calculation, any "null" value in any row will break the calculation. You
can either use Nz() to convert a null to some other value before
calculating, or you can elect to leave out rows that have nulls in any
critical fields.

I'm back to 0 again ... without an idea of what you are measuring, how you
want to aggregate, and how you intend to display results, specific
suggestions aren't appropriate.

I will point out that you don't have to make your query "display" the final
output. You can use a query to return the values you'll need, then use a
report for organizing and printing out the results.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


MeredithS said:
TRANSFORM Avg([Subcategory Ratings by Clinician].[Avg Of Rating Assigned]) AS
[AvgOfAvg Of Rating Assigned]
SELECT [Subcategory Ratings by Clinician].[Variable Name]
FROM [Subcategory Ratings by Clinician]
WHERE ((([Subcategory Ratings by Clinician].[Date Rate By Quarter])>"Q1
2005") AND (([Subcategory Ratings by Clinician].[Clinicians Rated]) Like
"Agui*"))
GROUP BY [Subcategory Ratings by Clinician].[Variable Name]
PIVOT [Subcategory Ratings by Clinician].[Date Rate By Quarter];


Jeff Boyce said:
Without a look at the SQL you are using, it is still tough to offer anything
specific.

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I checked out the Nz function -- where would that be inserted in a query?
I
tried to put it as criteria in the Value row of a crosstab query but
there's
another criteria -- making that an aggregate, which Access won't do. Is
there
a better place to deal with potential null values? The report itself?

Thanks,

Meredith


:

Meredith

Could you provide a bit more explanation? It's a little tough to offer
specific suggestions without specific description.

For example, can you post the SQL statement of your query?

Are you handling the potential nulls in your query? (see Access HELP
for
Nz() function)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I'm producing reports from queries that sometimes return data from
only
some
of the fields (the others are null or empty) -- when I run the report
it
gives me an error message that it doesn't recognize such and such a
variable
(that's not produced any data from the query) and won't run. How can I
change
that so that the report will run regardless?

Thanks,

Meredith
Click to expand...
Click to expand...
Click to expand...
 
Back
Top