## For ReaperMan, Re: Dynamic Calculations

Hi, <BR><BR>Thanks for your suggestions. <BR><BR>Here is a more specific example of what I need the script to do. <BR><BR>The query is based on three tables. <BR><BR>1) Consultants - The fields are consultantid, consultantname, weightfactor. <BR><BR>2) Mediacodes - The fields are mediacode, mediacost <BR><BR>3) Salesleads - The fields are consultantid, mediacode <BR><BR>The script needs to perform the following functions: <BR><BR>The script is given a mediacode and pulls of a list of consultants from the database, it then searches the sales leads database for matching mediacodes. Once it has done this it performs the following calculations. <BR><BR>It counts the number of sales leads with matching mediacodes for each consultant. &#039;consultant_total_leads&#039; <BR><BR>It then add all the consultant_total_leads together to create &#039;all_consultants_total_leads&#039;. <BR><BR>It then multiplies the total leads by the consultants weight factor. &#039;consultant_total_leads * weightfactor&#039; to create a &#039;consultant_score&#039; for each consultant. <BR><BR>It then divides each consultant_score by the all_consultants_total_leads to create a &#039;consultant_cost_so_far&#039; for each consultant. <BR><BR>It then takes the &#039;consultant_cost_so_far&#039; for each consultant then multiplies it by the mediacost and rounds it to two decimal places to create a &#039;consultant_final_cost&#039; for each consultant. <BR><BR>It also needs to create a &#039;consultant_final_cost_exvat&#039; for each consultant which is the same as &#039;consultant_final_cost&#039; but minus 17.5% worth of VAT. <BR><BR>The final output would be something like this: <BR><BR><BR>Consultant No. of leads Cost per lead Cost ex. VAT <BR><BR>Name 1 5 £0.00 £0.00 <BR>Name 2 1 £0.00 £0.00 <BR>Name 3 4 £0.00 £0.00 <BR><BR>Total no. leads 10 <BR>Media cost £0.00 <BR>Media cost ex vat £0.00 <BR><BR>The number of consultants in the database changes regularly so it needs to be able to do this all dynamically. <BR><BR>One problem I have encountered is when there are no leads for any consultants, it tries to divide 0 by 0 and causes an error. <BR><BR>Hope this makes it a little clearer for you. Obviosuly the variable names wouldn&#039;t be what I have stated, I used this context to explain it a little easier. This script has baffled we and I am unsure of how to proceed with it. <BR><BR>Thanks, <BR><BR>Robert.