Calculate Values from one table and display sum on another table

[gregnbrown]

I have run into a snag.  I have two tables, one is called project and the other is called sales. The relationship between the two tables is a field called salesrep.  The project table contains information on projects, specifically three important pieces of information that I need to sum up and display information in the sales table. 

For example.  A Sales Rep will own a project and he/she will put a dollar value in the 'project_amount' field, identify which quarter the project will book in the 'billing_cycle' field and what the status is in the 'sales_projection' field. I can run manual reports all day long by searching criteria in these fields and performing a find and have that output of that find go to a layout with the search results. Pretty basic. 

 

Here is my challenge. I want to have a layout that lists all of my Sales Reps, and by their billing_cycle, have it automatically sum up all of the projects that they own with a pre-defined value, lets say the predefined value is forecast, and display that result in my sales table.

 

Remember, the project table contains the dollar amounts and the sales table contains the account reps. An Account Rep can have many projects, however, a project can only have one Account Rep.

 

What type of script could I write to have this done ?

[comment]

I am not sure I understand your question, esp. this part:
 

automatically sum up all of the projects that they own with a pre-defined value, lets say the predefined value is forecast, and display that result in my sales table.

1. What exactly is "forecast"? Is it a field in the Projects table?

2. What exactly do you mean by "sum up all of the projects that they own with a pre-defined value"? Would this involve finding only (sales of) projects that meet some criteria?


IMHO, you have an inherent structural issue that may prevent you from producing some type of reports. The relationship between Projects and Sales should be based on matching ProjectID - not SalesRep. In fact, going by your own description you should have three tables, arranged as:

SalesReps -< Projects -< Sales

The way you have it now, it is impossible to determine which project a record in Sales belongs to. Therefore, you won't be able to  produce any kind of report on sales that would incorporate data from their respective parent projects.