If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
union query for report
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. |
#2
|
|||
|
|||
union query for report
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" wrote: 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. |
#3
|
|||
|
|||
union query for report
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" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
union query for report
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" wrote: 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" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
union query for report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|