Need report to show totals and percentages

C

CEV

I am not sure how to get the numbers I am looking for. I have a query that
shows all the records I need totals for. The numbers I need on a report are
the total number of records entered for each OutcomeNumber. Then I need the
total number of "Yes" and "No" for each OutcomeNumber in the field Outcome
and also in the field OutcomeSupports. Finally I need to show the percentage
of Yes's and No's for each. I have listed my query below and would greatly
appreciate help with this as I have never worked with calculations in Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad
 
G

Guest

Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date range
defined in the form', and you'll be grouping the query by OutcomeNumber. To
count all rows per group use the COUNT function, to count 'yes' or 'no' rows
use the SUM function to sum the return value of an expression which evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd group
the report on OutcomeNumber and use similar expressions to those in the above
query as the ControlSource properties of unbound text box controls in the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

CEV said:
I am not sure how to get the numbers I am looking for. I have a query that
shows all the records I need totals for. The numbers I need on a report are
the total number of records entered for each OutcomeNumber. Then I need the
total number of "Yes" and "No" for each OutcomeNumber in the field Outcome
and also in the field OutcomeSupports. Finally I need to show the percentage
of Yes's and No's for each. I have listed my query below and would greatly
appreciate help with this as I have never worked with calculations in Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
 
K

kingston via AccessMonster.com

If your query returns all the records you need, create a totals query based
on it. Group by OutcomeNumber and Outcome, and count on the field Outcome.
Use this query for your report and add a field that divides the count by the
total number of answers for a given Outcome. Display this as a percentage or
multiply by 100.

Denominator=DCount("[Outcome]","[YourQuery]","[OutcomeNumber]=" &
ReportOutcomeNumber)

You can do the same thing for OutcomeSupports. hth
I am not sure how to get the numbers I am looking for. I have a query that
shows all the records I need totals for. The numbers I need on a report are
the total number of records entered for each OutcomeNumber. Then I need the
total number of "Yes" and "No" for each OutcomeNumber in the field Outcome
and also in the field OutcomeSupports. Finally I need to show the percentage
of Yes's and No's for each. I have listed my query below and would greatly
appreciate help with this as I have never worked with calculations in Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));

Thank You,

Chad
Click to expand...
 
C

CEV

Thank you very much Ken for the fast reply. I am currently playing with this
query before using it in a report to be sure I am getting what I need. I
left the DateOfPlan in there so I can select a range of dates and it is
looking like it works. I am currently having only one issue with it and I'm
thinking it should be simple, hopefully. The Outcome field is not a Boolean
data type, but a text type. The reason is that there is one additional
option besides Yes and No and that is RNC. So I am wondering if that part of
the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest of
the query looked like.

Thank You,

Chad

Ken Sheridan said:
Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health."
Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

CEV said:
I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health."
Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
 
C

CEV

Well, that is obliously not correct since it does not work. I'm not sure how
else I would write it.

Thanks,

Chad

CEV said:
Thank you very much Ken for the fast reply. I am currently playing with
this query before using it in a report to be sure I am getting what I
need. I left the DateOfPlan in there so I can select a range of dates and
it is looking like it works. I am currently having only one issue with it
and I'm thinking it should be simple, hopefully. The Outcome field is not
a Boolean data type, but a text type. The reason is that there is one
additional option besides Yes and No and that is RNC. So I am wondering if
that part of the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest
of the query looked like.

Thank You,

Chad

Ken Sheridan said:
Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health."
Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

CEV said:
I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Sum(IIF(Outcome="Yes",1,Null)) as CountYes

If tblOutcomes.Outcome is a text field that has a value of "Yes", "No",
or "RNC" then you could use something like the following to Count Yes

Count(IIF(Outcome="Yes",1,Null)) as CountYes

Count(IIF(Outcome="Yes",1,NuLL))/ Count(Outcome) as YesPercent

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you very much Ken for the fast reply. I am currently playing with this
query before using it in a report to be sure I am getting what I need. I
left the DateOfPlan in there so I can select a range of dates and it is
looking like it works. I am currently having only one issue with it and I'm
thinking it should be simple, hopefully. The Outcome field is not a Boolean
data type, but a text type. The reason is that there is one additional
option besides Yes and No and that is RNC. So I am wondering if that part of
the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest of
the query looked like.

Thank You,

Chad

Ken Sheridan said:
Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health."
Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

CEV said:
I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health."
Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

The problem is that I also need to count how many "No" and how many "RNC"
there are and this is not working either. I would think there has to be a
way but the suggestions I'm getting so far are not working. Please keep them
coming.

