calculating a percent of total in a query

[mhodkin]

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?

[Salad]

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.

[mhodkin]

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.