trying to build a questionnaire - may or may not be relevant to this f

[NeilBelgium]

please bear with me if you think this is in the wrong forum -it may well be an SQL/Access issue, I don't know without feedback, and people on this forum have given me great help to get to this stage!

i'm attempting an application to allow a group of people to fill in a pretty simple questionnaire, but the basic ultradev tutorials leave me with several issues which can't be tackled with simple form objects. I think I need some BASIC ASP or SQL commands - basic so that I understand them! I purchased ultradev because although i have an interest in learning ASP/SQL , I hoped to be able to do this in my own time and have ultradev do the hard work on this project! ( i have about 10 days, so a comfy armchair, pipe & slippers and a 2000 page reference book aren't going to cut it!)

Issue1: The user is asked for their name (i have a drop-down list) and the name of the person they are scoring (the questionnaire asks questions about their peers). I need to ensure that firstly they are not rating the same person twice, and if allowed to proceed, that a record is changed from a "0" to a "1" so that if they try again they won't be able to. How do I catch the info about the two names, grab the record which either allows/prevents this questionnaire being filled in, and then inserting a "1" into the record?

Issue 2:
Again, the same variables (username, name of person being scored) are needed, as there are different relationships between people. If the relationship, taken from another table in my db is "boss" (although i'm using numerical values 1 for boss, 2 for peer, 3 for customer etc.etc.) then the user is directed to a form that inserts data into a BOSS table. likewise for the other relationships. Basically, i need to redirect based on a value taken from one of my tables. More programming needed, I fear!!

Issue 3:
OK, we're rolling now. The form is created, the data is being inserted into the relevaznt table. HOWEVER, this is a questionnaire that various people can fill in, so my records are going to be written over every time!
What I need (again) is a method to extract the original value from the record, add the value being submitted by the user, and putting the sum of the two back into the record. eg. current value in the field is 17. the user is inputting a "4" score. The record should be updated to read 21, and not four.

OK, actually that's it- and my apologies if it made no sense hatsoever! A bit of a mouthful, but I'd really appreciate some help. There may even be a totally different and far superior way to build all this.
The key to this is that although I can insert & display data, i can't perform the operations necessary here which require the data to be called and questioned, action taken but not used in forms at all

many thanks, neil

 

[JerryDennison]

Actually, you're wrong on almost every count. You can and should do everything via forms. You primary problem is probably a lack of understanding of database structure. For example, under no circumstances would you want to &quot;...put(ting) the sum of the two back into the record....&quot;. This is not to say that some of the things you want to do won't be somewhat cumbersome to accomplish. For example, defining who is the &quot;boss&quot; of whom and so forth. This would be relatively easy if you can &quot;departmentalize&quot; people. As for insuring that one person cannot evaluate another person more than once, this is easily handled via a compound primary key using the interviewer and interviewee <sp> fields. So, to cut this diatribe short, you can do this very well and efficiently in Access, even to the point of letting the end users define each questionaire, it's just a matter of getting a better understanding of relational database fundamentals.

 

[Sawatzky]

Dear Neil,

issue1:
I would have a logon form, so the current user is identified before he does anything else. Otherwise it would be possible for him to vote in the name of someelse.
I would have a table with 2 fields 1 taking the voting person, one taking the person being voted.
So after logon I could query this table, for the persons left over to be voted at.

issue2:
I am not yet getting the point sorry.

issue3:

Jerry is right when he points, that you never put the sum of something back into a table. (shiver,blah)
instead you would could expand the table I introduced before with a field taking the vote-points. So you could do a sum-query of the points grouped by persons being voted.

hth
regards astrid

btw.: why do you have Belgium in your handle? just curiosity

 

[NeilBelgium]

Dear both,

let's take this a stage at a time then, and look at the db design!! here's some more detail:

I’m trying to build an online questionnaire which gathers scores and then inserts the data into an access db. However, several people have expressed the view, quite rightly, thet my db design isn’t optimal, and I can’t for the life of me work out how to set it up.

So, here's what I'm trying to build:
the final result should be a report for each of 40 managers, telling them how other people rated them on a 40-question questionnaire. The slightly more complex part is that of the ten or so people that are assessing each individual manager, there are different
”relationships” in play. for example, two of these people may be colleagues , two may be bosses, five may be people who report directly to this manager, and
others may be for example external customers, suppliers etc.
so, for each question, I don't need just the average score given, I need to
also produce results for each of these groups.
eg. your colleagues (three persons) rated you a 4.2, your subordinates rated
you a 3.7 and so on........ and overall you scored a 3.9 on this question.

what I'VE done is to create four separate tables - one for the scores given by a person's bosses, one for their colleaugues, one for their subordinates etc.
Plus another four tables to collect the amount of responses there are for
each group. obviously to produce an average score, I need both the total
score and the number of responses so that I can divide one by the other.
I've also got a table with the relationships between the hundred or so
people filling in these questionnaires, and the 40 managers. numerical
values demonstrate whether the relationship between these two people is
boss-subordinate, suborinate-boss, colleague or customer. another table,
same names, with a 1 or 0 value to record whether this person has rated this manager or not. The default value is 0, and once the questionnaire is filled in this value is changed to a 1.

Hope this makes some sense to you, and thanks once again for taking the
time to help- it is greatly appreciated!

neil

p.s. I ususally use neil, but I notice that people have taken this, and neilt. As I live & work in Belgium , pop.6, I figured this is a unique handle. no-one else in this country has my name!

 

[Sawatzky]

dear neil,

I will think about this, posting you the answer on monday or tuesday.

warmest regards to Belgium (where my mother comes from)

astrid

 

[NeilBelgium]

many thanks! is she from the dutch or french part? i'm in gent, east flanders.

 

[JerryDennison]

You should not have 4 different tables that do essentially the same thing. This will make it extremely difficult to do extensive analysis and it builds in undersirable redundancy. Also, you should not &quot;hard code&quot; the questions for the questionaire. By that I mean do not make a table for the questionaire and give each question a field in this table. Here is a brief description (with minimal field assignment, you can expand as fits your need) of the tables and fields for each.

Employee/Customer table
ID - Primary key (recommend autonumber)
Name - This can be broken down into first/last etc.
Type - employee, customer, etc.
Supervisor - FK link back to this table's PK
Department - FK link or lookup to department table

Department table
ID - Primary Key (recommend autonumber)
ParentGroup - FK link back to this table's PK
(this allows you to nest your groups and/or create a hiarchical group structure)
Name - text description of the group

Questionaire table
ID - Primary Key (recommend autonumber)
Description - text field describing questionaire
Eligible Groups - optional field linked to the Department table PK that limits who can take/evaluate this questionaire

Questions table
ID - Primary Key (recommend autonumber)
QuestionaireID - FK link to Questionaire PK
Question - text field for the actual question

Answers table (optional table for allowing only specific possible answers to each specific question)
ID - Primary Key (recommend autonumber)
QuestionID - FK link to QuestionID
Answer - text field for the actual answer(s)

Questionaire/Testee table (this is the table that will capture when a questionaire is taken)
ID - PsuedoPrimary Key (recommend autonumber)
QuestionaireID - Link to Questionaire table
Employee/CustomerID - Link to Employee/Customer table
(note: if you don't want a user to answer any questionaire more than once use a compound primary key made up of the QuestionaireID and Employee/CustomerID)
Date - date questionaire taken

Questionaire/Testee Answer table (this table will capture the actual results of the questionaire)
ID - PsuedoPrimary Key (recommend autonumber)
Questionaire/TesteeID - link to psuedoprimary key of questionaire/Testee table
QuestionID - link to question table
(note: to insure that no question is answered more than once make the Questionaire/TesteeID and QuestionID a compound primary key)
AnswerID - link to answer table (if applicable)

Hopefully, you get the picture. This is a much more normalized structure for handling your questionaire issue. This structure will minimize the need for code (you will probably need to use some code to insure that when a questionaire is take that all responses are recorded, note: you can allow questions to be skipped completely without the need for entering a null record, by linking the question table to the responses you'll know which questions weren't answered) This structure will also allow you to let the end user create their own questionaires with any number of questions and answers for each without the need of modifying the structure or your forms/queries/reports.

 

[NeilBelgium]

thanks jerry- I'll have a good look at this then report back!
It's 1am here now and i'm fading fast.

neil

 

[Sawatzky]

Good morning Neil,

I saw jerrys answer, and it is similar to what I thought .

Just to have it mentioned. Having a not compound primary key on every table is best. To ensure that records are not filled in twice into the table make a compound unique index. (you can't have 2 primary keys in 1 table).

As Jerry already said, a good structure in DB minimizes code needed.
Nevertheless, the unique index will not let you enter double records, because it bails out, when trying to insert records, but you should catch such a situation in advance. As a programm's normal flow should never rely on errors being produced.

regards Astrid

(my mom's from Deurne/Antwerpen. De groeten naar Gent)

 

[NeilBelgium]

guys -

sorry, I've been away travelling for a while and never got round to thanking you both for your help
so- thanks!!

everything on the db is sorted now!

your regards have been passed to belgium,

best ishes, neil