I’m trying to build a database to calculate the demurrage on containers. The demurrage is determined by the container owner, the container type and the number of days that it is in use. I’ve calculated the number of days that it is in use in ElapsedDays query as well as collated the other required information. I can’t work out how to use this information in a second query to calculate the demurrage. An example is:
Owner = CCS
Container Type = 20DY
Elapsed Days = 40
Demurrage = (30 *0) + (10*2) = 20
I’m sure that it can be done through VBA or SQL but that is beyond my skill level. The calculation was originally in Excel using VLOOKUP and SUMPRODUCT.
Attached is a scaled down version of the database.
Owner = CCS
Container Type = 20DY
Elapsed Days = 40
Demurrage = (30 *0) + (10*2) = 20
I’m sure that it can be done through VBA or SQL but that is beyond my skill level. The calculation was originally in Excel using VLOOKUP and SUMPRODUCT.
Attached is a scaled down version of the database.