Help w/query and null memo fields

[houstonbill]

Have a query based on a form where users input comments based on several items. Currently, the subsequent report lists all 6 comment items whether or not the user has entered comments. Ideally, I would like to have the 6 items listed once with all comments appropriately under each item but I don't see how that can happen since I do not have the option to set headers for each of the items. So....I will go with separate sets of 6 for each person who inputs information from the form. Since the users do not have to input on all comment fields, how can I eliminate those that have no entry? Tried "is null" for each item but then nothing shows. Is there something else I should be doing? Below is my SQL:

SELECT tblReviewers.SuggID, tblReviewers.Feasibility, tblReviewers.StrategicPlans, tblReviewers.Costs, tblReviewers.Safety, tblReviewers.EmpSat, tblReviewers.Communication, tblReviewers.Competive, tblReviewers.Novelty, tblReviewers.CustProv
FROM tblReviewers;

 

[rac2]

This sounds more like a question for the Access Reports forum.

http://www.tek-tips.com/threadminder.cfm?pid=703

I have handled this issue for web page reporting by showing tallies for those questions with fixed responses, and showing links for the open-ended questions. Each question has a link to a different page (actually to a dynamic page) that lists all answers to that question. In Access this would be somewhat like preparing a different report for each question that allows comments. Or at least it would involve a different query for each question.

My feeling is that listing all of the answers to one question together will be satisfactory because the viewers attention will be focused on the question, not on the associated ratings from each respondent.

In order to see the comments in the context of the ratings for one respondent, I provide the questionnaire form with the specific answers for an individual.

Just some ideas.

 

[lespaul]

maybe something like:

SELECT A.* FROM
(SELECT SuggID, Feasibility, "Feasibility", FROM tblReviewers WHERE Feasibility <> ""
UNION
SELECT SuggID, StrategicPlans, "StrategicPlans", FROM tblReviewers WHERE StrategicPlans<> ""
UNION
SELECT SuggID, Costs, "Costs", FROM tblReviewers WHERE Costs<> ""
etc.
SELECT SuggID, CustProv, "CustProv", FROM tblReviewers WHERE CustProv<> "") As A

you can then look into a crosstab query to convert the display.

Some sample data from your table and your expected results make it much easier to really "see" what you need done. This is my guess!





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi