Assign a numerical value to a response and calculate a score (1 Viewer)

[dafne]

Dear all,
First of all, I'm sorry if I haven't posted this in the right section. I'm not sure which category my problem falls into.
My form contains a series of checkboxes (e.g. "animate", "human", "concrete", "definite"...). I want to assign numerical values to my answers in order to calculate what I call the degree of salience of the sentences I'm studying (I work in Linguistics): for example, a checked checkbox is worth 1, and an unchecked checkbox is worth 0. I would then be able to classify the examples in my DB according to the following scale:
0-3 (checked checkboxes): low salience
4-7 (checked checkboxes): medium salience
8-11 (checked checkboxes): high salience

I've been looking all over the internet for an answer, there was talk of "IIF statements" and "Switch" functions, but I keep getting syntax errors, so I guess I'm doing it all wrong.

Can you help me?
Thank you very much!

 

[plog]

Is this purely a calculator (just an interface) or does the data get saved to table(s)?

If its purely a calculator, you will use VBA to run through all your checkboxes, determine the score and then spit that out some place on the form.

If this data is going to a table(s), then you need to properly configure your tables to handle this data. Then, you create a query to generate the score.

 

[isladogs]

Have you considered using a reference table with Description and Score fields?
Then use DLookup to obtain the score for any description.

That way you can add more records at any time without changing your code.

You can certainly use both Iif and Switch to do this but each time you add any more items you will need to modify your code

 

[dafne]

Hi, plog!

Is this purely a calculator (just an interface) or does the data get saved to table(s)?

I think I have to save it to the table where all my other answers are stored, because I want to do statistical analysis later and it's easier to work with just one value (e.g. "high salience") than with the result of dozens of checkboxes.

If this data is going to a table(s), then you need to properly configure your tables to handle this data. Then, you create a query to generate the score.

How can I do it?
Thank you.

 

[The_Doc_Man]

If there is a form guiding this, then your issues are probably related to where you are keeping this information and when you are gathering it.

From your description, you have several check boxes on the same form and want a numerical total of boxes checked. It is not clear whether the check boxes are recorded in your underlying table or if you only track the totals. Therefore, I'll take the simplest approach and tell you how to count these boxes.

In your form design, you have the option to add what is called a Class Module. If you have ANY event-based code on that form, you already have one. So get into the Class Module and create a function. It MIGHT look like this, but what I'll show you is not the only way to do this.

Function CountChecks() As Integer

Dim TotChecks as Integer

    TotChecks = 0

    If [ckAnimate] = True Then TotChecks = TotChecks + 1
    If [ckHuman] = True Then TotChecks = TotChecks + 1
...
    If [ckDefinite] = True Then ToTChecks = TotChecks + 1
...

    CountChecks = TotChecks

End Function

Then for each of the check boxes, create a LostFocus event (works) or a Change event (probably works better) with this code. I'll write it generically but if you do it the way I suggest, Access might build it slightly differently than this. In form design, select one of the check boxes. Then have the Forms Designer show you the properties of that control and select the tab that says Events. Select either LostFocus or Change and select the drop-down to say Event Procedure. Then double-click that box. Access will create an empty VBA routine for you. You fill it in like this.

Sub ckAnimate_Change()

Dim Boxes As Integer

    Boxes = CountChecks

    Select Case Boxes
        Case 0 To 3
            [Salience] = "Low"
        Case 4 To 7
            [Salience] = "Medium"
        Case 8 To 11
            [Salience] = "High"
        Case Else
            [Salience] = "N/A"
    End Select

End Sub

Looks like a lot of typing, right? BUT you can simply copy/paste everything between (but not including) the Sub and End Sub lines, pasting once for each check box.

I wrote it this way because I wasn't sure what you wanted to preserve. This way gives you the chance to preserve the totals separately from your salience rating and the values of the check boxes themselves. However, this is NOT the most efficient way to get the salience value. It is just the way that preserves the most options.

 

[dafne]

Hi, ridders!

Have you considered using a reference table with Description and Score fields?
Then use DLookup to obtain the score for any description.

That way you can add more records at any time without changing your code.

You can certainly use both Iif and Switch to do this but each time you add any more items you will need to modify your code

Oh, I'm not an expert at all, and I only mentioned those because I saw them mentioned in other forums.
I'm mostly having trouble with the order in which I should be doing things.

 

[plog]

The order in which you should do things is this:

Set up tables
Build reports and the queries to get your data out
Build input forms

My gut is telling me your tables aren't set up properly for this. I'm guessing you have one big table with columns for every check box. That's probably not how this should be accomplished.

I suggest doing 2 things for us:

1. Give us a plain english explanation of what this data is to do. No database jargon, pretend its career day at an elementary school and you have to tell the kids what it is you do.

