Formula to calc total value by using muliplier effective on that date

[posero]

I wonder if someone could help me with the following formula problem.&nbsp;&nbsp;I'm a new user and I have been working on it for hours without success,&nbsp;&nbsp;I'm just about to see if my laptop has aeronautical capabilities...<br><br>I am using a btrieve (It's not SQL) database, with Crystal 8 to develop a report for my department in the clinic.&nbsp;&nbsp;This report is to calculate the radiation dose a patient has received in one anatomical area over a course of treatment.&nbsp;&nbsp;One area is treated with multiple treatment fields over many days and times.&nbsp;&nbsp;(continued below)<br><br>&nbsp;This should be done by multiplying the dose by a dose coefficient:&nbsp;&nbsp;<br><br><br>e.g.&nbsp;&nbsp;<br>Day 1 FirstTreatment:dose100unitsx1.2 coefficient = 120 cGy <br>Day 2 LastTreatment:dose100 units x1.4 coefficient = 140 cGy<br> Total Dose for Treatment: 260 cGy <br><br><br>This would be simple (even for my skill level) but, <br><br>- the coefficient changes at certain dates, with the first date that the new coefficient is to be used recorded in the database:&nbsp;&nbsp;<br><br>E.G.<br>From start date of treatment<br>e.g. coefficient &quot;1&quot;&nbsp;&nbsp;&nbsp;(the start date is usually recorded by the user either as a blank (null) field in the date field in the database, or as a date equal to the date the patient is first treated)<br><br>coeffiecient field&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;start date this coefficient<br> 1<br><br>From 31.8.2000:<br><br>coefficient field&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;start date this coefficient<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31.8.2000<br><br>From 15.9.2000<br><br>coefficient field&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;start date this coefficient<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15.9.2000<br><br><br>Ideally the results would look like this:<br><br><br>Trt day Trt Dose Coefficient Effective Date&nbsp;&nbsp;DoseToday Total<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;of Coefficient<br><br>15.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;100<br>19.8.00&nbsp;&nbsp;100 cG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;200<br>31.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;330<br>5.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;460<br>8.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;590<br>15.9.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;700<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Grand total of Dose given to this field:&nbsp;&nbsp;700 cGy<br><br><br>Unfortunately the database links each treatment field to all coefficients belonging to that field, so<br>that the data looks as below:<br><br><br>Trt day&nbsp;&nbsp;Trt Dose Coefficient Effective Date DoseToday Total <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;of Coefficient<br><br>15.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;<br>15.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;230<br>15.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;340<br>19.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;440<br>19.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;570<br>19.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;680<br>31.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;780<br>31.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;910<br>31.8.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1020<br>5.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1120<br>5.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1250<br>5.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1360<br>8.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1460<br>8.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1590<br>8.9.00&nbsp;&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1700&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>15.9.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(date is blank)&nbsp;&nbsp;&nbsp;100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1800<br>15.9.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;31:8:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1930<br>15.9.00&nbsp;&nbsp;100 cGy&nbsp;&nbsp;&nbsp;&nbsp;1.1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15:9:2000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2040<br>(INCORRECT)Grand total of Dose given to this field:2040 cGy<br><br>I need help with a formula that uses the start date (usually blank, but not always) to calculate the dose for those treatment days, until the first coeffiecient date equals the treatment date when it uses the coefficient of that date to calculate the dose for the next treatment days, until the next coefficient day is in effect etc. etc.<br><br>Part of the problem I had is that any formula I created that was sophisticated enough to attempt to give me a halfway result for a particular day refused to allow these results to be summed to a grand total dose of all fields in the entire course of treatment using another grand total formula or running total formula.<br><br>If you could please help,&nbsp;&nbsp;or tell me where I might find help to create such a formula.&nbsp;&nbsp;<br><br>Many thanks in advance !<br><br><br><br><br><br><br><br><br><br>&nbsp;<br>

 

[MalcolmW]

It might help to simplify your calculation if you had a lookup table with two fields - one that contained every date in the calendar, and other the coefficent for that date.<br>This would make the determination of the appropriate coefficient to use for a given treatment date easy.&nbsp;&nbsp;This table would have to be updated daily I presume. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>