Thank You,

Chad

John Spencer said:
Sum(IIF(Outcome="Yes",1,Null)) as CountYes

If tblOutcomes.Outcome is a text field that has a value of "Yes", "No", or
"RNC" then you could use something like the following to Count Yes

Count(IIF(Outcome="Yes",1,Null)) as CountYes

Count(IIF(Outcome="Yes",1,NuLL))/ Count(Outcome) as YesPercent

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you very much Ken for the fast reply. I am currently playing with
this query before using it in a report to be sure I am getting what I
need. I left the DateOfPlan in there so I can select a range of dates and
it is looking like it works. I am currently having only one issue with it
and I'm thinking it should be simple, hopefully. The Outcome field is not
a Boolean data type, but a text type. The reason is that there is one
additional option besides Yes and No and that is RNC. So I am wondering
if that part of the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest
of the query looked like.

Thank You,

Chad

Ken Sheridan said:
Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in
the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

:

I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

I think I may of just got it. I entered the following and I get no errors.
I'll need to manualy check the numbers to see if they match but so far it is
looking good. I'll post back when I have confirmed.

count(IIF(tblOutcomes.Outcome="Yes",1)) AS YesOutcomeCount,
(count(IIF(tblOutcomes.Outcome="Yes",1))/COUNT(*))*100 AS
YesOutcomePercentage,
count(IIF(tblOutcomes.Outcome="No",0)) AS NoOutcomeCount,
(count(IIF(tblOutcomes.Outcome="No",0))/COUNT(*))*100 AS
NoOutcomePercentage,
count(IIF(tblOutcomes.Outcome="RNC",3)) AS RNCOutcomeCount,
(count(IIF(tblOutcomes.Outcome="RNC",3))/COUNT(*))*100 AS
RNCOutcomePercentage

Thanks,

Chad

John Spencer said:
Sum(IIF(Outcome="Yes",1,Null)) as CountYes

If tblOutcomes.Outcome is a text field that has a value of "Yes", "No", or
"RNC" then you could use something like the following to Count Yes

Count(IIF(Outcome="Yes",1,Null)) as CountYes

Count(IIF(Outcome="Yes",1,NuLL))/ Count(Outcome) as YesPercent

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you very much Ken for the fast reply. I am currently playing with
this query before using it in a report to be sure I am getting what I
need. I left the DateOfPlan in there so I can select a range of dates and
it is looking like it works. I am currently having only one issue with it
and I'm thinking it should be simple, hopefully. The Outcome field is not
a Boolean data type, but a text type. The reason is that there is one
additional option besides Yes and No and that is RNC. So I am wondering
if that part of the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest
of the query looked like.

Thank You,

Chad

Ken Sheridan said:
Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in
the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

:

I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

For safety's sake, I would add the third argument to the IIF statements.
However, you are probably all right doing it as you have done it.


Count(IIF(TblOutcomes.Outcome ="Yes",1,NULL)) as YesOutcomeCount



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I think I may of just got it. I entered the following and I get no errors.
I'll need to manualy check the numbers to see if they match but so far it is
looking good. I'll post back when I have confirmed.

count(IIF(tblOutcomes.Outcome="Yes",1)) AS YesOutcomeCount,
(count(IIF(tblOutcomes.Outcome="Yes",1))/COUNT(*))*100 AS
YesOutcomePercentage,
count(IIF(tblOutcomes.Outcome="No",0)) AS NoOutcomeCount,
(count(IIF(tblOutcomes.Outcome="No",0))/COUNT(*))*100 AS
NoOutcomePercentage,
count(IIF(tblOutcomes.Outcome="RNC",3)) AS RNCOutcomeCount,
(count(IIF(tblOutcomes.Outcome="RNC",3))/COUNT(*))*100 AS
RNCOutcomePercentage

Thanks,

Chad

John Spencer said:
Sum(IIF(Outcome="Yes",1,Null)) as CountYes

If tblOutcomes.Outcome is a text field that has a value of "Yes", "No", or
"RNC" then you could use something like the following to Count Yes

Count(IIF(Outcome="Yes",1,Null)) as CountYes

Count(IIF(Outcome="Yes",1,NuLL))/ Count(Outcome) as YesPercent

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you very much Ken for the fast reply. I am currently playing with
this query before using it in a report to be sure I am getting what I
need. I left the DateOfPlan in there so I can select a range of dates and
it is looking like it works. I am currently having only one issue with it
and I'm thinking it should be simple, hopefully. The Outcome field is not
a Boolean data type, but a text type. The reason is that there is one
additional option besides Yes and No and that is RNC. So I am wondering
if that part of the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest
of the query looked like.

