Data LookUp Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to lookup a value in a table which is dependent on 4 fields in a
second table. Suggestions on how to accomplish?
 
Steve said:
Trying to lookup a value in a table which is dependent on 4 fields in a
second table. Suggestions on how to accomplish?
Click to expand...

How are the two tables related?
 
Amy,
The database involves the capture of commodity trading records. I am trying
to pull the ClrgBrkrFee from the TBL - Broker Fee Schedule and place it in
the field corresponding with the appropriate record in the TBL - Deal Entry
Database. The Exchange/Commodity/ContractType/ClearingBroker combination
determines which ClrgBrkrFee value should be pulled from TBL - Broker Fee
Schedule. The specific tables referred to are:

TBL - Broker Fee Schedule
Exchange
Commodity
ContractType
ClearingBroker
ClrgBrkrFee
ExecBrkrFee
ExchangeFee
NFAFee

TBL - Deal Entry Database
TradeID (Primary Record, Identity)
Entity
Trader
FillDate
FillTime
Exchange
Commodity
ContractType
ClearingBroker

Hope that makes sense and thanks so much for your response.

Steve
 
Trying to lookup a value in a table which is dependent on 4 fields in a
second table. Suggestions on how to accomplish?
Click to expand...

Create a Join between the two tables, joining on all four fields.

For a more detailed answer feel free to post a more detailed question.

John W. Vinson [MVP]
 
Steve said:
Amy,
The database involves the capture of commodity trading records. I am
trying
to pull the ClrgBrkrFee from the TBL - Broker Fee Schedule and place it in
the field corresponding with the appropriate record in the TBL - Deal
Entry
Database. The Exchange/Commodity/ContractType/ClearingBroker combination
determines which ClrgBrkrFee value should be pulled from TBL - Broker Fee
Schedule. The specific tables referred to are:

TBL - Broker Fee Schedule
Exchange
Commodity
ContractType
ClearingBroker
ClrgBrkrFee
ExecBrkrFee
ExchangeFee
NFAFee

TBL - Deal Entry Database
TradeID (Primary Record, Identity)
Entity
Trader
FillDate
FillTime
Exchange
Commodity
ContractType
ClearingBroker
Click to expand...

I suspect you have a normalization problem. You should not be entering four
identical pieces of information in two different tables. However, you could
do something like:

SELECT NFAFee FROM [Broker Fee Schedule] AS BrokerFee INNER JOIN [Deal Entry
Database] AS DealEntry ON BrokerFee.Exchange = DealEntry.Exchange AND ON
BrokerFee.Commodity = DealEntry.Commodity AND ON BrokerFee.ClearingBroker =
DealEntry.ClearingBroker AND ON BrokerFee.ContractType =
DealEntry.ContractType;

HTH;

Amy
 
Back
Top