List text data in memo fields?

[directormac]

Probably just a person preference, but I always use autonums for Primary Keys unless there is a specific reason not to (e.g., a junction table). I'm revising your db to do this now...

--Auto Mac

 

[directormac]

The QuestionID field is unecessarily complex--if you know what survey the question is linked to, you don't need to put a marker for it in this field. Making this a simple int will also clear up your sorting problem (noted earlier) and eliminate the need to include the question numbers within the text of the questions themselves.

Working...

--Editing Mac

 

[Kila]

Oh, you know us Junior Members SO well!!!! One of the 1st surveys I did a few years back was just like the madman's!

The 1st version of this Patient Satisfaction survey was like the redo of David's survey, with a field for each question. We spent MUUUUUUUUCH time writing the reports for that thing & calculating the report fields (Dcount..........). In spite of all the work we already put into the thing, Rich's suggestion to restructure it was very appealing because the survey has already been changed once after the reports et al were almost done, requiring changes to EVERYTHING because a new question was added before the 1st question. I don't want to do that again, & I know they will change it again (Gotta love committees that don't include the designer!!!)

At any rate, I have completed tables & queries of the new database exactly as Pat suggested...before you sent your link. At this moment, I am currently struggling with the data entry form. I think I've got it....I'll let you know.

Thanks so much!!

 

[directormac]

What is the purpose of the Clinic table? Do surveys belong to clinics? If so, let's have a FK field in the Surveys table assigning it to the proper clinic...

--Clinical Mac

 

[Kila]

Yes, I know what you mean about the complex ID. I waffled with that one...and still am. I'll probably change it. Thanks for looking at it.

 

[Kila]

I realized my mistake with the clinics table this morning. (remnants of the 1st database ) I reworked it. Now the results table links to it instead so that when a survey is entered, they can pick which clinic the patient is in. There are five different surveys, but MANY clinics the patient could be in completing one of them.

 

[directormac]

Looks like the same question may appear on more than one survey, yes? For example, "Have you been previously seen at this medical center by a primary care provider in the last two years?" This is why I was looking for junction tables earlier, so that you could re-use both questions and answers...

Check out the attached.

-Quickie Mac

 

[directormac]

And the answers definitely repeat way too much...

--Still-Working Mac

 

[Kila]

STILL fighting with the combo box. I FINALLY got it to show the answers on the data entry form, based on an invisible QuestionID box, but it keeps showing the answers from the 1st question. How can I get it to refresh the query before showing the answers when you click on the combo button?

This is the programming for the command button. I don't really know VB. What command line do I add?

Private Sub cmdNext_Click()
On Error GoTo NextError
DoCmd.GoToRecord , , acNext
Exit Sub

Thanks.

 

[Kila]

Yes, the answers repeat, but not all the time. I thought about it, but I was not sure of a clean way to handle that inconsistency with reference to the combo selection box in the data entry form.

 

[directormac]

STILL fighting with the combo box.

Thought you wanted a radio button / option group, or something like it? Look at the Northwinds sample and search Access help on how they create the main menu forms. Couldn't you do something similar?

--Pondering Mac

 

[Kila]

I usually do use autonums, but the darn thing kept displaying the ID field instead of the test I wanted (in hind sight that may have been because it was a memo field, not text. I have since changed it.)

 

[directormac]

Forget the above, just use subforms. See if the attached is at all helpful.

It doesn't tally, just displays at this point. Set the OnClick event of the rectangle next to each answer to run some code to add the appropriate record to tblResultsDetails.

Also needs a form for the creation of surveys, allowing you to insert questions and answers. Plus whatever reports you want to summarize the findings.

--Sketchy Mac

 

[Kila]

Thanks. I'll look at it. I did change the QuestionID AND add an autonumber to the questions AND the answers so that as the question numbers are changed (as if the committee would change the survey! ) , the answers that have already been entered will remain attached to the right question.

 

[directormac]

Did you add autonum keys to the other tables as well? I promise it will make life easier... Check out the attached. It uses your own structure, basically, but with slightly cleaner relationships due to the use of autonums everywhere. Be sure to check out the queries and form recordsources as well.

--Auto Mac

 

[directormac]

BTW, you can always run a one-time update query to fit your old data to a new & improved structure without loss.

--Upgrade Mac