Thank You,

Chad

Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in
the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

:

I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

count(IIF(tblOutcomes.Outcome="Yes",1)) AS YesOutcomeCount,
(count(IIF(tblOutcomes.Outcome="Yes",1))/COUNT(*))*100 AS
YesOutcomePercentage,
count(IIF(tblOutcomes.Outcome="No",0)) AS NoOutcomeCount,
(count(IIF(tblOutcomes.Outcome="No",0))/COUNT(*))*100 AS
NoOutcomePercentage,
count(IIF(tblOutcomes.Outcome="RNC",3)) AS RNCOutcomeCount,
(count(IIF(tblOutcomes.Outcome="RNC",3))/COUNT(*))*100 AS
RNCOutcomePercentage
Click to expand...
or maybe this:

SUM(IIF(tblOutcomes.Outcome="Yes",1,0)) AS YesOutcomeCount,
SUM(IIF(tblOutcomes.Outcome="Yes",1,0))/COUNT(*)*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome="No",1,0)) AS NoOutcomeCount,
SUM(IIF(tblOutcomes.Outcome="No",1,0))/COUNT(*)*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome="RNC",1,0)) AS RNCOutcomeCount,
SUM(IIF(tblOutcomes.Outcome="RNC",1,0))/COUNT(*)*100 AS
RNCOutcomePercentage
 
G

Guest

My guess would be that the columns are not of Boolean data type, but text.
You'll note that my reply assumed the former.

Ken Sheridan
Stafford, England

CEV said:
Well, that is obliously not correct since it does not work. I'm not sure how
else I would write it.

Thanks,

Chad

CEV said:
Thank you very much Ken for the fast reply. I am currently playing with
this query before using it in a report to be sure I am getting what I
need. I left the DateOfPlan in there so I can select a range of dates and
it is looking like it works. I am currently having only one issue with it
and I'm thinking it should be simple, hopefully. The Outcome field is not
a Boolean data type, but a text type. The reason is that there is one
additional option besides Yes and No and that is RNC. So I am wondering if
that part of the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the rest
of the query looked like.

Thank You,

Chad

Ken Sheridan said:
Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by OutcomeNumber.
To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible health."
Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in the
group footer.

You might find the SQL for the query above is broken up somewhat in your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

:

I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a report
are
the total number of records entered for each OutcomeNumber. Then I need
the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

This has worked for me so far. I greatly appreciate everyones input. This
learning experience will benefit me in another database I have for turnover.
I also need to do calculations in that one. I have still not figured out how
the arguments determine the outcome of the query yet but am hoping to get
that soon so I can understand it.

Thanks,

Chad

CEV said:
I think I may of just got it. I entered the following and I get no errors.
I'll need to manualy check the numbers to see if they match but so far it
is looking good. I'll post back when I have confirmed.

count(IIF(tblOutcomes.Outcome="Yes",1)) AS YesOutcomeCount,
(count(IIF(tblOutcomes.Outcome="Yes",1))/COUNT(*))*100 AS
YesOutcomePercentage,
count(IIF(tblOutcomes.Outcome="No",0)) AS NoOutcomeCount,
(count(IIF(tblOutcomes.Outcome="No",0))/COUNT(*))*100 AS
NoOutcomePercentage,
count(IIF(tblOutcomes.Outcome="RNC",3)) AS RNCOutcomeCount,
(count(IIF(tblOutcomes.Outcome="RNC",3))/COUNT(*))*100 AS
RNCOutcomePercentage

Thanks,

Chad

John Spencer said:
Sum(IIF(Outcome="Yes",1,Null)) as CountYes

If tblOutcomes.Outcome is a text field that has a value of "Yes", "No",
or "RNC" then you could use something like the following to Count Yes

Count(IIF(Outcome="Yes",1,Null)) as CountYes

Count(IIF(Outcome="Yes",1,NuLL))/ Count(Outcome) as YesPercent

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you very much Ken for the fast reply. I am currently playing with
this query before using it in a report to be sure I am getting what I
need. I left the DateOfPlan in there so I can select a range of dates
and it is looking like it works. I am currently having only one issue
with it and I'm thinking it should be simple, hopefully. The Outcome
field is not a Boolean data type, but a text type. The reason is that
there is one additional option besides Yes and No and that is RNC. So I
am wondering if that part of the query would look like the following?:

SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,Yes,No,RNC))/COUNT(*))*100 AS
YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,No,Yes,RNC))/COUNT(*))*100 AS
NoOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No)) AS RNCOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,RNC,Yes,No))/COUNT(*))*100 AS
RNCOutcomePercentage,

I'm sorry if this is not even close but I was just going by what the
rest of the query looked like.

Thank You,

Chad

Chad:

You can do the aggregation either in the query or in the report itself,
using your current query as it stands.

To do it in the query first remove the DateOfPlan column from the
SELECT
clause as its purely needed to restrict the rows returned to the date
range
defined in the form', and you'll be grouping the query by
OutcomeNumber. To
count all rows per group use the COUNT function, to count 'yes' or 'no'
rows
use the SUM function to sum the return value of an expression which
evaluates
to 1 or 0 depending on the value in the column. The percentages are
the
ratio of the each of the latter to the total count. I'm assuming the
Outcome
and OutcomeSupports columns are of Boolean (Yes/no) data type. So the
query
would go like this:

SELECT tblOutcomes.OutcomeNumber,
COUNT(*) AS TotalCount,
SUM(IIF(tblOutcomes.Outcome,1,0)) AS YesOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,1,0))/COUNT(*))*100
AS YesOutcomePercentage,
SUM(IIF(tblOutcomes.Outcome,0,1)) AS NoOutcomeCount,
(SUM(IIF(tblOutcomes.Outcome,0,1))/COUNT(*))*100
AS NoOutcomePercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,1,0)) AS YesOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,1,0))/COUNT(*))*100
AS YesOutcomeSupportsPercentage,
SUM(IIF(tblOutcomes.OutcomeSupports,0,1)) AS NoOutcomeSupportsCount,
(SUM(IIF(tblOutcomes.OutcomeSupports,0,1))/COUNT(*))*100
AS NoOutcomeSupportsPercentage
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social Security
Number] = tblOutcomes.SSAN WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are safe."
Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People are
respected."))
GROUP BY tblOutcomes.OutcomeNumber;

You can then base the report on the above query.

To use your original query and do the aggregations in the report you'd
group
the report on OutcomeNumber and use similar expressions to those in the
above
query as the ControlSource properties of unbound text box controls in
the
group footer.

You might find the SQL for the query above is broken up somewhat in
your
newsreader. It should work fine nevertheless.

Ken Sheridan
Stafford, England

:

I am not sure how to get the numbers I am looking for. I have a query
that
shows all the records I need totals for. The numbers I need on a
report are
the total number of records entered for each OutcomeNumber. Then I
need the
total number of "Yes" and "No" for each OutcomeNumber in the field
Outcome
and also in the field OutcomeSupports. Finally I need to show the
percentage
of Yes's and No's for each. I have listed my query below and would
greatly
appreciate help with this as I have never worked with calculations in
Access
before.

SELECT tblOutcomes.DateofPlan, tblOutcomes.OutcomeNumber,
tblOutcomes.Outcome, tblOutcomes.OutcomeSupports
FROM Individuals INNER JOIN tblOutcomes ON Individuals.[Social
Security
Number] = tblOutcomes.SSAN
WHERE (((tblOutcomes.DateofPlan) Between
[Forms]![frmIndOutcomesReport]![cboBeginDate] And
DateAdd("s",86399,[Forms]![frmIndOutcomesReport]![cboEndDate])) AND
((tblOutcomes.OutcomeNumber)="O01 - People are connected to natural
support
networks." Or (tblOutcomes.OutcomeNumber)="O02 - People have intimate
relationships." Or (tblOutcomes.OutcomeNumber)="O03 - People are
safe." Or
(tblOutcomes.OutcomeNumber)="O04 - People have the best possible
health." Or
(tblOutcomes.OutcomeNumber)="O05 - People exercise rights." Or
(tblOutcomes.OutcomeNumber)="O06 - People are treated fairly." Or
(tblOutcomes.OutcomeNumber)="O07 - People are free from abuse and
neglect."
Or (tblOutcomes.OutcomeNumber)="O08 - People experience continuity and
security." Or (tblOutcomes.OutcomeNumber)="O09 - People decide when to
share
personal information." Or (tblOutcomes.OutcomeNumber)="O10 - People
are
respected."));


Thank You,

Chad


.
Click to expand...
Click to expand...
Click to expand...
 
Top