Output a field by matching data in fields in Table 1 to both data in fields and fields themselves in Table 2 in Access
<p><strong>Gist:</strong></p>

<p>How do I output data stored in Table 2 Field X for each row in Table 1 by matching Table 1 Field A to Table 2 Field A and Table 1 Field B to the corresponding Field (B, C, D, E, etc.) in Table 2?</p>

<p>This might be nauseatingly simple so feel free to skip through some of this if you feel like I'm over-backgrounding!</p>

<p><strong>Usage Case:</strong></p>

<p>I inherited an Access database that tracks individual sales trips conducted by employees of contractors my company has hired to conduct sales. My company has negotiated a single hourly rate for all employees of each contractor for a given sales account at our company.</p>

<p>I have two Access tables:</p>

<p><strong>1. SalesTrips</strong> contains all instances of sales trips conducted by the employees of the contractors.</p>

<p><em>Format (relevant fields only):</em></p>

<pre><code>TripID     Sales Account     Contractor     Employee     Hours Contracted
1          800               EarthCO        Maurice      10
2          800               WindCO         Wade         36
3          900               FireCO         Don          17
4          800               FireCO         Sherry       52
</code></pre>

<p><strong>2. Contracts</strong> contains the hourly rates for each sales account that my company will pay to contractors' employees.</p>

<p><em>Format (relevant fields only - Rates are $/hr):</em></p>

<pre><code>Sales Account     EarthCO Rate     WindCO Rate     FireCO Rate
800               20               18              23
900               24               22              26
</code></pre>

<p><strong>Business Problem:</strong></p>

<p>My predecessor manually (read: in Excel) calculated necessary payments for each sales trip, but did not factor in evidence that some employees didn't complete all of their contracted hours due to sick leave (but were still paid for it). So, I built a query that pulls from a third table (SickLeave) of sick leave information and outputs, for each TripID, an "Hours Worked" quantity. Let's call that field "HoursWorked" in the query "TrueHours".</p>

<p>For each TripID, I need to match its Sales Account and Contractor from the SalesTrips table to the Sales Account and contractor rate (choose the correct field) on the Contracts table. Then, somewhat obviously, I need to multiply the "HoursWorked" quantity (output from my query, and updated frequently based on entries in the SickLeave table) by the correct payment rate (negotiated, static, and stored in the Contracts table).</p>

<p><strong>Example Output:</strong></p>

<p>I want to know that we need to pay Maurice of EarthCO $200 and Sherry of FireCO $1,196 (they had no sick leave).</p>

<p><strong>I've Considered:</strong></p>

<ol>
<li><p>Using a lookup field in the SalesTrips table that matches Sales Account and Contractor. But I'm not a fan of this option as I don't really like the idea of storing the rate in the SalesTrips table, and want to limit lookup fields on this table for interoperability with other systems.</p></li>
<li><p>Using an IIF statement or a DLookup within a query, but I've struggled to match an entry in a field (e.g. "WindCO") on the SalesTrips table to a field itself (e.g. "EarthCO Rate") on the Contracts table.</p></li>
</ol>

<p>I have very limited Access and SQL experience, and apparently so did my predecessor, so I'm relatively lost on this one despite many hours of research. It's frustrating because I completely understand how this would work in Excel but not in a relational database. I'd prefer a query or some similar option and would like to avoid restructuring either table, although I'm open to it if it's necessary.</p>

<p>As a longtime SO searcher and first-time poster, I hope y'all can help out with this one!</p>