I'm working on a faculty database and I want to store student feedback data associated with each course offering, and also have it tied with the instructor. I've got a main form that's tied to a Course Offerings table, and a subform containing the survey questions and answer fields displayed in Continuous Forms view. Since multiple instructors could teach during the same course offering, I want each survey submitted to contain the instructor's name. To do this I'd like to have a combobox at the top of the survey datasheet containing the instructor names from the instructor table (the easy part), and then update each row in the Instructor column with the selected instructor's name. After submitting the survey the form will append to a master feedback table and the form will clear. In case that didn't make sense, here's another way of looking at it:

(Combobox: [InstructorNames])
[Instructor Name] | Question 1: On a scale of 1-5, blah blah | (Student's Answer)
[Instructor Name] | Question 2: On a scale of 1-5, blah blah | (Student's Answer)
[Instructor Name] | Question 3: On a scale of 1-5, blah blah | (Student's Answer)
etc.

Questions could be added or removed over time, so I need something that will populate every Instructor field in the entire table no matter how many or few questions there are.

The part I'm trying to figure out right now is how to update all of the Instructor Name fields in the entire table shown in the form in datasheet view.

Thanks in advance.

Hi,

(IMG:style_emoticons/default/welcome2UA.gif)

What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question.

It would really help to know if your table structure is properly normalized before giving you any advice. Can you describe your table structure for us? Not sure why you would need to update the "entire" table with the Instructor's name. The Instructor's name should be stored as the student fills out the feedback form.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Sorry, forgot to add that in the beginning. Access 2007. And thanks for the quick reply.

I may be going about it all wrong, but the feedback form is a separate table with each row containing a question. The columns are basically "QuestionID", "Question", and "Answer." The entire feedback form shows up as Continuous Forms so it looks like a one-page survey. Tab 1 contains the course offering information (e.g English 101 from 01/01/2012 to 03/03/2012), and Tab 2 contains the survey for that course offering by enables the instructor to be added for that particular rated period.

I want the person filling out the form to be able to select the instructor's name in the page header, one time for each survey submitted. Upon submit, the survey table will append to the master survey results table which will have "ID", "Course Name", "Course Offering", and "Instructor Name" in addition to the "QuestionID", "Question", and "Answer" columns. The survey table will then clear of all answers so the next one can be populated, maybe for the same instructor, maybe for another.

The appended table containing all of the master survey data could later be easily queried to view feedback by Course, Course Offering, Date Range, or Instructor.

I attached some of the tables in case you see something I should be doing differently.

Hi,

I haven't looked at your db but you are correct to have a separate table for your question. However, you also need to separate the answers0 from the questions. That way, you don't have to repeat all the questions for each responder.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Unless I misunderstood you, I do want to repeat the same questions for each responder so that the statistics will have more meaning over time. For example, if an instructor spends the morning period talking about common misunderstandings with punctuation and then hands out five feedback forms before lunch, the end result should be five separate sets of answers for each question on the survey. The feedback will actually be done on paper for simplicity, but a data-entry clerk will be responsible for plugging everything in to the database (another reason for having one standard).

Hi,

Your database, if constructed properly, will still make sense over time even if you don't "repeat" each question for each response. That's called "normalization." Here's an example:

Let's say you have the following table of questions:


To create the answer table, it would look like this:


QID represents each question and SID represents each student. Notice that the clerk doesn't have to rewrite each question for each student and you can easily tell which question each student answered and what their answer is.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

That's kind of what I'm shooting for. Names are not required on the feedback forms since it could discourage unfiltered honesty, so each feedback question will only be linked to the Course, Course Offering, and Instructor. That way collective feedback can be evaluated over time to identify good or bad trends and staff training meetings can be tailored to address deficiencies rather than just picking random subjects. I've got the CourseID and CourseOfferingID from the main form since that will remain constant throughout a series of feedback forms, but multiple instructors could teach during a course offering, hence the need to auto-populate each question with the instructor's name using a combobox at the top of the form. The Instructor Name field will be hidden, but will move to the master feedback repository when the form is appended.

I might even change my design slightly to have one single feedback form with (no subforms) and allow the Course, Course Offering, and Instructor to be selected at the top of the form to populate every row in the feedback table. I'm currently thinking that a "while" or "for each" statement might do the trick, although I don't have a lot of experience with either. Basically, for each row in frmFeedback where IsEmpty([InstructorName]), [InstructorName].value = 'whatever's in the combobox'. Nobody will see the form fields populate since they'll be hidden, but when I append all the data I need will move to the master table.

Hi,

I am having a hard time imagining your setup. I think you may be working harder than you have to. Probably, setting the Default Value might be enough for what you need instead of doing loops.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

"I think you may be working harder than you have to."

It's sad, but I've been working on this all day with very little progress...so... I think you're right. I just noticed (seconds ago) that the combobox wizard in 2007 allows the combobox value to be placed directly in to the active record, which gets me a step closer. However, that default value idea sounds like it could be a lot simpler if I can set each row's default value for a specific field to the combobox value. Never tried that before, but it sounds like it should work. Might have to try it in the morning; I'm fried.

Okay, when you get a chance, try the following in the AfterUpdate event of the Combobox:

Me.ComboboxName.DefaultValue = """" & Me.ComboboxName & """"

And when I get a chance, I'll download your db and take a look.

Good night!

I tried adding the line of code you mentioned, but I can't see that it's actually doing anything. What was it supposed to do? Setting the Default Value in the properties doesn't seem to work either... it updates the first record of the Continuous forms and then doesn't update anything else. I'm trying on a different computer in case it was a software issue. I have a feeling that I'm going to implement DoCmd.FacePalm once I figure out what I'm doing wrong.

Hi,

The Default Value property sets the default value for a field for "new" records. Try selecting an instructor in the combobox and then add a new record to see what happens. The instructor you selected should pre-populate that field in your table.

Like I said, I haven't looked at your setup yet (still don't have time right now) so, I'm not really sure what approach is best for you.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

That explains why it wasn't working. I guess Default Value isn't the solution then since the eval form won't have any new records added; just existing records modified with the question responses. I've tried a couple Do Until or Do While approaches, but I'm not experienced with working with Recordsets so I can't get it to work completely.

I set the following in the AfterUpdate event for the field I want to populate, and it works fine for the first record... now I just need to get it to repeat until the last record in the continuous form.

Me!ID_CourseOffering.SetFocus
DoCmd.GoToRecord , "", acFirst
Forms!frmEvalCourseOffering.ID_CourseOffering.Value = Forms!frmEvalCourseOffering.cboOffering.Value
DoCmd.GoToRecord , "", acNext
' then continue until the end of the form and stop....

By the way, ignore the database I uploaded. The feedback form is no longer a subform so there are no parent/child relationships to deal with, and both the Course Offering and Instructor are just selected from comboboxes derived from the respective tables.

Hi,


This is why I said you may be working harder than you have to. The Default Value is a feature of Access that is supposed to help you. If you end up using loops, then maybe your setup needs to be examined. When I get done with my current project, I will try to download your db to see if I can make any more recommendations.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi,


Sorry, I missed this post and just downloaded your db. I don't see any form in it so I can't comment on your current setup. However, I do have a couple of comments on your table structure.

1. You should avoid using Lookup Fields at the table level. Your table "tblCourseDelivery" has a lookup field for the Course Name. To see one of the bad effects of this practice, take a look at the second column of your query "qryDeliveryInstructor." I bet that's not what you were expecting to see, right?

2. In your junction table to indicate the instructor who delivered the course, I wonder if you need a date field to indicate when they actually did it. That way, your feedbacks will be more specific. Not sure though if that's required per your business rules. If you include a date, you won't need to know the instructor in the feedback because you can figure that out if the student dates their feedback.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Thanks, I'll keep that in mind. Now I just need to figure out how to run that loop and I'll be just about set. Maybe I'll have some time to figure it out this weekend.

Hi,

(IMG:style_emoticons/default/yw.gif)

Let us know how it goes... Good luck!

Turns out I needed two select queries and one each of append, update, and delete. The first select query has the instructor/lesson/class values set to the comboboxes from the form, the next select query filters out the rows that have questions, but no answers. The append moves the answered questions to the master feedback table. The update query changes any populated fields back to null values but leaves the question numbers and questions in tact. Then the final delete query deletes the extra row that was added if a general comments field is filled out without being associated to a specific question, but still has instructor/lesson/class information added in through the first select query. Works like charm.

This post has been edited by snekker: Mar 9 2012, 06:51 AM