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.