By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

calculating a percent of total in a query

P: n/a
I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to write the
correct expression due to the "groupby" needed to group the city count
in the first column. Any clues?

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
mh*****@comcast.net wrote:
I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to write the
correct expression due to the "groupby" needed to group the city count
in the first column. Any clues?


Do you really need to have the percent calculated when running the query
or can you create a column with the total counts of cities and when you
run your process code on the resulting recordset calculate the percent?

For example:
Expr1 : Dcount("CityId","Table","criteria")

Then when you want to process, in a form or module calc the %?

To make it faster than using DCount, you could create a TotalsQuery that
gets the count of cities. Since the Totals query would return 1 record,
you could do a Cartesian join. Simply add that query to the other
tables (we'll call it citycnt) and then drag that count field into the
column next to the City field. Don't set a relationship. THen run.

If you absolutely need to have a percentage returned when the query is
first executed instead of being calculated, you could run Dcounts() on
the specific city divided by the total counts of cities.


Nov 13 '05 #2

P: n/a
Salad wrote:
mh*****@comcast.net wrote:
I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to write the correct expression due to the "groupby" needed to group the city count in the first column. Any clues?

Do you really need to have the percent calculated when running the

query or can you create a column with the total counts of cities and when you run your process code on the resulting recordset calculate the percent?
For example:
Expr1 : Dcount("CityId","Table","criteria")

Then when you want to process, in a form or module calc the %?

To make it faster than using DCount, you could create a TotalsQuery that gets the count of cities. Since the Totals query would return 1 record, you could do a Cartesian join. Simply add that query to the other
tables (we'll call it citycnt) and then drag that count field into the column next to the City field. Don't set a relationship. THen run.

If you absolutely need to have a percentage returned when the query is first executed instead of being calculated, you could run Dcounts() on the specific city divided by the total counts of cities.


thanks...I essentially did your suggestion of joining a totals query
with the speicific city query...also, I found that if I renamed the
columns, I can use them in an expression for a new, calculated column!
That is, Expr1:City is my first column (which contains the groupedby
City count data, i.e. the number of each City), Expr2:TotalCityCount is
my second column (from the totals query), then the third column is
Expr3:100*Expr1/Expr2 which gives the percent.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.