If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data LookUp Query
Trying to lookup a value in a table which is dependent on 4 fields in a
second table. Suggestions on how to accomplish? |
#2
|
|||
|
|||
Data LookUp Query
"Steve" wrote in message ... Trying to lookup a value in a table which is dependent on 4 fields in a second table. Suggestions on how to accomplish? How are the two tables related? |
#3
|
|||
|
|||
Data LookUp Query
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 a 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 "Amy Blankenship" wrote: "Steve" wrote in message ... Trying to lookup a value in a table which is dependent on 4 fields in a second table. Suggestions on how to accomplish? How are the two tables related? |
#4
|
|||
|
|||
Data LookUp Query
On Fri, 18 May 2007 13:48:01 -0700, Steve
wrote: Trying to lookup a value in a table which is dependent on 4 fields in a second table. Suggestions on how to accomplish? 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] |
#5
|
|||
|
|||
Data LookUp Query
"Steve" wrote in message ... 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 a 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 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 |
Thread Tools | |
Display Modes | |
|
|