Join on a calculated field
in [Queries]
|
From: accesswanabe on 11 May 2010 15:21 I am trying to provide a SQL query to the rowsource of a combobox. I am trying to use two tables to do this but am not sure how to go about it. The table for the combobox list is a contractor list and has two fields pertinent to what I need to do; ContractorName and ContractorKey. A ContractorKey data example is: USPipe. The other table is a pricelist table, and has a price item key on every record. An example of the pricelist table key is: USPipe_01_01A0. Not every contractor has price items. What I would like to do is join the tables so that the contractor list shows only contractors that have related price list records. Is there a way to do that without using an intermediate query by joining on a calculated field or something like that? Thanks in advance!!
From: KARL DEWEY on 11 May 2010 16:17 You can do the calculated join in SQL view but will not be able to work it thereafter in design view. FROM TableA LEFT JOIN TableB ON TableA.ContractorKey = Left(TableB.[pricelist], InStr(TableB.[pricelist], "_")-1) -- Build a little, test a little. "accesswanabe" wrote: > I am trying to provide a SQL query to the rowsource of a combobox. > > I am trying to use two tables to do this but am not sure how to go about it. > The table for the combobox list is a contractor list and has two fields > pertinent to what I need to do; ContractorName and ContractorKey. A > ContractorKey data example is: USPipe. The other table is a pricelist > table, and has a price item key on every record. An example of the pricelist > table key is: USPipe_01_01A0. > > Not every contractor has price items. What I would like to do is join the > tables so that the contractor list shows only contractors that have related > price list records. Is there a way to do that without using an intermediate > query by joining on a calculated field or something like that? > > Thanks in advance!!
From: Marshall Barton on 12 May 2010 10:04 accesswanabe wrote: >I am trying to provide a SQL query to the rowsource of a combobox. > >I am trying to use two tables to do this but am not sure how to go about it. > The table for the combobox list is a contractor list and has two fields >pertinent to what I need to do; ContractorName and ContractorKey. A >ContractorKey data example is: USPipe. The other table is a pricelist >table, and has a price item key on every record. An example of the pricelist >table key is: USPipe_01_01A0. > >Not every contractor has price items. What I would like to do is join the >tables so that the contractor list shows only contractors that have related >price list records. Is there a way to do that without using an intermediate >query by joining on a calculated field or something like that? You can join tables in calculatd expressions, but you must use SQL view to do it. Maybe something like: SELECT ContractorList.ContractorName, ContractorList.ContractorKey FROM ContractorList INNER JOIN PriceList ON ContractorList.ContractorKey = Left(PriceList.keyfield, Len(ContractorList.ContractorKey)) -- Marsh MVP [MS Access]
From: accesswanabe on 13 May 2010 14:18 Thanks guys...both methods work great!! Through experimentation I found an additional way: SELECT ContractorList.ContractorKey, ContractorList.Contractor FROM ContractorList, Pricelist WHERE ContractorList.ContractorKey=Left$(Pricelist.MasterKey,13); The instr function helps make the equality more dynamic. "accesswanabe" wrote: > I am trying to provide a SQL query to the rowsource of a combobox. > > I am trying to use two tables to do this but am not sure how to go about it. > The table for the combobox list is a contractor list and has two fields > pertinent to what I need to do; ContractorName and ContractorKey. A > ContractorKey data example is: USPipe. The other table is a pricelist > table, and has a price item key on every record. An example of the pricelist > table key is: USPipe_01_01A0. > > Not every contractor has price items. What I would like to do is join the > tables so that the contractor list shows only contractors that have related > price list records. Is there a way to do that without using an intermediate > query by joining on a calculated field or something like that? > > Thanks in advance!!
From: Marshall Barton on 13 May 2010 15:34
Your right. That query is the equivalent to the INNER JOIN query and, I believe the query optimizer will translate one of them(?) to the other. The difference is that your style query does not require SQL view, so it depends on how you like to write queries,. -- Marsh MVP [MS Access] accesswanabe wrote: >Thanks guys...both methods work great!! Through experimentation I found an >additional way: > >SELECT ContractorList.ContractorKey, ContractorList.Contractor FROM >ContractorList, Pricelist >WHERE ContractorList.ContractorKey=Left$(Pricelist.MasterKey,13); > >The instr function helps make the equality more dynamic. > >"accesswanabe" wrote: > >> I am trying to provide a SQL query to the rowsource of a combobox. >> >> I am trying to use two tables to do this but am not sure how to go about it. >> The table for the combobox list is a contractor list and has two fields >> pertinent to what I need to do; ContractorName and ContractorKey. A >> ContractorKey data example is: USPipe. The other table is a pricelist >> table, and has a price item key on every record. An example of the pricelist >> table key is: USPipe_01_01A0. >> >> Not every contractor has price items. What I would like to do is join the >> tables so that the contractor list shows only contractors that have related >> price list records. Is there a way to do that without using an intermediate >> query by joining on a calculated field or something like that? |