sql - Output a field by matching data in fields in Table 1 to both data in fields and fields themselves in Table 2 in Access -

gist:

how output data stored in table 2 field x each row in table 1 matching table 1 field table 2 field , table 1 field b corresponding field (b, c, d, e, etc.) in table 2?

this might nauseatingly simple feel free skip through of if feel i'm over-backgrounding!

usage case:

i inherited access database tracks individual sales trips conducted employees of contractors company has hired conduct sales. company has negotiated single hourly rate employees of each contractor given sales account @ our company.

i have 2 access tables:

1. salestrips contains instances of sales trips conducted employees of contractors.

format (relevant fields only):

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 

2. contracts contains hourly rates each sales account company pay contractors' employees.

format (relevant fields - rates $/hr):

sales account     earthco rate     windco rate     fireco rate 800               20               18              23 900               24               22              26 

business problem:

my predecessor manually (read: in excel) calculated necessary payments each sales trip, did not factor in evidence employees didn't complete of contracted hours due sick leave (but still paid it). so, built query pulls third table (sickleave) of sick leave information , outputs, each tripid, "hours worked" quantity. let's call field "hoursworked" in query "truehours".

for each tripid, need match sales account , contractor salestrips table sales account , contractor rate (choose correct field) on contracts table. then, obviously, need multiply "hoursworked" quantity (output query, , updated based on entries in sickleave table) correct payment rate (negotiated, static, , stored in contracts table).

example output:

i want know need pay maurice of earthco $200 , sherry of fireco $1,196 (they had no sick leave).

i've considered:

1. using lookup field in salestrips table matches sales account , contractor. i'm not fan of option don't idea of storing rate in salestrips table, , want limit lookup fields on table interoperability other systems.

2. using iif statement or dlookup within query, i've struggled match entry in field (e.g. "windco") on salestrips table field (e.g. "earthco rate") on contracts table.

i have limited access , sql experience, , apparently did predecessor, i'm relatively lost on 1 despite many hours of research. it's frustrating because understand how work in excel not in relational database. i'd prefer query or similar option , avoid restructuring either table, although i'm open if it's necessary.

as longtime searcher , first-time poster, hope y'all can out one!

i rethink database design.

table 1 ok

table 2 not idea define fields containing 'data' in field-name: hard-code client name in source code of program.

try have more relational approach, defining 3 fields:

sales account contractor rate  sales account     contractor     rate 800               earthco         20 800               windco          18 800               fireco          23 900               earthco         24 900               windco          22 900               fireco          26 

so can build query joining table1 table2 query this:

select table1.employee, table2.rate, table1.[hours contracted],  [rate]* [hours contracted] total  table1 inner join table2  on (table1.contractor = table2.contractor) , (table1.[sales account] = table2.[sales account]); 

the result (with data):

employee    rate    hours contracted        total maurice         20          10                   200 wade            18          36                   648 don             26          17                   442 sherry          23          52                  1196 

hope helps