Results 1 to 8 of 8
  1. #1
    Star Lounger
    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 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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Percentage based on sum functions (2003)

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

  3. #3
    Star Lounger
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    Star Lounger
    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    Star Lounger
    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 range--not a total for the entire date range. So I need a total of the daily totals.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •