Access 2003 / Forms / Data Entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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).
 
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
 
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
 
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
---------------------------
 
I find this suggestion quite interesting. I have recently been tasked with
creating and maintaining a quality control databse to be used for tracking
departmental quality with monitoring calls and auditing forms. Is there an
existing sample database to download and add my own persoanl touches or what
would be the ideal launching point to get this party started?
 
Sorry, I don't know of any. You could try Googling.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
G'Day Graham

Thank you for your reply, I am off to Google-land to see what i can track
down.
 
Back
Top