Calculating percentages

  • Thread starter Thread starter Luther
  • Start date Start date
L

Luther

Hello,

I have a calculated field (HowMany) which returns the
total number of updated records. What I want to accomplish
is to find their percent of the whole database. For
example, if there are 1,000 records, and 100 of them have
been updated (makes it 10%). How do you make that
calculation where you make Access count the database
(because there can be additions) and then divide HowMany
by that number?

I hope I have made it clear enough for a response.

Well appreciated in advance.
 
Hi Luther

Include another calculated field which returns the total count of all
records. Then include a third field which is the calculation of
HowMany/TotalRecords.

In a simple case, you would include three fields in your query:
TotalRecords: Count(*)
HowMany: Sum(IIf([Updated]<>0,1,0)
PercentUpdated: HowMany/TotalRecords
 
Hi Graham,

Thank you for the information. But I failed to mention one
thing:
1. There is a WHERE criteria that is applied, i.e. <=Date
() for the updated records; that is how I'm able to count
them.
So how would I combine this with the TotalRecords where
the variable TotalRecords read in ALL, not just the count
for the updated. Basically, I get the same number for both
variables. I hope that this explanation is clear enough.
-----Original Message-----
Hi Luther

Include another calculated field which returns the total count of all
records. Then include a third field which is the calculation of
HowMany/TotalRecords.

In a simple case, you would include three fields in your query:
TotalRecords: Count(*)
HowMany: Sum(IIf([Updated]<>0,1,0)
PercentUpdated: HowMany/TotalRecords

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello,

I have a calculated field (HowMany) which returns the
total number of updated records. What I want to accomplish
is to find their percent of the whole database. For
example, if there are 1,000 records, and 100 of them have
been updated (makes it 10%). How do you make that
calculation where you make Access count the database
(because there can be additions) and then divide HowMany
by that number?

I hope I have made it clear enough for a response.

Well appreciated in advance.
Click to expand...


.
Click to expand...
 
No problem! Just add the WHERE condition to your query.

Or do you mean that the criterion for determining if the record has been
updated is to test if the date field is <=Date()?

In that case, change the HowMany expression as follows:

HowMany: Sum(IIf([DateField]<=Date(),1,0)

What this says is: If DateField<=Date() then add 1 to the cound, otherwise
add 0 to the count.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



Hi Graham,

Thank you for the information. But I failed to mention one
thing:
1. There is a WHERE criteria that is applied, i.e. <=Date
() for the updated records; that is how I'm able to count
them.
So how would I combine this with the TotalRecords where
the variable TotalRecords read in ALL, not just the count
for the updated. Basically, I get the same number for both
variables. I hope that this explanation is clear enough.
-----Original Message-----
Hi Luther

Include another calculated field which returns the total count of all
records. Then include a third field which is the calculation of
HowMany/TotalRecords.

In a simple case, you would include three fields in your query:
TotalRecords: Count(*)
HowMany: Sum(IIf([Updated]<>0,1,0)
PercentUpdated: HowMany/TotalRecords

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello,

I have a calculated field (HowMany) which returns the
total number of updated records. What I want to accomplish
is to find their percent of the whole database. For
example, if there are 1,000 records, and 100 of them have
been updated (makes it 10%). How do you make that
calculation where you make Access count the database
(because there can be additions) and then divide HowMany
by that number?

I hope I have made it clear enough for a response.

Well appreciated in advance.
Click to expand...


.
Click to expand...
Click to expand...
 
Works great !!! You have been most helpful. Once again,
thank you.
-----Original Message-----
No problem! Just add the WHERE condition to your query.

Or do you mean that the criterion for determining if the record has been
updated is to test if the date field is <=Date()?

In that case, change the HowMany expression as follows:

HowMany: Sum(IIf([DateField]<=Date(),1,0)

What this says is: If DateField<=Date() then add 1 to the cound, otherwise
add 0 to the count.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



Hi Graham,

Thank you for the information. But I failed to mention one
thing:
1. There is a WHERE criteria that is applied, i.e. <=Date
() for the updated records; that is how I'm able to count
them.
So how would I combine this with the TotalRecords where
the variable TotalRecords read in ALL, not just the count
for the updated. Basically, I get the same number for both
variables. I hope that this explanation is clear enough.
-----Original Message-----
Hi Luther

Include another calculated field which returns the
Click to expand...
total
count of all
records. Then include a third field which is the calculation of
HowMany/TotalRecords.

In a simple case, you would include three fields in
Click to expand...
your
query:
TotalRecords: Count(*)
HowMany: Sum(IIf([Updated]<>0,1,0)
PercentUpdated: HowMany/TotalRecords

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hello,

I have a calculated field (HowMany) which returns the
total number of updated records. What I want to accomplish
is to find their percent of the whole database. For
example, if there are 1,000 records, and 100 of them have
been updated (makes it 10%). How do you make that
calculation where you make Access count the database
(because there can be additions) and then divide HowMany
by that number?

I hope I have made it clear enough for a response.

Well appreciated in advance.


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


.
Click to expand...
 
Back
Top