union query for report

  • Thread starter Thread starter jmoore
  • Start date Start date
J

jmoore

Using several fields, I am trying to create a report that will provide a list
the all entries grouped by each field, in a vertical list instead of the
field names horizontally across the page. I based the report on a union
query which gave me the results I want, but with a couple of issues I don’t
know how to resolve.

1) I created a group header for Question. An error message in design view
of the report says this is invalid sorting and grouping. However, the data
does display in groups by question in print preview.
2) I am asked to enter a parameter value for A1aC when switching from
design view to print preview or report view. Would this be because there are
no entries for A1aC yet? There are other fields that also do not have any
entries yet. If I select OK, the report displays with the correct results
except for #3.
3) Many records will not have entries for some of the fields and these
display in the report as a blank line leaving LOTS of white space in the
report. Is there any way to ignore null values?

A sample of the SQL from the union query:
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample];

In case it makes any difference, this report is a subreport linked by
CNTYNAME. Thank you.
 
If the design error doesn't bother you, ignore it. The report might think it
is a memo field. You could try set the Sorting and Grouping expression to:
=Left([Question],30)

I would confirm the spelling of the field A1aC.

To get rid of Nulls, try:

SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1aC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1bC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample]
WHERE [A2C] Is Not Null;
 
Thanks very much. I was able to eliminate the nulls, but, the issue with
A1aC is giving me a huge headache. I’ve checked the spelling – multiple
times, but do not find anywhere that is not spelled correctly.

I have a report with 14 subreports. It appears that the report does finally
open correctly, but the enter parameter value for A1aC display 12 times. I
leave the parameter text box blank and click on “OK†multiple times. Eight of
the subreports are based on a select query and I do not get the parameter
prompt when I open them individually. The other 6 are based on 6 union
queries, with only one having the A1aC field.

I wondered if the problem would be using the query, qYr2ReviewSample, in the
union query so I tried creating a select query (in query design view) first
and then a union query for the Comments By Section subreport (code is below),
but I still get the parameter prompt for A1aC. Arrrgh!

I do hope you have some idea why this occurs and how to correct it. Thanks!

qYr2CommentsBySection
SELECT [2007 Sample].ID, [2007 Sample].Key, [2007 Sample].CNTYNAME, [2007
Sample].STRATA, tReview.ReviewKey, tReview.ASectionComments,
tReview.BSectionComments, tReview.CSectionComments, tReview.DSectionComments,
tReview.ESectionComments, tReview.FSectionComments
FROM [2007 Sample] LEFT JOIN tReview ON [2007 Sample].Key = tReview.Key
WHERE ((([2007 Sample].STRATA)=2) AND ((tReview.ReviewKey) Is Not Null))
ORDER BY [2007 Sample].ID;

Then, the following query is based on the one above and is the record source
for one subreport.

