Calculating Percentage based on sum functions (2003)

[klepto84]

I have a simple database--one table with these fields: Date, Route, Total Contact, Total "Yes", Total "No"
I want to calculate the percentage for the Total "Yes" and Total "No" fields based on the Total Contact for a date range.
I have a query that is calculating the percentages for each item in the table using this calculated field

PerCent: [Total Yes]/[Total Contacts]

But when I try to create a query to do the same calculation using the sum of Total Contact, Total Yes and Total No, I get a prompt box asking me to input the parameters. What I have typed into the [ ] brackets in the calculated cell appears in the prompt box and if I hit RETURN the calculated cell is blank.

Do you know what I'm doing wrong?

[HansV]

What exactly is the expression you're using? (The one that fails, I mean)

[klepto84]

I used the wizard to create a sum function, grouping the dates by year. It works.

When I add the calculated cell

Yes %: [Sum Of Total Yes: Total Yes ]/[Sum Of Total Contacts: Total Contacts ]

it doesn't work.

[HansV]

Try:

Yes %: Sum([Total Yes])/Sum([Total Contacts])

Make sure that you set the Total option for this column to Expression.

[klepto84]

That did the trick
Thanks
Right now the query is grouping by date by year.
If I put a between [First Date] and [Last Date] in the criteria line in the date field I am not getting the totals for the date range.
How do I set up the query to return totals for a date range?

[HansV]

Do you mean that you want to return a total over the entire date range, even if it is longer than one year? If so, you'll have to use a separate query, not grouped by year.

[klepto84]

I'd like to be able to do two things: 1. return the totals for the data over a date range not grouped by year
2. return the totals for data over a date range grouped by a range within the year
When I remove the grouping fields and use date as a field, and use between [first date] and [last date] in the criteria field I get the total for each day in the date range--not a total for the entire date range. So I need a total of the daily totals.

[HansV]

If you want to calculate a total over the entire date range, create a separate query.
In this query, set the Total option for the date field to Where. This will automatically clear the Show check box for this column.
Specify the Between ... And ... condition in the Criteria line.
Set the Total option for the field you want to calculate to Sum.