Question Replace a record value with a pre-determined comment?

[daveblanch]

Hi,

I'm hoping for some help, I am working on what is a small and fairly straightforward database but am having trouble with one aspect of it. Apologies for the title of the thread but i didn't know how to phrase it! This is in Access 2010.

The database is required because we now have forms that can be submitted online when previously they were completed as Excel spreadsheet and submitted - then they had to be collated and analysed.

The new database has two tables, one is the output from the forms that have been submitted. Each record has a number of questions that are answered as Yes, No or N/A and is stored as text. The other table is a 'Comments' table. For example is the answer to a question is "Yes" then one comment will be shown on a report, if "No" then it will be another. N/A is ignored.

The field name in table one (which holds the data submitted) are each of the question titles, a 'ID' and some demographic data. There is a field in the comments table (questions) that holds a copy of all of the question field names in the first table from which we are hoping to link - the other is the appropriate comment.

The problem comes when trying to create the report.

We need to create a report that produces a report for each individual who has submitted a form and provide the appropriate comments, i.e. if answer Yes to question 1 say "No further action", if answer No to question 1 say "This is the action to be taken".

There are 30 questions and up to 400 users who are to submit.

I just can't seem to work out how to substitute the answer of the question for the appropriate comment.

Hope someone can help - let me know if i haven't been clear or should provide more information.

David.

 

[BBQ Kittens]

I had to read this a few times but ill see if I understood correctly. You want to generate a report specific to a user and their input and replace yes, no, n.a. with their appropriate comments on output for that user. If this is right then some simple vba should do the job assuming your savvy in vba. You can store the username of each person with their input and use a query/filter to gather that input for the report. Within the query, make an IF statement that enters in your comment ie.
Comment: If([text1]="Yes","Comment1",If([text2]="No",Comment2","Comment3"))
From here you should be able to manage the rest I hope.

 

[daveblanch]

Thanks for the reply, i did find my question quite hard to articulate.

I'll give it a go this morning and post back my results!

 

[daveblanch]

Hi,

I can't figure it out. I've attached a copy of the database in the hope seeing the data might give somone more of an understanding of what i need.

All i would like is for the query/report to look at the answer of each of the questions in tbl_Main_Data and depending on whether it is Yes or No (ignoring NA) replace the 'Yes' or 'No' with the appropriate comment from tbl_Comment.

I've tried an Iif AND statement in the query but it starts bringing in each Name mulltiple times.

The structure of the data in tbl_Main_Data is how it is downloaded from the server.

If i can provide any more information please let me know and thanks for any help that you can offer!

David.