# Thread: math formula in report (access 2000-2003)

1. ## 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. ## 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

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

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

4. ## 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. ## 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 ?

6. ## 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. ## 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

8. ## 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
•