Add 1 when T/F fields are true

R

Rod

Hello,

I have a field, [Priority]. I have 6 true/false fields, [TF1], [TF2],
[TF3], [TF4], [TF5], and [TF6]. I would like to count the number of time
[TFx] is true and store that value in [Priority].

My assumption is an update query is the best approach, so how could I cout
these [TFx] and store that in [Priority] so the query can be run whenever the
form is opened and the record's [Priority] stored?

Thanks much!
 
S

Smartin

Rod said:
Hello,

I have a field, [Priority]. I have 6 true/false fields, [TF1], [TF2],
[TF3], [TF4], [TF5], and [TF6]. I would like to count the number of time
[TFx] is true and store that value in [Priority].

My assumption is an update query is the best approach, so how could I cout
these [TFx] and store that in [Priority] so the query can be run whenever the
form is opened and the record's [Priority] stored?

Thanks much!
Click to expand...

Hello Rod,

Here is one way:

Priority = (
IIf(TF1 = True,1,0) +
IIf(TF2 = True,1,0) +
IIf(TF3 = True,1,0) +
IIf(TF4 = True,1,0) +
IIf(TF5 = True,1,0) +
IIf(TF6 = True,1,0)
)

Now please consider this: storing such a calculated value in the table
is not a good idea, for good reasons. Since you want to show Priority in
a form, calculating the value is entirely redundant, even.

All you need to do is place an unbound text field on the form and use
the forms OnCurrent event to fire a query that calculates the current
Priority value.

We could also discuss how your table is not normalized (read: design
flaw) because you are storing related information in fields instead of
records. This often leads to trouble and awkward query constructs (note
the repetition) like the solution I gave above. While you may be
forgiven for only having to test six fields, should you ever add or
remove a true/false criterion the query will have to be modified. A
better design is to remove the True/False criteria to a separate,
related table. Then you could write a query to sum up the True entries
that is not dependent on the number of questions or answers.

Best Regards,
 
A

Allen Browne

Do not store the Priority in your table.
Instead, ask Access to get the total for you.

1. Create a query using this table.

2. Type an expression like this into the Field row:
Priority: - ([TF1] + [TF2] + [TF3] + ...)

This relies on the fact that Access uses -1 for True, and 0 for False.
Therefore summing the fields gives the negative total of the fields checked.

Doing this in a query is much safer than doing it in a table. You know the
answer is always right. That's why one of the basic rules of data
normalization is to never store depedent data.

In a relational database, there might be a better way to store the data than
6 check boxes too, though we can't be sure as we don't really know what this
is for. If you are trying to store information about which of the 6 things
apply to the record in the main table, it would be better to use a lookup
table (with 6 records) and a junction table to record just the items that
apply.
 
Top