Field in UNION query not visible in report

[MarkRennes]

I have used a SELECT UNION in order to get round the fact that Access truncates Memo fields.

My query reads:

SELECT DISTINCT [surname] & " " & [forenames] AS fullname, Answer.grade, Answer.[grade mark], Answer.[actual mark], Answer.[penalty type], Answer.[penalty percentage], [Project Question].[weighting in project], Project.[weighting in module], [actual mark]*[weighting in project]*[weighting in module] AS contribution, Student.[student number], [Enter Student N°; leave blank to show all] AS Expr1, Student.surname, Student.forenames, Team.[team name], Project.[project code], Project.[weighting in module], Question.[question code], [Project Question].[question number], [Project Question].[question part], [Project Question].[max mark], [Project Question].[weighting in project], Answer.[actual mark], [Project Weighting].type, [Project Weighting].[SumOfweighting in module], Project.[project type], Answer.[answer nr]
FROM [Project Weighting] INNER JOIN ((Team INNER JOIN (Question INNER JOIN (Project INNER JOIN ([Project Question] INNER JOIN Answer ON [Project Question].[project question SID] = Answer.[project question SID]) ON (Project.semester = [Project Question].semester) AND (Project.yr = [Project Question].yr) AND (Project.[project code] = [Project Question].[project code]) AND (Project.[module code] = [Project Question].[module code])) ON Question.[question code] = [Project Question].[question code]) ON (Team.trigramme = Answer.trigramme) AND (Team.semester = Answer.semester) AND (Team.yr = Answer.yr) AND (Team.[team number] = Answer.[team number]) AND (Team.[group number] = Answer.[group number]) AND (Team.[module code] = Answer.[module code])) INNER JOIN (Student INNER JOIN [Team Student] ON Student.[student number] = [Team Student].[student number]) ON (Team.trigramme = [Team Student].trigramme) AND (Team.semester = [Team Student].semester) AND (Team.yr = [Team Student].yr) AND (Team.[team number] = [Team Student].[team number]) AND (Team.[group number] = [Team Student].[group number]) AND (Team.[module code] = [Team Student].[module code])) ON [Project Weighting].type = Project.type
WHERE (((Student.[student number])=[Enter Student N°; leave blank to show all]) AND (([Enter Student N°; leave blank to show all]) Is Not Null)) OR ((([Enter Student N°; leave blank to show all]) Is Null))
ORDER BY [surname] & " " & [forenames], Project.[project code], [Project Question].[question number] UNION select news as newz, null, null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null, null, null , null from control where sid = 1;

The field newz is not visible when I use that query as the record source in a report - that is, the field list includes fullname, team number etc. but not newz (or news).

What am I doing wrong, please? Thanks for your help.

 

[pbaldy]

You have that in the position of FullName in the first SELECT, and the field names always come from the first SELECT.

 

[MarkRennes]

Thanks for your reply. I appreciate that only the field names in the first part are visible. But it's the value in the second table that I want, that is, the news attribute - that's the whole point of using a UNION query in the first place. If I reverse the order of the two components of the UNION, I won't have access to all the other fields! This query is the control source for a report. If I include the control table as part of a single query (no UNION), then the contents of the news attribute - which is a Memo field - get truncated.

To step back: what I'm trying to do is to keep a news field on a table containing a single record - the control table - and to be able to display it as part of the report header. Is there an alternative way of achieving this?