qYr2CommentsBySection_Union
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"ASectionComments" as Section, [ASectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [ASectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"BSectionComments" as Section, [BSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [BSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"CSectionComments" as Section, [CSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [CSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"DSectionComments" as Section, [DSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [DSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"ESectionComments" as Section, [ESectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [ESectionComments] Is Not Null
UNION ALL SELECT [qYr2CommentsBySection].CNTYNAME,
[qYr2CommentsBySection].ReviewKey, "FSectionComments" as Section,
[FSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [FSectionComments] Is Not Null;


Duane Hookom said:
If the design error doesn't bother you, ignore it. The report might think it
is a memo field. You could try set the Sorting and Grouping expression to:
=Left([Question],30)

I would confirm the spelling of the field A1aC.

To get rid of Nulls, try:

SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1aC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1bC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample]
WHERE [A2C] Is Not Null;

--
Duane Hookom
Microsoft Access MVP


jmoore said:
Using several fields, I am trying to create a report that will provide a list
the all entries grouped by each field, in a vertical list instead of the
field names horizontally across the page. I based the report on a union
query which gave me the results I want, but with a couple of issues I don’t
know how to resolve.

1) I created a group header for Question. An error message in design view
of the report says this is invalid sorting and grouping. However, the data
does display in groups by question in print preview.
2) I am asked to enter a parameter value for A1aC when switching from
design view to print preview or report view. Would this be because there are
no entries for A1aC yet? There are other fields that also do not have any
entries yet. If I select OK, the report displays with the correct results
except for #3.
3) Many records will not have entries for some of the fields and these
display in the report as a blank line leaving LOTS of white space in the
report. Is there any way to ignore null values?

A sample of the SQL from the union query:
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample];

In case it makes any difference, this report is a subreport linked by
CNTYNAME. Thank you.
Click to expand...
Click to expand...
 
I wasn't aware there were multiple subreports involved. Have you checked the
Sorting and Grouping dialog in each subreport. How about all of the Link
Master/Child properties?

--
Duane Hookom
Microsoft Access MVP


jmoore said:
Thanks very much. I was able to eliminate the nulls, but, the issue with
A1aC is giving me a huge headache. I’ve checked the spelling – multiple
times, but do not find anywhere that is not spelled correctly.

I have a report with 14 subreports. It appears that the report does finally
open correctly, but the enter parameter value for A1aC display 12 times. I
leave the parameter text box blank and click on “OK†multiple times. Eight of
the subreports are based on a select query and I do not get the parameter
prompt when I open them individually. The other 6 are based on 6 union
queries, with only one having the A1aC field.

I wondered if the problem would be using the query, qYr2ReviewSample, in the
union query so I tried creating a select query (in query design view) first
and then a union query for the Comments By Section subreport (code is below),
but I still get the parameter prompt for A1aC. Arrrgh!

I do hope you have some idea why this occurs and how to correct it. Thanks!

qYr2CommentsBySection
SELECT [2007 Sample].ID, [2007 Sample].Key, [2007 Sample].CNTYNAME, [2007
Sample].STRATA, tReview.ReviewKey, tReview.ASectionComments,
tReview.BSectionComments, tReview.CSectionComments, tReview.DSectionComments,
tReview.ESectionComments, tReview.FSectionComments
FROM [2007 Sample] LEFT JOIN tReview ON [2007 Sample].Key = tReview.Key
WHERE ((([2007 Sample].STRATA)=2) AND ((tReview.ReviewKey) Is Not Null))
ORDER BY [2007 Sample].ID;

Then, the following query is based on the one above and is the record source
for one subreport.

qYr2CommentsBySection_Union
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"ASectionComments" as Section, [ASectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [ASectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"BSectionComments" as Section, [BSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [BSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"CSectionComments" as Section, [CSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [CSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"DSectionComments" as Section, [DSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [DSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"ESectionComments" as Section, [ESectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [ESectionComments] Is Not Null
UNION ALL SELECT [qYr2CommentsBySection].CNTYNAME,
[qYr2CommentsBySection].ReviewKey, "FSectionComments" as Section,
[FSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [FSectionComments] Is Not Null;


Duane Hookom said:
If the design error doesn't bother you, ignore it. The report might think it
is a memo field. You could try set the Sorting and Grouping expression to:
=Left([Question],30)

I would confirm the spelling of the field A1aC.

To get rid of Nulls, try:

SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1aC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1bC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample]
WHERE [A2C] Is Not Null;

--
Duane Hookom
Microsoft Access MVP


jmoore said:
Using several fields, I am trying to create a report that will provide a list
the all entries grouped by each field, in a vertical list instead of the
field names horizontally across the page. I based the report on a union
query which gave me the results I want, but with a couple of issues I don’t
know how to resolve.

1) I created a group header for Question. An error message in design view
of the report says this is invalid sorting and grouping. However, the data
does display in groups by question in print preview.
2) I am asked to enter a parameter value for A1aC when switching from
design view to print preview or report view. Would this be because there are
no entries for A1aC yet? There are other fields that also do not have any
entries yet. If I select OK, the report displays with the correct results
except for #3.
3) Many records will not have entries for some of the fields and these
display in the report as a blank line leaving LOTS of white space in the
report. Is there any way to ignore null values?

A sample of the SQL from the union query:
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample];

In case it makes any difference, this report is a subreport linked by
CNTYNAME. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Thank you, thank you, thank you! I had each subreport set to group on either
question or section, but somehow a sort on expression was a level above.
Removing the sort eliminated all prompts. Again, thanks. I struggled with
this for a very long time.

Duane Hookom said:
I wasn't aware there were multiple subreports involved. Have you checked the
Sorting and Grouping dialog in each subreport. How about all of the Link
Master/Child properties?

--
Duane Hookom
Microsoft Access MVP


jmoore said:
Thanks very much. I was able to eliminate the nulls, but, the issue with
A1aC is giving me a huge headache. I’ve checked the spelling – multiple
times, but do not find anywhere that is not spelled correctly.

I have a report with 14 subreports. It appears that the report does finally
open correctly, but the enter parameter value for A1aC display 12 times. I
leave the parameter text box blank and click on “OK†multiple times. Eight of
the subreports are based on a select query and I do not get the parameter
prompt when I open them individually. The other 6 are based on 6 union
queries, with only one having the A1aC field.

I wondered if the problem would be using the query, qYr2ReviewSample, in the
union query so I tried creating a select query (in query design view) first
and then a union query for the Comments By Section subreport (code is below),
but I still get the parameter prompt for A1aC. Arrrgh!

I do hope you have some idea why this occurs and how to correct it. Thanks!

qYr2CommentsBySection
SELECT [2007 Sample].ID, [2007 Sample].Key, [2007 Sample].CNTYNAME, [2007
Sample].STRATA, tReview.ReviewKey, tReview.ASectionComments,
tReview.BSectionComments, tReview.CSectionComments, tReview.DSectionComments,
tReview.ESectionComments, tReview.FSectionComments
FROM [2007 Sample] LEFT JOIN tReview ON [2007 Sample].Key = tReview.Key
WHERE ((([2007 Sample].STRATA)=2) AND ((tReview.ReviewKey) Is Not Null))
ORDER BY [2007 Sample].ID;

Then, the following query is based on the one above and is the record source
for one subreport.

qYr2CommentsBySection_Union
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"ASectionComments" as Section, [ASectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [ASectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"BSectionComments" as Section, [BSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [BSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"CSectionComments" as Section, [CSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [CSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"DSectionComments" as Section, [DSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [DSectionComments] Is Not Null
UNION ALL
SELECT [qYr2CommentsBySection].CNTYNAME, [qYr2CommentsBySection].ReviewKey,
"ESectionComments" as Section, [ESectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [ESectionComments] Is Not Null
UNION ALL SELECT [qYr2CommentsBySection].CNTYNAME,
[qYr2CommentsBySection].ReviewKey, "FSectionComments" as Section,
[FSectionComments] as Comments
FROM [qYr2CommentsBySection]
WHERE [FSectionComments] Is Not Null;


Duane Hookom said:
If the design error doesn't bother you, ignore it. The report might think it
is a memo field. You could try set the Sorting and Grouping expression to:
=Left([Question],30)

I would confirm the spelling of the field A1aC.

To get rid of Nulls, try:

SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1aC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
WHERE [A1bC] Is Not Null
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample]
WHERE [A2C] Is Not Null;

--
Duane Hookom
Microsoft Access MVP


:

Using several fields, I am trying to create a report that will provide a list
the all entries grouped by each field, in a vertical list instead of the
field names horizontally across the page. I based the report on a union
query which gave me the results I want, but with a couple of issues I don’t
know how to resolve.

1) I created a group header for Question. An error message in design view
of the report says this is invalid sorting and grouping. However, the data
does display in groups by question in print preview.
2) I am asked to enter a parameter value for A1aC when switching from
design view to print preview or report view. Would this be because there are
no entries for A1aC yet? There are other fields that also do not have any
entries yet. If I select OK, the report displays with the correct results
except for #3.
3) Many records will not have entries for some of the fields and these
display in the report as a blank line leaving LOTS of white space in the
report. Is there any way to ignore null values?

A sample of the SQL from the union query:
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1aC" as
Question, [A1aC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A1bC" as
Question, [A1bC] as Answer
FROM [qYr2ReviewSample]
UNION ALL
SELECT [qYr2ReviewSample].CNTYNAME, [qYr2ReviewSample].ReviewKey, "A2C" as
Question, [A2C] as Answer
FROM [qYr2ReviewSample];

In case it makes any difference, this report is a subreport linked by
CNTYNAME. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top