Counting 2 different fields

C

CEV

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
 
J

John W. Vinson

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
Click to expand...

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]
 
G

Guest

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;
 
C

CEV

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

KARL DEWEY said:
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;

--
KARL DEWEY
Build a little - Test a little


CEV said:
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
Click to expand...
Click to expand...
 
G

Guest

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;

--
KARL DEWEY
Build a little - Test a little


CEV said:
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

KARL DEWEY said:
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;

--
KARL DEWEY
Build a little - Test a little


CEV said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top