2. Set up your relationship tool, make sure all fields in every table are visible and then post a screenshot here for us to view.

 

[dafne]

Hi, The_Doc_Man! Thank you for your very detailed answer.

If there is a form guiding this, then your issues are probably related to where you are keeping this information and when you are gathering it.

From your description, you have several check boxes on the same form and want a numerical total of boxes checked. It is not clear whether the check boxes are recorded in your underlying table or if you only track the totals. Therefore, I'll take the simplest approach and tell you how to count these boxes.

Yes, I do have several check boxes on the same form. The check boxes are indeed recorded in my underlying table. I'd like to keep the results of the individual check boxes there, but I'd also like to calculate the degree of salience of each record (which is the value I'm going to be working with later in my statistical analysis). Does this change your answer?

 

[dafne]

Hi, plog. Thank you for taking your time to help me again.

The order in which you should do things is this:

Set up tables
Build reports and the queries to get your data out
Build input forms

My gut is telling me your tables aren't set up properly for this. I'm guessing you have one big table with columns for every check box. That's probably not how this should be accomplished.

Yes, I do! You got me!
I need to analyze lots of criteria. Every control in my form populates the same table, including my check boxes.

I suggest doing 2 things for us:

1. Give us a plain english explanation of what this data is to do. No database jargon, pretend its career day at an elementary school and you have to tell the kids what it is you do.

Well, let me see. Imagine the sentence "My cat died last week"; I'm interested in its subject, which is "cat". Here, "cat" is "animate", "concrete", "definite", "referential", "countable" and "individual", but it is not "human" nor "proper". I have 6 checked check boxes but 2 of them are not; the subject in this sentence has a salience score of 6 (actually, it's 9, because it's also "explicit", it's a "noun" and it's "singular", but those answers come from combo boxes, not check boxes, and I wanted to focus on the latter first).
It's interesting to have all these very refined categories, but categorizing salience would allow me to assemble categories and examples, and that would simplify my statistical analysis (I guess I failed career day, sorry).

2. Set up your relationship tool, make sure all fields in every table are visible and then post a screenshot here for us to view.

I'm not sure I understand what you mean, I'm sorry. I'm mostly self-taught and sometimes I grasp with terminology.

 

[plog]

1. That's a good explanation. Now, will you be saving 'Cat' in the database somewhere? The entire sentence?

Also, what will the future hold? Could it be possible new categories could be added?
How frequently would that occur?


2. For the relationship tool starting at the top of the screen in Access--

DATABASE TOOLS -> Relationships

Bring in all your tables and then join them where appropriate.

 

[dafne]

1. That's a good explanation. Now, will you be saving 'Cat' in the database somewhere? The entire sentence?

Also, what will the future hold? Could it be possible new categories could be added?
How frequently would that occur?

Thank you! Yes, the entire sentence is stored in a specific field of my records table. New records may be added later (I'm done with it for now, though), but the list of categories is pretty much closed (I already have more or less 40 criteria to analyze and my DB contains 2300 records, so every time I add a field or change something, my workload increases immensely. Is that what you were asking?

2. For the relationship tool starting at the top of the screen in Access--

DATABASE TOOLS -> Relationships

Bring in all your tables and then join them where appropriate.

Oh, I knew about that, actually. I have a lot of tables (they feed the options to the combo boxes that populate the fields in my tbl_RECORDS), so I'm sending a screenshot of two relationships I've created as an example. I'm not sure how to fit my check boxes into this.

 

[Pat Hartman]

so every time I add a field or change something, my workload increases immensely

Only because your schema is not normalized. If you add a new question to a properly normalized questionnaire, NO WORK IS REQUIRED. Everything just works, your forms, your reports, your queries, your calculations - EVERYTHING. And, if you thought ahead a little. You can add a completely new survey in the time it takes you to add the new questions.

If this survey is done, then there isn't any reason to change it. However, if you need to do another or use this one again, you might consider starting from scratch.

 

[dafne]

Only because your schema is not normalized. If you add a new question to a properly normalized questionnaire, NO WORK IS REQUIRED. Everything just works, your forms, your reports, your queries, your calculations - EVERYTHING. And, if you thought ahead a little. You can add a completely new survey in the time it takes you to add the new questions.

If this survey is done, then there isn't any reason to change it. However, if you need to do another or use this one again, you might consider starting from scratch.

Hi, Pat.
What I meant is that if I add a new category to analyze, I have to do it for every record in my DB. This is not something Access can do through a calculation - e.g. it can’t tell that “cat” in “My cat died last week” is “referential”. If it could, they wouldn’t need me to do it!

 

[plog]

Using your first post, let's say you have 4 categories-animate, human, concrete, definite. Those should be values in fields, not be the names of fields. You have this:

record_ID, Animate, Human, Concrete, Definte...
14, True, False, True, True

Instead, it should be stored as such:

CategoryID, ParentID, Category
11, 14, Animate
12, 14, Concrete
13, 14, Definite


CategoryId is the autonumber primary key of the table (like record_ID in your existing one). ParentId is a link back to the record_ID value of your existing table so the system knows what record all these values go to. Category holds all the category values that are true for that record.

That's how your database should be structured. Then, when you want to add a new category you simply add a new record to that table for it without touching the structure. With a consistent structure its so much easier to build on top of them the reports and forms your system uses.

Additionally, i would suggest you not use check boxes to hold this data. Instead I would use a subform where the appropriate categories are selected from a drop down and added to this new table I describe.

 

[The_Doc_Man]

Does this change your answer?

If you keep your form structured as-is, no major changes, just details such as the names of the actual variables as opposed to what I used.

HOWEVER, Pat and plog are not wrong to suggest that if you change the DB to remember ANOTHER category besides "human" "definite" "animate" etc. you would do better to normalize your data. The difference is whether there is potential for growth of categories and whether this is a short-term or long-term endeavor. If this were a one-off-and-done database, don't sweat it. If you are talking longer than a couple of months of activity, start thinking about (and READING about) database normalization. The longer you want to track this data, the better off you will be by normalizing it.

The form to store this stuff gets SLIGHTLY more complex - but not seriously so - but the queries needed to make these counts just jump out at you become trivially simple.

 

[Pat Hartman]

dafne, we may not be taling about the same thing. Let's say you have a table of students and when you start you want to record three attributes - Gender, Age, Language. Then someone gets the bright idea (after you're done) that they also need Ethnecitiy. Well, if you created a single table with three combos, you now have to add a fourth combo and change every single other query, form, report, and calculation, etc.. PLUS gather the relevant data.

However if you had used the normalized three table schema - Students, Attributes, StudentAttributes, You would add a new row to Attributes so it would go from three rows to four rows. Then you do have to gather the information by student and enter it. BUT, you don't have to change any queries, forms, reports, and calculations, etc, That's the difference. You cannot avoid the data collection regardless of what you do. Without the data you have nothing to analyze but you can protect yourself from programming changes by proper design.

 

[dafne]

Dear all,
Thank you, thank you for your precious advice. I'll try to make the changes you suggested and I'll get back to you!

 

[dafne]

Using your first post, let's say you have 4 categories-animate, human, concrete, definite. Those should be values in fields, not be the names of fields. You have this:

record_ID, Animate, Human, Concrete, Definte...
14, True, False, True, True

Instead, it should be stored as such:

CategoryID, ParentID, Category
11, 14, Animate
12, 14, Concrete
13, 14, Definite


CategoryId is the autonumber primary key of the table (like record_ID in your existing one). ParentId is a link back to the record_ID value of your existing table so the system knows what record all these values go to. Category holds all the category values that are true for that record.

Hi,
I've been reading this tutorial and I've already made a few changes on my DB (I already had separate tables for most of my categories but no relationships). Here is an example of what I got:
1) a table "tbl_records" containing the fields a) record_ID, b) record_text, c) variety_ID
2) a table "tbl_variety" containing the fields a) variety_ID, b) variety_name (= Brazilian Portuguese, European Portuguese)
3) a one-to-many relationship between the variety_ID field from tbl_variety and its counterpart from tbl_records.

It feels like something's missing, though (namely, a reference to "record_ID" in my tbl_variety).

Also, when you say:

Instead, it should be stored as such:

CategoryID, ParentID, Category
11, 14, Animate
12, 14, Concrete
13, 14, Definite

You mean I should have something like:
NP_feature_ID | NP_feature_name
1 | animate
2 | non_animate
3 | human
4 | non_human
... and so on, plus a reference to the record number?

Also, will I have to create a different dropdown for every (pair of) feature(s) in the subform? Finally, the analysis of every semantic feature may not be relevant to the record in question: I have to account for my "NAs", but where?

Thank you all for your help.

 

[plog]

Yes you will have a reference to the main record in your Category table.

Formwise, you would have just 1 drop down on your subform. It would be a conitnous subform, meaning that once they use one drop down, a new record/dropdown will appear for them to enter an additional category.

The N/a values can be determined via a query. Using a LEFT JOIN to a table with all categories you will be able to discern which ones are missing.

 

[dafne]

Yes you will have a reference to the main record in your Category table.

Thanks, plog. How do I do this? Do I create a relationship between tbl_record's record_ID and tbl_features's record_ID? Or is tbl_feature's record_ID a lookup field?
I'm uploading a snapshot of the feature's table.