Results 1 to 8 of 8

20061106, 13:51 #1
 Join Date
 Mar 2006
 Location
 Kitchener, Ontario, Canada
 Posts
 53
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculating Percentage based on sum functions (2003)
I have a simple databaseone 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?

20061106, 13:54 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Calculating Percentage based on sum functions (2003)
What exactly is the expression you're using? (The one that fails, I mean)

20061106, 14:38 #3
 Join Date
 Mar 2006
 Location
 Kitchener, Ontario, Canada
 Posts
 53
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Percentage based on sum functions (2003)
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.

20061106, 14:41 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Calculating Percentage based on sum functions (2003)
Try:
Yes %: Sum([Total Yes])/Sum([Total Contacts])
Make sure that you set the Total option for this column to Expression.

20061106, 15:07 #5
 Join Date
 Mar 2006
 Location
 Kitchener, Ontario, Canada
 Posts
 53
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Percentage based on sum functions (2003)
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?

20061106, 15:36 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Calculating Percentage based on sum functions (2003)
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.

20061106, 16:03 #7
 Join Date
 Mar 2006
 Location
 Kitchener, Ontario, Canada
 Posts
 53
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Percentage based on sum functions (2003)
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 rangenot a total for the entire date range. So I need a total of the daily totals.

20061106, 16:10 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Calculating Percentage based on sum functions (2003)
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.