calculating a percent of total in a query

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?

re: calculating a percent of total in a query

mhodkin@comcast.net wrote:[color=blue]
> 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?
>[/color]

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.

re: calculating a percent of total in a query

Salad wrote:[color=blue]
> mhodkin@comcast.net wrote:[color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > correct expression due to the "groupby" needed to group the city[/color][/color]
count[color=blue][color=green]
> > in the first column. Any clues?
> >[/color]
>
> Do you really need to have the percent calculated when running the[/color]
query[color=blue]
> or can you create a column with the total counts of cities and when[/color]
you[color=blue]
> run your process code on the resulting recordset calculate the[/color]
percent?[color=blue]
>
> 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[/color]
that[color=blue]
> gets the count of cities. Since the Totals query would return 1[/color]
record,[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
is[color=blue]
> first executed instead of being calculated, you could run Dcounts()[/color]
on[color=blue]
> the specific city divided by the total counts of cities.[/color]

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.