Percentages in a Query

[talkiechick]

Is there a way to take the column total and have it display for each group in a query?

 

[John Big Booty]

Is there a way to take the column total and have it display for each group in a query?

Columns are an Excel concept and the term really has no meaning in Access. Do you mean you want to calculate the total for a group of records in a particular field?

If so you can do this using a Totals query, you can do this by creating a select query, then whilst it is in design view click the Sigma (Σ) button, when you do this you will notice that a new row appears in the design grid called Total: this then allows a whole lot of options including Sum.

 

[talkiechick]

I apologize for that... I did not mean to refer to the field as a column. i understand how to sum a group, but i want to create a field on the query for percent of each group in relationship to the entire query. You can accomplish this through a report, but I would like it as a percent in the query for formatting purposes as well as charting reasons.

Do you think there is a way to do this john?

 

[Brianwarnock]

Columns are an Excel concept and the term really has no meaning in Access. Do you mean you want to calculate the total for a group of records in a particular field?

.

Let's not get too pedantic, the design grid does use the word column.

Brian

 

[Brianwarnock]

You cannot do that in one query as the final total is not available until the end of the query and thus not available for each row.

Brian

 

[SOS]

You can, however use a REPORT in conjunction with a query to do all sorts of grouping, totalling, etc.

 

[talkiechick]

The problem with using it in a report, when I make slight changes to the report, often it screws with my query and i have to recreate the query field and report over again. Also i want an actual field in the query for when i am creating charts. The Bar chart in Access does not allow for percents to show up on series labels and I would rather have percents show up than actual values.

If it is not possible I guess its a sunk cause.

 

[Brianwarnock]

Well you can use 2 queries the first sums just the field, then this is included in the second query, no join required and then
percentage:tablename.Fieldname/queryname.fieldname (probably sumoffieldname)

Sum on total row and select property as percentage

Brian