
Dynamic calculations
Hi,<BR><BR>I'm trying to work out how to retrieve data from an access db table then perform calculations on certain columns.<BR><BR>The table consists of the following columns.<BR><BR>Consultant, Number of leads, Cost per consultant, Cost exc. VAT<BR><BR>1) The names list down the page<BR><BR>2) The number of leads for a given period is displayed next to the consultant name.<BR><BR>3) Cost per consultant is worked out using a set formulae and based on another figure from another table. 'Weighting factor'. I.e. 40<BR><BR>4) Cost exc. VAT is the same as 3 but minus 17.5% VAT<BR><BR>I can get this to work if i explicity ask for certain consultants.<BR><BR>At the minute I am having to change the code each time a new consultant is employed or leaves.<BR><BR>I need the script to find the names of the consultants and work the math out from there dynamically. The number of consultants may change from month to month and the names change regularly. <BR><BR>Is this possible?<BR><BR>If so what type of commands would I use to get the dynamic version working.<BR><BR>Thanks<BR><BR><BR>Robert<BR><BR>

You can do this via one query
Ok, so my knowledge of SQL isn't groundbreaking, but I am sure this can be done using one SQL query  even in Access.<BR><BR>Something like:<BR>SELECT ConsultantName, NoOfLeads, Cost, (CInt(Cost)/1.175) AS CostExVat FROM ConsultantsTable<BR><BR>Would need more info to be able to give you a proper query, though.<BR><BR>Craig.

RE: You can do this via one query
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. 'consultant_total_leads'<BR><BR>It then add all the consultant_total_leads together to create 'all_consultants_total_leads'.<BR><BR >It then multiplies the total leads by the consultants weight factor. 'consultant_total_leads * weightfactor' to create a 'consultant_score' for each consultant.<BR><BR>It then divides each consultant_score by the all_consultants_total_leads to create a 'consultant_cost_so_far' for each consultant.<BR><BR>It then takes the 'consultant_cost_so_far' for each consultant then multiplies it by the mediacost and rounds it to two decimal places to create a 'consultant_final_cost' for each consultant.<BR><BR>It also needs to create a 'consultant_final_cost_exvat' for each consultant which is the same as 'consultant_final_cost' 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'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.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

