Query referencing 3 tables with 2 relationships, ignoring rows that only have 1 relationship

[Nozomi02]

My company has a monster excel spreadsheet that's had a huge issue with data integrity/redundancy. To prevent this issue in future, I'm attempting to build it into an Access database instead. I have a lot of experience in excel, but no experience in Access, so this is all new to me.


The goal of the information is to track our long-term contracts and calculate the estimated future revenue. This calculation involves build rates supplied by our customers.


Our sane customers do this build rate based on their end product, however we have one insane customer that gives us the build rate based on our part number. There are also a few outliers from the sane customers that have rates specific to the part we provide.


In excel, the formula looks like this:


=IF(2015<YEAR(Contract Start),0,IF(2015>YEAR(Contract End),0,(Price)*(1+Assumed Price Up/Down 2015)*(IFERROR(IF(Customer=”Insane”,VLOOKUP(Part #,'Insane Rates'!$A:$L,2,),VLOOKUP(End Product,'Sane Rates'!$A:$L,2,)),VLOOKUP(Part #,'Insane Rates'!$A:$L,2,)))))


This calculates revenue for 2015 (assuming 2015 is within the contract period), using the correct build rates for sane and insane customers, and correctly accounts for the outliers.


In Access I have one table with the contract details (customer, part number, end product the part will be used in, contract period, pricing over the life of the contract, etc.), another table with the sane customer build rates that I've linked to the end product field, and a third table with the insane customer build rates and the sane customer outliers that link to the part number.


I've built a query that calculates this(except I haven't figured out how to work in the outliers yet), however, the only results I'm getting are from contracts that have both relationships (ex: both the end product and the part number exist in the other two tables); it’s not calculating contracts that have just one (ex: end product exists in sane table, but part number doesn't exist in insane table).


How can I get my query to give me results for all contracts? Basic format of my query formula is below:

CCur(IIf(2015<Year([Contract Start]),0,IIf(2015>Year([Contract End]),0,([Price])*(1+[LTA]![Assumed Price Up/Down 2015])*IIf([LTA]![Customer]="Insane",[Insane Rates]![Insane Rate 2015],[Build Rates]![Build Rate 2015]))))

[June7]

Options:

1. Save ID of rate record then retrieve rate by joining tables

2. Save the actual rate with data

3. DLookup

4. VBA function if DLookup is inadequate