Calculating control by specific criteria

[Kay Starnes]

I need to calculated a percentage for specific criteria on a report.

My table has various indicators Apples/Oranges/Grapes. Reviews are being
done on the individual indicators....so many Apples reviewed and so on. All
Apples/Oranges/Grapes reviewed are on the same table.

I have bad apples (shown with check boxes)....3 of 5 reviewed which should
be 60 % at group level. Because all indicators are on the same table I get 3
of 100 reviewed and a percent of 3%. I still will want the overall percent
to show 3% of all charts reviewed at the bottom but need to correct the
grouped percent.

Can you help be create an expression to give me % of the actual indicator at
the Group level?

Thanks!
K

 

[Ken Snell [MVP]]

If you put the textbox (with the percentage calculation) in the Group Footer
section, you should get the result that you wish for each group.

 

[Kay Starnes]

I have the percent in the Group Footer like this: =sum[not met])/([Not met
grand total]) so it goes to the Grand total which is ALL the reviews
completed and so I get a wrong %. How would I write it to see the total of
Apples when the Group is apples?

 

[Ken Snell [MVP]]

Your expression needs to use the Sum of the items in that group (regardless
of whether "met" or "not met"), not the grand total.

Add a hidden textbox to your Detail section (set its Visible property to
No). Name the textbox txtGroupSum. Set the textbox's Running Sum property to
Over Group. Set the textbox's Control Source to this expression:
= 1

Then change your expression to this:
=Sum([not met])/[txtGroupSum]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the percent in the Group Footer like this: =sum[not met])/([Not met
grand total]) so it goes to the Grand total which is ALL the reviews
completed and so I get a wrong %. How would I write it to see the total
of
Apples when the Group is apples?

If you put the textbox (with the percentage calculation) in the Group
Footer
section, you should get the result that you wish for each group.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Kay Starnes]

I put that created field into the group section as the percent and it is not
what I am looking for. It shows that the percent is zero. So I
investigated by putting the text box into the group area and it is giving me
a total of the records in that section which is 5. 5 records/5 records = 0%

I know I will use that later in life but for now, can you help be get the
Total on this section to be

=[not met]/ ????? to give me a Percent of records not meeting by total
records reviewed by criteria "apples"

20 record reviews for apples overall
30 for oranges
50 total reviewed (now in report footer as grand)

3 fell out of 5 for individual = 60% of personal total (got this)

(help) I need to also see 3/20 for apples reviewed for everyone 15% of
overall for individual.

Your expression needs to use the Sum of the items in that group (regardless
of whether "met" or "not met"), not the grand total.

Add a hidden textbox to your Detail section (set its Visible property to
No). Name the textbox txtGroupSum. Set the textbox's Running Sum property to
Over Group. Set the textbox's Control Source to this expression:
= 1

Then change your expression to this:
=Sum([not met])/[txtGroupSum]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the percent in the Group Footer like this: =sum[not met])/([Not met
grand total]) so it goes to the Grand total which is ALL the reviews
completed and so I get a wrong %. How would I write it to see the total
of
Apples when the Group is apples?

If you put the textbox (with the percentage calculation) in the Group
Footer
section, you should get the result that you wish for each group.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I need to calculated a percentage for specific criteria on a report.

My table has various indicators Apples/Oranges/Grapes. Reviews are
being
done on the individual indicators....so many Apples reviewed and so
on.
All
Apples/Oranges/Grapes reviewed are on the same table.

I have bad apples (shown with check boxes)....3 of 5 reviewed which
should
be 60 % at group level. Because all indicators are on the same table I
get 3
of 100 reviewed and a percent of 3%. I still will want the overall
percent
to show 3% of all charts reviewed at the bottom but need to correct the
grouped percent.

Can you help be create an expression to give me % of the actual
indicator
at
the Group level?

Thanks!
K

 

[Ken Snell [MVP]]

I'm not completely clear, I think, about exactly what you want, but are you
wanting to show this "60%" value in the report footer, where you have a
textbox to show the percent for apples, the percent for oranges, etc.?

If this is what you want, for the oranges textbox in the report footer, an
expression similar to this should be what you seek:

=Sum(IIf([NameOfControlShowingOrangesOrApples]="Oranges", [not met],
0))/[Not met grand total])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I put that created field into the group section as the percent and it is
not
what I am looking for. It shows that the percent is zero. So I
investigated by putting the text box into the group area and it is giving
me
a total of the records in that section which is 5. 5 records/5 records =
0%

