Change in records

[fule12]

Hi to all

I have form with 3 fields (VesselID,CodeID,DayBudget)
And in another form where i choose Star Date and End date
Difference between this two date is multiply with DayBudget and is store to Field Budget
so in the end i have report what is look like this:
Vessel : Test01
Code : ABC01
Budget : 2000$ (amount from choousen date)
Cost : 1000$ (amount from Code)
Total : 1000$ (Budget - Cost)
My question is:
Let say Daily Budget for Vessel Test01 was 30$ from 01/01/2002 up to 31/04/2002 and now from 01/05/2002 is change to 35$.So Today if i enter date's 01/01/2002(Start Date) and 21/05/2002(End Date) = 140 day's * with DayBudget(35$) is 4200$ what is wrong. Corect amount is :
((01/01/2002 - 30/04/2002)*30)+((01/05/2002 - 21/05/2002)*35))= Budget
any idea or suggestion?
Thanks
Fule

 

[LittleSmudge]

The problem is that the entire structure of your database ( from what you describe ) is incompatible with multiple DayBudgets in a single batch.

You'll need to go back to the original schema and design the option in from scratch.


Alternativly calculate the AVERAGE day budget by having multiple StateDate, EndDate and DayBudget fields on the DateGetting Form. Then use the data to calculate the duration weighted average and use that.


G LS

 

[BSman]

You need to include in your database a place to store the start and end dates that a specific Day Budget is in effect. For example, if you include start and end date fields in the table (form) you mention with VesselID, CodeID, and DayBudget, you would then have the necessary data in your database to perform the calculation you say you need.

Otherwise, how can your database "know" that the day rate changed on May 1?