I'm trying to develop a system that allows a compensation manager to analyze market data. The data comes from surveys conducted by large research firms (Milliman & Robertson, William M. Mercer, etc.) and is divided into job classes. Each job category (such as "Admin Asst 1") has a pay structure derived from current market levels, such as "average minimum", "average maximum", "mean", etc.
The Access DB I'm constructing will allow the comp manager to input several different survey data sets for each job class, age them to a specified forecast date, and aggregate them into a market average for that job at that point in time. I have most of the rest of the DB done, but the aging process isn't as clear to me. Here's me meta-process:
1. User inputs jobNumber, baseAgingFactor (a percentage at which the market for this job is expected to grow), and forecastDate. A command button (cmdReport) is pressed.
2. cmdReport calls all records from the market data table (tblMktData) that match the input jobNumber.
3. For each matching record:
A. Find calcAgeFactor - (baseAgingFactor/12)*(forecastDate-surveyDate)
B. Multiply each data point (avgMinimum, etc.) by calcAgingFactor.
4. Sum and average each column to produce an aggregate forecasted data point for the jobNumber in question. This is then spit out in a report.
It's pretty straightforward on paper, but putting it into VBA/Access 97 is a bit harder. Some people have suggested I use several queries in a queue, another said I should store the values from step three into a table and then run a query on it... Macros, modules... I'm not sure where to go. Any suggestions from the audience?
Thanks,
Andreas
[This message has been edited by AUdby (edited 06-30-2000).]
The Access DB I'm constructing will allow the comp manager to input several different survey data sets for each job class, age them to a specified forecast date, and aggregate them into a market average for that job at that point in time. I have most of the rest of the DB done, but the aging process isn't as clear to me. Here's me meta-process:
1. User inputs jobNumber, baseAgingFactor (a percentage at which the market for this job is expected to grow), and forecastDate. A command button (cmdReport) is pressed.
2. cmdReport calls all records from the market data table (tblMktData) that match the input jobNumber.
3. For each matching record:
A. Find calcAgeFactor - (baseAgingFactor/12)*(forecastDate-surveyDate)
B. Multiply each data point (avgMinimum, etc.) by calcAgingFactor.
4. Sum and average each column to produce an aggregate forecasted data point for the jobNumber in question. This is then spit out in a report.
It's pretty straightforward on paper, but putting it into VBA/Access 97 is a bit harder. Some people have suggested I use several queries in a queue, another said I should store the values from step three into a table and then run a query on it... Macros, modules... I'm not sure where to go. Any suggestions from the audience?
Thanks,
Andreas
[This message has been edited by AUdby (edited 06-30-2000).]
