|
| 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
May 18th, 2007, 09:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
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
May 18th, 2007, 11:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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
May 18th, 2007, 11:52 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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
May 19th, 2007, 12:06 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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
May 19th, 2007, 12:22 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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 | |
|
|
Linear Mode
