Re: Total of choice two
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Fri, 28 Oct 2005 06:43:25 -0400
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
"T Horner" <THorner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0B9E0386-089F-4521-A731-7F9ED602C2C5@xxxxxxxxxxxxxxxx
> 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:
>
>> 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
>>
>>
>> "T Horner" <THorner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:92B5210C-CA15-447E-9C19-7BD3DC45AF1C@xxxxxxxxxxxxxxxx
>> >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.
>>
>>
>>
.
- References:
- Re: Total of choice two
- From: Michel Walsh
- Re: Total of choice two
- From: T Horner
- Re: Total of choice two
- Prev by Date: Limtited Query by Field
- Next by Date: Re: Limtited Query by Field
- Previous by thread: Re: Total of choice two
- Next by thread: export a select query
- Index(es):
Relevant Pages
|
