Calculate % in one query : "Count some" divided by "Count All"

P

Palto Fondberg

Hi

I have a table of 5 columns whereby field values are either positive,
negative or level. I've constructed a query whereby the results are then
converted to either "P" for positive, "N" for negative or "L" for level.
What I want to be able to do is count all of the "P"'s in say column one and
then divide this result by the total for that column ie: P+N+L. I want to do
this for all five columns. Will I need to use an SQL approach?


thanks
 
P

Palto Fondberg

Great, Thanks Ryan

for those also looking for help on this, see below my actual formula in
Access - as ever, you need to make sure the " and ' are used
appropriately.
I've also added an OR statement at the end to count multiple criteria:

CountofAll:
DCount("[Result1_categorise]","[qryDealerSurveyIndicies_categorise]","[Result1_categorise]='P'"
Or "[Result1_categorise]='L'" Or "[Result1_categorise]='N'")

Thanks again Ryan

Ryan said:
Dcount is what you are looking for. Here is how it works.
DCount ( expression, domain, [criteria] )

So your code would look something like this

DCount("ColumnOne", "YourTable", "ColumnOne = "P"")

Check out this link
http://msdn.microsoft.com/en-us/library/aa190038(office.10).aspx


--
Please remember to mark this as answered if this solves your problem.


Palto Fondberg said:
Hi

I have a table of 5 columns whereby field values are either positive,
negative or level. I've constructed a query whereby the results are then
converted to either "P" for positive, "N" for negative or "L" for level.
What I want to be able to do is count all of the "P"'s in say column one
and
then divide this result by the total for that column ie: P+N+L. I want to
do
this for all five columns. Will I need to use an SQL approach?


thanks
 
R

Ryan

No problem. Please remember to mark this post as answered if this solves
your problem.



Palto Fondberg said:
Great, Thanks Ryan

for those also looking for help on this, see below my actual formula in
Access - as ever, you need to make sure the " and ' are used
appropriately.
I've also added an OR statement at the end to count multiple criteria:

CountofAll:
DCount("[Result1_categorise]","[qryDealerSurveyIndicies_categorise]","[Result1_categorise]='P'"
Or "[Result1_categorise]='L'" Or "[Result1_categorise]='N'")

Thanks again Ryan

Ryan said:
Dcount is what you are looking for. Here is how it works.
DCount ( expression, domain, [criteria] )

So your code would look something like this

DCount("ColumnOne", "YourTable", "ColumnOne = "P"")

Check out this link
http://msdn.microsoft.com/en-us/library/aa190038(office.10).aspx


--
Please remember to mark this as answered if this solves your problem.


Palto Fondberg said:
Hi

I have a table of 5 columns whereby field values are either positive,
negative or level. I've constructed a query whereby the results are then
converted to either "P" for positive, "N" for negative or "L" for level.
What I want to be able to do is count all of the "P"'s in say column one
and
then divide this result by the total for that column ie: P+N+L. I want to
do
this for all five columns. Will I need to use an SQL approach?


thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top