Scorecard FORM

[HelsBels]

Hello

I am having difficulty setting up a form to record quality scores. Originally I had a spreadsheet set up which had a drop down list for each question and then if statements in another cell which would give each question a mark depending on the answer (e.g the drop down list would have options such as exceed, met, not met and the formulas in the next cell would give each option a score depending on which answer was given). However, I now need to set up a database as I need to be able to build up some historical data so that I can run queries and analyse the data in the future.

I started setting up a table in Access which I have called GREETING. In that the first question is the introduction. I have used a list box with 2 columns to record the information but it's not bringing back what I want to see. This is the format:

Field Name: Introduction
Display Control: List Box
Row Source Type: Value List
Row Source: Met;3;Not Met;0,Appr;1
Bound Column:1
Column Count:2
Column Width: 1.501cm; 0.75cm

What I want this to do is when I select Met it should bring back a value of 3, not met =0 etc. When I open the table it lets me select the option met, not met etc. and shows the value beside it but once I've clicked on it it only shows the description met or not met and not the value.

The 2nd problem is I then tried to use the table in a form but the form doesn't work at all - it doesn't have a drop down box it just shows the value not met!!

The properties of the form are:
List Box
Row Source/Type: Table/Query
Row Source: SELECT DISTINCT ROW [GREETING].[INTRODUCTION] FROM[GREETING].
Bound Column: 1
Enabled: Yes
Locked: NO

Sorry that this message is so long but has anyone got any ideas as to the best way to set up a form like this that other users can use to enter information?

Thanks

 

[ColinEssex]

Hi
I think I follow what you're saying (a bit) - how many questions are there?

Col

 

[HelsBels]

Hello

Sorry, I know it's longwinded but I don't know how else to explain. There are 7 different subject headings and each has about 4 different questions each. I was going to set up 7 different tables and base the form on that. Even though each question is marked with exceed or met, the score for exceed on one quesion might be different to another (so exceed = 3 on 1 question, but 5 on another.

Does that help?

 

[ColinEssex]

Firstly, having 7 different tables is not the way to go, you'll have big probs later when you want to view results.

It seems that there are 2 ways you can go about this that we can think of.

Option 1

You have a form with 7 different tabs Question 1, Question 2 etc
then on each page you have an option box with the selection in it, that way they can only select one option per question and you will be able to allocate a value for each option, so "Met" in Q1 could = 3points but "Met" in Q3 could = 5points etc.
You would need to create a table with a field for each value and the option boxes would be bound to the relevent field.

Option 2

You create a table of questions with an AutonumberID. You then create a table of answers linked by the ID. So the answers table would have 2 fields - ID and answer value.

Perhaps someone else may come up with another solution, I was just thinking off the top of my head.

Hope this helps
Col

 

[HelsBels]

Thanks - will try the 2nd option, I think that sounds easier.

 

[HelsBels]

Forms with option box

sorry to revisit this after so long, but I've tried to set this up following the first option that was given to me and I'm getting really confused. My understanding is that I set up 1 table which lists all the questions. But then I don't understand how I create a form from that table that consists of mulitple pages. How do I do that? Once I do that, I think I then create an option box per page and link back to each question. But I'm stuck on the very first part, i.e creating multiple pages within the form?

 

[ColinEssex]

Re: Forms with option box

But then I don't understand how I create a form from that table that consists of mulitple pages. How do I
do that? /QUOTE]

In form design view. In the toolbox is an icon of a form with tabs called tab control. Drag and drop to your form.

I'll knock you up a quick sample

Col

 

[ColinEssex]

Here you go. Its a bit basic but it shows a form with tabs on it.

Its in A97

Col

 

[andy_dyer]

Hi All,

Sorry to resurrect this one but...

I'm trying to do a similar thing...

I've got 15 questions and depending on the answers selected in the combo boxes I want to allocate different scores...

I think I've got the table structure right, i.e. i've opted for the second option and created two tables.

Can anyone help?

Thanks

Andy

 

[HelsBels]

I ended up using the tabbed form option in the last reply with option boxes allocated against each question on the form and it worked really well for me! Didn't end up using the 2nd option with the two tables, got too confused!