Data Analysis

[cjmcfg]

I'm fairly new to access and i'm not sure how to do what i want but this is it...

I have revenue values that employees are credited with and my company creates a ranking report for them. What i need to do is take a record which is similar to this:

Date Employee Office Loan Amt Fees Loan Type
5/5/2007 Smith, John H-burg 100000 325 2

I need to get the following values:

Sum of all Loan Amts
Sum of all Loan Amts for each employee
Sum of all Fees
Sum of all Fees for each employee
Loan Types 1&3 have a value of 1 Loan Types 2&4 have a value of .5. I need the total of all those values and the total of each employee.

Then I need to normalize the data by percentage meaning:

Sum of John Smith Loan Amt/Total Loan Amt
Sum of John Smith Fees/Total Fees
Sum of John Smith Loan Types/Total Loan Types

Then I need to Calculate a weighted average of the percentages for each employee with the weights of:
Loan Amt (.5)
Fees (.4)
Loan Types (.1)

Then List the employees with all the data in a ranked order from highest to lowest.

The Employees need to be read from an employee table because i need to include all employees even if they were not entered as having a loan.

 

[dhookom]

I would normalize the Loan Amoutn and Fees columns into a single column using a union query. Then have a field in your loan type lookup table that stores the "values" of 1 and .5. Then have a field in your lookup table of transactions (Loan, Fees,...) that stores .5, .4, and .1.

Then combine all these together in a report or reports. You might need to include subreports based on the amount of details and summaries you want on the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]