Counting in Access

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

Guest

I have a table that has eight fields of the 'Yes/No' datatype. Is it
possible to calculate the of 'Yes' entries for each record?
 
Sure. Try the following.

Field: CountThem: Abs(FieldA + FieldB + FieldC + FieldD + FieldE)
 
It can be done easily (John's method is one way) but I'd suggest standing
back for a moment to consider whether your design is a good one. Multiple
Boolean (Yes/No) columns in a table is usually a sign of a flaw in the design
(in the jargon its 'encoding data as column headings' which contravenes a
fundamental principle of the database relational model). This is a very
common mistake, particularly with questionnaire type databases.

A better design in such circumstances is to have a related table in which
you insert one row for each True (Yes) value in the Boolean columns in your
present design. To take a simplified and somewhat unrealistic example, say
you have a table of suppliers who might be located in London, New York, Paris
or Rome (you're a high class fashion store), and each could have branches in
one or more of these cities, so you have 4 Boolean columns in the table, each
column of the city name. This is not a good design. The correct design
would be to have a Suppliers table and a Cities table, the former with a
SupplierID primary key column and the latter with a SupplierID foreign key
column. So for a supplier with branches in Paris and Rome there would be two
rows in the Cities table with that supplier's SupplierID value, one row for
Rome, one for Paris.

Counting the number of cities per supplier is then a simple matter of a
query like this:

SELECT Supplier, COUNT(*) As CityCount
FROM Supplier INNER JOIN Cities
ON Suppliers.SupplierID = Cities.SupplierID
GROUP BY Supplier;

For data input the usual arrangement would be a Suppliers form in single
form view with a Cities subform in continuous form view within the main form,
linking the form and subform on SupplierID.

Even where the Boolean columns do not represent values of the same entity
type, as in the above example, where each is a value of the entity type
Cities, the same principles apply. In a questionnaire type database for
instance the referencing table would have columns Question and Answer, which
would in turn reference Questions and Answers tables.

Ken Sheridan
Stafford, England
 
Back
Top