Counting 2 different fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CEV - 10 Apr 2007 23:05 GMT

I have the following query setup to count how many of each goal.

SELECT tblGoals.Goal, Count(*) AS TotalCount,
Count(IIf(tblGoals.Goal="Relationship",0)) AS RelationshipGoalCount,
Count(IIf(tblGoals.Goal="Travel",1)) AS TravelGoalCount,
Count(IIf(tblGoals.Goal="Residence",2)) AS ResidenceGoalCount,
Count(IIf(tblGoals.Goal="Employment",3)) AS EmploymentGoalCount,
Count(IIf(tblGoals.Goal="Living Skills",4)) AS LivingSkillsGoalCount,
Count(IIf(tblGoals.Goal="Education",5)) AS EducationGoalCount,
Count(IIf(tblGoals.Goal="Communication",6)) AS CommunicationGoalCount,
Count(IIf(tblGoals.Goal="Health",7)) AS HealthGoalCount,
Count(IIf(tblGoals.Goal="Recreation",8)) AS RecreationGoalCount,
Count(IIf(tblGoals.Goal="Financial",9)) AS FinancialGoalCount FROM tblGoals
WHERE (((tblGoals.DateofPlan) Between Forms!frmOutcomesReports!cboBeginDate
And DateAdd("s",86399,Forms!frmOutcomesReports!cboEndDate))
And ((tblGoals.Goal)="Relationship" Or
(tblGoals.Goal)="Travel" Or
(tblGoals.Goal)="Residence" Or
(tblGoals.Goal)="Employment" Or
(tblGoals.Goal)="Living Skills" Or
(tblGoals.Goal)="Education" Or
(tblGoals.Goal)="Communication" Or
(tblGoals.Goal)="Health" Or
(tblGoals.Goal)="Recreation" Or
(tblGoals.Goal)="Financial"))
GROUP BY tblGoals.Goal;

This query is currently working, but I would like to add additional items to
count. Each Goal has a GoalProgress field with 8 different possible answers.
Now that I have the total number of each Goal, for example 40 Travel, I
would like to know how many of those 40 have a GoalProgress of "Yes","No",
"In Progress", etc, etc. and the percentage of each. If someone could please
show me what the first couple of lines should look like, I can enter the
criteria for all the options.

Thank You,

CEV

Reply to this Message

John W. Vinson - 10 Apr 2007 23:42 GMT

>SELECT tblGoals.Goal, Count(*) AS TotalCount,
>Count(IIf(tblGoals.Goal="Relationship",0)) AS RelationshipGoalCount,
[quoted text clipped - 7 lines]
>Count(IIf(tblGoals.Goal="Recreation",8)) AS RecreationGoalCount,
>Count(IIf(tblGoals.Goal="Financial",9)) AS FinancialGoalCount FROM tblGoals

The problem is that the Count function doesn't count values - it counts
*records*.

Replace these with expressions like

Sum(IIF([Goal] = "Relationship", 1, 0))

This will add 1 for each record having a goal of Relationship, and zero for
all other records.

John W. Vinson [MVP]

Reply to this Message

KARL DEWEY - 11 Apr 2007 01:30 GMT

Try this ---
SELECT tblGoals.Goal, Count(tblGoals.Goal) AS CountOfGoal
FROM tblGoals
WHERE (((tblGoals.DateofPlan) Between
[Forms]![frmOutcomesReports]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmOutcomesReports]![cboEndDate])))
GROUP BY tblGoals.Goal;

Signature

KARL DEWEY
Build a little - Test a little

> I have the following query setup to count how many of each goal.
>
[quoted text clipped - 34 lines]
>
> CEV

Reply to this Message

CEV - 11 Apr 2007 18:26 GMT

My current query is already giving me the total number of each goal. What I
am now trying to do is find out how many of the "Relationship" goals have a
GoalProgress of "Yes", how many have a "No", how many have a "In Progress"
and so on. I need to do that for each of the 10 Goals. If you could please
show me what that would look like, I would greatly appreciate it.

Thanks,

CEV

> Try this ---
> SELECT tblGoals.Goal, Count(tblGoals.Goal) AS CountOfGoal
[quoted text clipped - 48 lines]
>>
>> CEV

Reply to this Message

KARL DEWEY - 11 Apr 2007 20:16 GMT

Try this ---
SELECT tblGoals.Goal, Count(tblGoals.Goal) AS CountOfGoal,
tblGoals.GoalProgress
FROM tblGoals
WHERE (((tblGoals.DateofPlan) Between
[Forms]![frmOutcomesReports]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmOutcomesReports]![cboEndDate])))
GROUP BY tblGoals.Goal, tblGoals.GoalProgress;

Signature

KARL DEWEY
Build a little - Test a little

> My current query is already giving me the total number of each goal. What I
> am now trying to do is find out how many of the "Relationship" goals have a
[quoted text clipped - 58 lines]
> >>
> >> CEV

Reply to this Message