Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    new orleans, louisiana
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    math formula in report (access 2000-2003)

    i have a database tracking how many years a person worked at a site,example: 3 years while one employer, 8 another, 4 another. i want the report to show each of those in a column, then a final column showing the percent of time that one employer was of the whole. in other words, 3+8+4=15, then the formula will determine what percent of 15 the 3 was. how in the heck do i do that, and where-in the report, in the form, in the table?? and what are the signs or operators? (the boss doesn't want it in Excel) thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    Your example shows three employers. Will that *always* be the case? Does your database have three fields dedicated to these three employers? Or do you a "normalized" structure with say, an Employees table and a YearsAtEmployers table? Using a normalized structure will require a crosstab query to get the records for each employee into a single row for reporting. A normalized database structure is more flexible and powerful in the long run, but it's also more work to set up.

    I am going to assume you have a table with three fields for the three employers. Obviously, you must use the actual field names from your table, but I am going to pretend that I know the field names...

    You do this work in either a query or a report. To do it in a query, make a new query and add the employee name and the three "years" fields. Then make a new field in the query, something like this:
    TotalYears: YearsEmployer1 + YearEmployer2 + YearEmployer3

    Then add three more fields to the query, something like this:
    PctEmployer1: YearsEmployer1/TotalYears
    PctEmployer2: YearsEmployer3/TotalYears
    PctEmployer3: YearsEmployer3/TotalYears

    Save the query, then build a report based on the query.


    To do it solely in a report, you add an textbox (say, txtTotalYears) for the sum of the years. Put this formula in its ControlSource:
    = txtYearsEmployer1 + txtYearsEmployer2 + txtYearsEmployer3

    Then add three more textboxes, and put these formulae in their ControlSources
    = txtYearsEmployer1 / txtTotalYears
    = txtYearsEmployer2 / txtTotalYears
    = txtYearsEmployer3 / txtTotalYears
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    new orleans, louisiana
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    that looks great! thanks, i'll try it in the morning!

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    new orleans, louisiana
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    OK, the txt employer year 1 etc work great, but the "= txtYearsEmployer1 / txtTotalYears" doesn't show- neither as a percent or number. the properties have it being visible, i tried making it a % but it just showed 0.00% for the last two records, nothing for the first record. (I did just cut and paste-should i type it?)

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    First answer to the question : How is the data stored?
    In 3 fields in a record or is it a normalized table with undefined number of employers ?
    Where did you try to enter the formulas ? In a query or in a form/report ?
    Francois

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    new orleans, louisiana
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    the data is a table with 6 fields: name, #years with employer 1, # years with employer 2, #years with employer 3-all data entried. then i need a total of the years worked, and then the percent that were worked with employer 1. these are all the fields there will be for each record. i am just tracking the years so only need results in the report after i enter the data. i put the expression in a text box in the report

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    You can do it in the query, see query1 in the attached database.
    You can also do it in the report see report1 based on qryReport. In the report, I set the format property to Precent.
    Hope this help
    Francois

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    new orleans, louisiana
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: math formula in report (access 2000-2003)

    Thank You!!!

Posting Permissions

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