I know I will use that later in life but for now, can you help be get the
Total on this section to be

=[not met]/ ????? to give me a Percent of records not meeting by total
records reviewed by criteria "apples"

20 record reviews for apples overall
30 for oranges
50 total reviewed (now in report footer as grand)

3 fell out of 5 for individual = 60% of personal total (got this)

(help) I need to also see 3/20 for apples reviewed for everyone 15% of
overall for individual.

Your expression needs to use the Sum of the items in that group
(regardless
of whether "met" or "not met"), not the grand total.

Add a hidden textbox to your Detail section (set its Visible property to
No). Name the textbox txtGroupSum. Set the textbox's Running Sum property
to
Over Group. Set the textbox's Control Source to this expression:
= 1

Then change your expression to this:
=Sum([not met])/[txtGroupSum]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the percent in the Group Footer like this: =sum[not met])/([Not
met
grand total]) so it goes to the Grand total which is ALL the reviews
completed and so I get a wrong %. How would I write it to see the
total
of
Apples when the Group is apples?

:

If you put the textbox (with the percentage calculation) in the Group
Footer
section, you should get the result that you wish for each group.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I need to calculated a percentage for specific criteria on a report.

My table has various indicators Apples/Oranges/Grapes. Reviews are
being
done on the individual indicators....so many Apples reviewed and so
on.
All
Apples/Oranges/Grapes reviewed are on the same table.

I have bad apples (shown with check boxes)....3 of 5 reviewed which
should
be 60 % at group level. Because all indicators are on the same
table I
get 3
of 100 reviewed and a percent of 3%. I still will want the overall
percent
to show 3% of all charts reviewed at the bottom but need to correct
the
grouped percent.

Can you help be create an expression to give me % of the actual
indicator
at
the Group level?

Thanks!
K

 

[Kay Starnes]

Ken, Thanks for all your help. I was able to get that report to an
acceptible format.

I haven't had time to work on it more. I would like to try and reword what
I need to see if you can help me.

I have a table with everything on it. I review charts for multiple
individuals. I need each persons report to give them a picture of what they
look like for the charts I review for them.

chart 1 -5 apples ( for this person) everyone (apples) 16
2 bad charts of the 5 for the person
Field I need help with: a field that calculates 2 of 16 department apple
charts reviewed = 12.5% department compliance located on each Person's report

I'm not completely clear, I think, about exactly what you want, but are you
wanting to show this "60%" value in the report footer, where you have a
textbox to show the percent for apples, the percent for oranges, etc.?

If this is what you want, for the oranges textbox in the report footer, an
expression similar to this should be what you seek:

=Sum(IIf([NameOfControlShowingOrangesOrApples]="Oranges", [not met],
0))/[Not met grand total])

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I put that created field into the group section as the percent and it is
not
what I am looking for. It shows that the percent is zero. So I
investigated by putting the text box into the group area and it is giving
me
a total of the records in that section which is 5. 5 records/5 records =
0%

I know I will use that later in life but for now, can you help be get the
Total on this section to be

=[not met]/ ????? to give me a Percent of records not meeting by total
records reviewed by criteria "apples"

20 record reviews for apples overall
30 for oranges
50 total reviewed (now in report footer as grand)

3 fell out of 5 for individual = 60% of personal total (got this)

(help) I need to also see 3/20 for apples reviewed for everyone 15% of
overall for individual.

Your expression needs to use the Sum of the items in that group
(regardless
of whether "met" or "not met"), not the grand total.

Add a hidden textbox to your Detail section (set its Visible property to
No). Name the textbox txtGroupSum. Set the textbox's Running Sum property
to
Over Group. Set the textbox's Control Source to this expression:
= 1

Then change your expression to this:
=Sum([not met])/[txtGroupSum]

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I have the percent in the Group Footer like this: =sum[not met])/([Not
met
grand total]) so it goes to the Grand total which is ALL the reviews
completed and so I get a wrong %. How would I write it to see the
total
of
Apples when the Group is apples?

:

If you put the textbox (with the percentage calculation) in the Group
Footer
section, you should get the result that you wish for each group.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I need to calculated a percentage for specific criteria on a report.

My table has various indicators Apples/Oranges/Grapes. Reviews are
being
done on the individual indicators....so many Apples reviewed and so
on.
All
Apples/Oranges/Grapes reviewed are on the same table.

I have bad apples (shown with check boxes)....3 of 5 reviewed which
should
be 60 % at group level. Because all indicators are on the same
table I
get 3
of 100 reviewed and a percent of 3%. I still will want the overall
percent
to show 3% of all charts reviewed at the bottom but need to correct
the
grouped percent.

Can you help be create an expression to give me % of the actual
indicator
at
the Group level?

Thanks!
K

 

[Ken Snell [MVP]]

Let's try starting with some info about the report. I cannot "see" the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?

 

[Kay Starnes]

SELECT [Overall Charts Reviewed with findings].Physician, [Overall Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall Charts
Reviewed with findings].los, [Overall Charts Reviewed with findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts Reviewed
with findings].[Not Met], [Overall Charts Reviewed with findings].[Reason Not
Met], [Overall Charts Reviewed with findings].[Peer Review Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];

 

[Ken Snell [MVP]]

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT [Overall Charts Reviewed with findings].Physician, [Overall Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall Charts
Reviewed with findings].los, [Overall Charts Reviewed with findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts
Reviewed
with findings].[Not Met], [Overall Charts Reviewed with findings].[Reason
Not
Met], [Overall Charts Reviewed with findings].[Peer Review Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];

Let's try starting with some info about the report. I cannot "see" the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Kay Starnes]

Group on Field: Physician - with header keep together with whole group
Group on Field: Review of - with header keep together with whole grup
group on Not Met - descending no header or footer

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT [Overall Charts Reviewed with findings].Physician, [Overall Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall Charts
Reviewed with findings].los, [Overall Charts Reviewed with findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts
Reviewed
with findings].[Not Met], [Overall Charts Reviewed with findings].[Reason
Not
Met], [Overall Charts Reviewed with findings].[Peer Review Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];

Let's try starting with some info about the report. I cannot "see" the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ken, Thanks for all your help. I was able to get that report to an
acceptible format.

I haven't had time to work on it more. I would like to try and reword
what
I need to see if you can help me.

I have a table with everything on it. I review charts for multiple
individuals. I need each persons report to give them a picture of what
they
look like for the charts I review for them.

chart 1 -5 apples ( for this person) everyone (apples) 16
2 bad charts of the 5 for the person
Field I need help with: a field that calculates 2 of 16 department
apple
charts reviewed = 12.5% department compliance located on each Person's
report

 

[Ken Snell [MVP]]

For which group level (Physician, Review of, or Not Met) do you want the
calculation to be done?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Group on Field: Physician - with header keep together with whole group
Group on Field: Review of - with header keep together with whole grup
group on Not Met - descending no header or footer

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT [Overall Charts Reviewed with findings].Physician, [Overall
Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with
findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall Charts
Reviewed with findings].los, [Overall Charts Reviewed with
findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts
Reviewed
with findings].[Not Met], [Overall Charts Reviewed with
findings].[Reason
Not
Met], [Overall Charts Reviewed with findings].[Peer Review Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];


:

Let's try starting with some info about the report. I cannot "see" the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ken, Thanks for all your help. I was able to get that report to an
acceptible format.

I haven't had time to work on it more. I would like to try and
reword
what
I need to see if you can help me.

I have a table with everything on it. I review charts for multiple
individuals. I need each persons report to give them a picture of
what
they
look like for the charts I review for them.

chart 1 -5 apples ( for this person) everyone (apples) 16
2 bad charts of the 5 for the person
Field I need help with: a field that calculates 2 of 16 department
apple
charts reviewed = 12.5% department compliance located on each
Person's
report

 

[Kay Starnes]

Physician please, thank you for your help.

For which group level (Physician, Review of, or Not Met) do you want the
calculation to be done?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Group on Field: Physician - with header keep together with whole group
Group on Field: Review of - with header keep together with whole grup
group on Not Met - descending no header or footer

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


SELECT [Overall Charts Reviewed with findings].Physician, [Overall
Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with
findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall Charts
Reviewed with findings].los, [Overall Charts Reviewed with
findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts
Reviewed
with findings].[Not Met], [Overall Charts Reviewed with
findings].[Reason
Not
Met], [Overall Charts Reviewed with findings].[Peer Review Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];


:

Let's try starting with some info about the report. I cannot "see" the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Ken, Thanks for all your help. I was able to get that report to an
acceptible format.

I haven't had time to work on it more. I would like to try and
reword
what
I need to see if you can help me.

I have a table with everything on it. I review charts for multiple
individuals. I need each persons report to give them a picture of
what
they
look like for the charts I review for them.

chart 1 -5 apples ( for this person) everyone (apples) 16
2 bad charts of the 5 for the person
Field I need help with: a field that calculates 2 of 16 department
apple
charts reviewed = 12.5% department compliance located on each
Person's
report

 

