If [fieldname] then "send this to a report"

[goslincm]

Here is what I need to do and have no idea really where to begin. User has a form set up with several yes/no questions. For each question that is being answered YES I need to place the corresponding questions into a letter.

The letter will have an intro paragraph that explains why they are being "disqualified" for a particular service and below that would be the list of questions that they answered YES to on the form.

Hope I presented this clearly.

 

[Remou]

This should be easy with normalized tables. What is the problem?

 

[goslincm]

I guess the problem is my inexperience as I don't know where to begin. Create a report and put code behind a command button.

 

[Remou]

How about creating a report based on the table of applicants with a sub-report based on the table of responses, selecting only those responses that are True (Yes). The link fields would be the relevant ID. You can then use DoCmd.OpenReport with he Where argument set to the relevant key on the form.

 

[goslincm]

So as a user who has only had one SQL class, and most query done through the design grid, what your saying is I need to, in my query, describe the proper way of using my WHERE clause so

WHERE [fieldname]= -1 ?? AS "You do not qualify due to...

 

[Remou]

I would imagine that you have a table:

tblReason
QuestionID
RejectionReason

Or even include the reason in the question table. You will, I imagine, be joining the response table to the question table in your query.

 

[goslincm]

Unfortunately the table I am dealing with is NOT set up properly. Table.AuditReviews contains fields for the various questions [question1], [question2], etc. These are yes/no data types. The actual question being posed to the user is merely a label. So they read the label, and provide their answer in the yes/no fields.

 

[Remou]

Well then you have a problem. It may be easier to build a Word Document using bookmarks or simply using automation.

 

[goslincm]

So working in Word would be better or should I fix the problem. Can I fix the table problem?

 

[Remou]

It is always better to have a properly set-up database. Small problems tend to get bigger with time when the tables are not normalized. Have you read the often-recommended

http://www.r937.com/relational.html

?

You may also wish to look at the survey database published by dhookom on his site.

 

[goslincm]

I have been hesitant in tackling this as the database has been set up at this end and used by staff for 4 years. I came on board 1 year ago.

Yes I have read the article within the link you indicated. Any suggestions for what to read when working with an existing database and breaking it down into the 1NF,2NF, 3NF?

 

[Remou]

No, I don't. How important is this database and do you see it going anywhere? Is it simply a quick way of typing letters? If so, creating a Word document from a form is not so difficult and should only take an hour or two, if you have done anything similar before. If the database is likely to become important, it may be worth creating a new, properly set-up database and importing the data. If you sketch out your table schemas and post them in forum700, you are likely to get a number of useful suggestions. Once again, to cope with the immediately problem, you could create a Word document.

 

[goslincm]

Thank so you much for all the help Remou. I am going to create a word document and then tackle the other issues as the database is considered important and does contain a lot of information. Just does not contain it in a well designed manner.

Take Care