Total of choice two

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

Guest

I have a table that records a value of 0, 1, or 2 for about 25 fields. I need
to know the total number of 1's and the total number of 2's for each entry. I
was looking at a calculated field in a query but I don't know how to set it
up.
 
Could you provide an example of the fields/data? When the task is
counting/summing ACROSS fields, there's a good chance you've committed
spreadsheet on Access. This is not a good thing, as it means your table
could benefit from further normalization ... and you'd get better use out of
the tools Access provides.

If what you are trying to do works well in Excel, is there a reason you
aren't using a spreadsheet?!
 
It sounds as if your table structure is incorrect and could be normalized.
Assuming that you are stuck with the current structure there are two ways to
solve your problem.

Method A: Use calculated fields in a totals query.
SELECT Abs(Sum(FieldA=2)) as FieldA2, Abs(Sum(FieldB=2)) as FieldB2, ...
FROM YourTable

Method B: Normalize the data using a UNION query and then use that as the
source for your totals queries
QueryNormal
SELECT FieldA as TheValue, "FieldA" as MyFieldName FROM YourTable
UNION ALL
SELECT FieldB, "FieldB" FROM YourTable
UNION ALL
SELECT FieldC, "FieldC" FROM YourTable
....

Then using that Saved query
SELECT MyFieldName, TheValue, Count(TheValue) as CountValue
FROM QueryNormal
WHERE TheValue = 2 '<-- This line is optional and can be dropped
GROUP BY MyFieldName, TheValue

The WHERE line is optional and would only be needed if you wanted to count
only twos, or ones, or zeroes.
 
Hi,


Your data does not sound to be normalized, but lucky you, you MAY be able to
use a crosstab, directly, to reach the result you seek:

TRANSFORM Nz(COUNT(*), 0)
SELECT "total"
FROM myTable, PossibleChoices
WHERE d IN(f1, f2, f3, f4, f5)
GROUP BY "total"
PIVOT d


There are 2 tables, the original table, with fields( id, f1, f2, f3, f4,
f5) -- yours go up to 25, change the IN() clause appropriately

and table PossibleChoices, one field, d, with as many record as there is
possible choice (ie, if there is 100 possible choices, that makes 100
records).

The query returns the number of occurrences of each choice, "by id"... and
unless a choice is NEVER used, the choice will appear under a field with its
"name". In:


Query61
Expr1002 1 2 3 4 5 6 7 9
total 3 2 1 1 2 1 2 2




you can observe that choice "8" is missing, because no initial record ever
speaks of it, in my data.


I also ASSUMED there is not duplicated selected choices, per original
record. If there are, the choice is counted once only.

Table32
ID f1 f2 f3 f4 f5
1 1 2 4 9 9


here, 9 is mentioned twice, but will be counted only once.



For more than that, you SHOULD go to a normalized table design.



Hoping it may help,
Vanderghast, Access MVP
 
The table is populated with the results of a form. My data are the results of
Observations. Each Entry is a single observation. Each field represents
different components of that observation. A Zero means that that component
was not observed. A 1 means that that component was correct. A 2 means that
that component was incorrect. I have a series of reports that return
percentages of incorrect components for each field. These reports use
Relationships to determine if certain areas are worse than others and so
forth.
I need to sum across these fields just to create a report that gives an
overall look. By seeing the number of incorrect components per entry, we can
see if the overall performance has increased over time or relationally to
over things. Because of the need for relationships, I am using Access rather
than Excel. This is the only place where I need to sum across fields.
 
T Horner said:
The table is populated with the results of a form. My data are the results of
Observations. Each Entry is a single observation. Each field represents
different components of that observation. A Zero means that that component
was not observed. A 1 means that that component was correct. A 2 means that
that component was incorrect. I have a series of reports that return
percentages of incorrect components for each field. These reports use
Relationships to determine if certain areas are worse than others and so
forth.
I need to sum across these fields just to create a report that gives an
overall look. By seeing the number of incorrect components per entry, we can
see if the overall performance has increased over time or relationally to
over things. Because of the need for relationships, I am using Access rather
than Excel. This is the only place where I need to sum across fields.

"Michel Walsh" wrote:
Click to expand...

T Horner,

The other solution, much more cumbersome than the crosstab query
mentioned earlier, is to write VBA code, create a recordset of your
query, and loop through every field, one row at a time.

This is terribly slow and defeats the general purpose of MS Access,
but it will work.


Sincerely,

Chris O.
 
Hi,


In addition to Jeff, John and Chris suggestions, you can try to simply make
a long ... long arithmetic expression:


SELECT id, iif( f1=2, 1, 0) + iif( f2=2, 1, 0 ) + iif( f3=2, 1, 0 ) + ...
+ iif( f25=2, 1, 0)
FROM myTable


It would be shorter if you worked on a normalized table:


SELECT id, COUNT(*)
FROM myNormalizedTable
WHERE CorrectnessCode = 2
GROUP BY id


assuming your table is like (fields name):


id Component CorrectnessCode





Hoping it may help,
Vanderghast, Access MVP
 
Back
Top