[Ken Snell [MVP]]

OK. Let me recap to be sure that I understand what you want to do.

For each Physician, you want a textbox to show the percentage of the Not Met
field's values that are checked compared to the total number of records for
that Physician. The Not Met field is a Boolean datatype, meaning that a
checked value is -1 and a nonchecked value is either 0 or Null.

Based on the above, put a textbox in the Group Footer section of the
Physician grouping. Use this expression as its Control Source:
=Sum(Abs([Not Met]))/Sum(IIf(Nz([Not Met],0)=0,1,Abs([Not Met]))

This expression will show the number of records where Not Met is a -1
(checked) divided by the total number of records for that Physician value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Physician please, thank you for your help.

For which group level (Physician, Review of, or Not Met) do you want the
calculation to be done?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Group on Field: Physician - with header keep together with whole group
Group on Field: Review of - with header keep together with whole grup
group on Not Met - descending no header or footer

:

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


SELECT [Overall Charts Reviewed with findings].Physician, [Overall
Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with
findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall
Charts
Reviewed with findings].los, [Overall Charts Reviewed with
findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts
Reviewed
with findings].[Not Met], [Overall Charts Reviewed with
findings].[Reason
Not
Met], [Overall Charts Reviewed with findings].[Peer Review
Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall
Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];


:

Let's try starting with some info about the report. I cannot "see"
the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Ken, Thanks for all your help. I was able to get that report to
an
acceptible format.

I haven't had time to work on it more. I would like to try and
reword
what
I need to see if you can help me.

I have a table with everything on it. I review charts for
multiple
individuals. I need each persons report to give them a picture
of
what
they
look like for the charts I review for them.

chart 1 -5 apples ( for this person) everyone (apples) 16
2 bad charts of the 5 for the person
Field I need help with: a field that calculates 2 of 16
department
apple
charts reviewed = 12.5% department compliance located on each
Person's
report

 

[Kay Starnes]

Thank you Ken you are Awesome!

OK. Let me recap to be sure that I understand what you want to do.

For each Physician, you want a textbox to show the percentage of the Not Met
field's values that are checked compared to the total number of records for
that Physician. The Not Met field is a Boolean datatype, meaning that a
checked value is -1 and a nonchecked value is either 0 or Null.

Based on the above, put a textbox in the Group Footer section of the
Physician grouping. Use this expression as its Control Source:
=Sum(Abs([Not Met]))/Sum(IIf(Nz([Not Met],0)=0,1,Abs([Not Met]))

This expression will show the number of records where Not Met is a -1
(checked) divided by the total number of records for that Physician value.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Physician please, thank you for your help.

For which group level (Physician, Review of, or Not Met) do you want the
calculation to be done?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Group on Field: Physician - with header keep together with whole group
Group on Field: Review of - with header keep together with whole grup
group on Not Met - descending no header or footer

:

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


SELECT [Overall Charts Reviewed with findings].Physician, [Overall
Charts
Reviewed with findings].ErPhys, [Overall Charts Reviewed with
findings].AccountNumber, [Overall Charts Reviewed with
findings].[Patient
Name], [Overall Charts Reviewed with findings].DisDate, [Overall
Charts
Reviewed with findings].los, [Overall Charts Reviewed with
findings].Age,
[Overall Charts Reviewed with findings].[Review of], [Overall Charts
Reviewed
with findings].[Not Met], [Overall Charts Reviewed with
findings].[Reason
Not
Met], [Overall Charts Reviewed with findings].[Peer Review
Comments],
[Overall Charts Reviewed with findings].[Reviewd By], [Overall
Charts
Reviewed with findings].[Date Reviewed]
FROM [Overall Charts Reviewed with findings];


:

Let's try starting with some info about the report. I cannot "see"
the
report's data contents nor the design very clearly yet; sorry.

Post the SQL statement of the query that is being used the report.

Which fields are in the Sorting & Grouping window for the report?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Ken, Thanks for all your help. I was able to get that report to
an
acceptible format.

I haven't had time to work on it more. I would like to try and
reword
what
I need to see if you can help me.

I have a table with everything on it. I review charts for
multiple
individuals. I need each persons report to give them a picture
of
what
they
look like for the charts I review for them.

chart 1 -5 apples ( for this person) everyone (apples) 16
2 bad charts of the 5 for the person
Field I need help with: a field that calculates 2 of 16
department
apple
charts reviewed = 12.5% department compliance located on each
Person's
report