Hi. I was wondering if anyone knew of a way to have a query that runs a specific nested IIf statement based on a certain company. The way I have it set up now is in the main query, and it works just fine, but it will only work on 1 company. I then made a table that had companies and the nested IIf statement that went with each one. When I tied the company field together and dropped the IIf field into the query, it showed the IIf function written out instead of the calculated number. Any suggestions would be appreciated.
I am working on a way to compare how hogs would be paid if they were processed in different plants. The first thing I am talking about that is different for each company is the premiums/discounts being paid for percent lean (The leaness of the hog). For Example, company A's payouts look like this:
I want the query to run the right Lean Premium Factor Calculation based on which company was being compared. Right now, I have to put the manual calculation in and run it individually. I hope this is specific enough. I can get more specific if you want, but I'm not quite sure how best to explain it.
Create a separate table with the 'leanness' bands and relate it to the company table ie. have a table with 4 columns 'CoID', UpperLeanness, Payout, LowerLeannes with values CompanyA,1.00,6,0.58; CompanyA,0.58,5,0.56; CompanyA,0.56,4,0.54; etc. until you've entered all 'bands' for Company A.
Relate the table to the company table and for whatever field defines what the specific 'leanness' is set the following criteria: <=[UpperLeanness] And >[LowerLeanness]
This should enable you to easily expand your list of companies as well as amend the 'bands' that define the payouts.