Different Calculations for each Vendor (record)
I am building my first database so bear with me here.
I am trying to build a cost estimater for a video editing project I am working on. Currently I am working with two tables, Clips and Vendors. I have been able to sucessfully get data on each clip in by exporting a edl file from my editing software and importing it into the clips table. From the file names of the clips I can determin which vendor it came from and through a relationship I can track which clips came from each. Also from the in and out times generated from the edl I can determin how long each clip is. So far this is working great and I can see how many total clips or seconds of clips are being used from any single vendor.
My problem comes when I try to apply a formula that will multiply out my cost. Each vendor has a different set of variables to multiply by so I can't make one formula that fits them all. Instead I need to have a seprate formula for each and store that in the vendors record so it can be used to multiply out the final cost.
I guess my question is if there is a way to store a record specific formula as opposed to programing one that would effect the whole table?
I have tried to store the formula as text in a text field but when I create a calculation field to pull it into I can't recognize it as a calculation just as text.
Is this the correct way to go about it? Any help would be appriciated.