Re: Access 2003 / Forms / Data Entry

The problem you're having is due to poor conceptual design. What I'm trying
to tell you is how to resolve the problem so it never happens again.

You only need one table to store the questions, and one to store the answers
(which would include a SessionID).

Instead of hard-coding anything, build the user interface to account for
whatever is there. That's why I suggested using a ListBox to display the
questions. It doesn't much matter who's using it, the Candidate or their
Manager.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

"th0r0n" <th0r0n@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BF6D007B-19B8-462C-800A-46D0E02D84BD@xxxxxxxxxxxxxxxx
> Hi Graham,
>
> Thanks for your helpful reply,
>
> However, This isn't to be entered by the Candidate himself, but the Line
> Manager of the Employer (It's to do with performance quality).
>
> So at the moment I have 32 columns with the questions as the column
> headers,
> which works for one campaign where the question number is 32, but on other
> campaigns, where sometimes there is 28 etc, this obviously doesnt work.
>
> I record the staff name, team leader, data, time, and the 32 yes/no
> answers,
> I think it would be easier to generate a new key for each new session and
> insert all 32 answers seperately, and use the unique key to bring all 32
> records back together.
>
> Would I be able to achieve stage 3 normalization through this process?
>
> Regards,
>
> Toby
>
> "Graham R Seach" wrote:
>
>> You need a table for recording the answers, with a foreign key constraint
>> to
>> the specific question.
>>
>> I'd display the questions in a list box. That way, it doesn't matter how
>> many questions there are. The list box can be filtered to display only
>> those
>> questions relating to the user's department.
>>
>> The user clicks a question, and enters their answer into a textbox. When
>> they click a button, maybe entitled "Submit answer", their answer is
>> saved
>> to the table.
>>
>> Regards,
>> Graham R Seach
>> Microsoft Access MVP
>> Sydney, Australia
>> ---------------------------
>>
>> "th0r0n" <th0r0n@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:C49033E4-72E2-4DC9-A781-B0D775866257@xxxxxxxxxxxxxxxx
>> > Basically,
>> >
>> > I'm writing a database that records answers of different questions
>> > (staff
>> > performance monitoring sessions, to be exact!)
>> >
>> > I have 8 tables:
>> >
>> > lkpManagers, lkpContracts, lkpServices, - 3 Lookup tables that show the
>> > Manager, Contract and Department the Employee is working on, related to
>> > tblFullStaffList
>> >
>> > tblQuestions - A list of Question Names, Sections, Descriptions and
>> > Service
>> > that they are related to.
>> >
>> > tblFullStaffList - A List of Staff Name, Manager, Contract and Service,
>> >
>> > tblSessions - The tracking system for a performance monitoring session,
>> > this
>> > takes the Employee Name, Date, Time, Manager (Combobox on form) NT
>> > Logon
>> > (Environ("UserName") and then I have *32 Columns* to record the Answers
>> > for
>> > each question.
>> >
>> >
>> >
>> > Now the problem is this, I have hard coded all 32 questions into the
>> > columns
>> > and used the caption for the checkbox labels as the question name (Hand
>> > written all of the labels, but have only just found out that the number
>> > and
>> > type of questions change depending what department the session is being
>> > run
>> > on.
>> >
>> > I have 1 form for entering the data with a tab control splitting the
>> > Question Sections up so they're easier to process, but now obviously
>> > depending on the member of staff selected the service needs to change
>> > based
>> > on the service they work on.
>> >
>> > Does anyone know how I could do this? I've been trying to create 32
>> > empty
>> > labels on a form, and set up a loop to pull them out of the database,
>> > but
>> > it
>> > doesn't feel right having a 32 column table to store the question
>> > answers!
>> >
>> > Can anyone think of an easier/more correct solution?
>> >
>> > Thanks very much!
>> >
>> > (PS: IF you need anymore info, I'll see what I can do).
>>
>>
>>


.