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
|
|||
|
|||
Join on a calculated field
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!! |
#2
|
|||
|
|||
Join on a calculated field
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!! |
#3
|
|||
|
|||
Join on a calculated field
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] |
#4
|
|||
|
|||
Join on a calculated field
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.Maste rKey,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!! |
#5
|
|||
|
|||
Join on a calculated field
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.Maste rKey,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? |
#6
|
|||
|
|||
Join on a calculated field
Thanks Marshall...really appreciate your help!
"Marshall Barton" wrote: 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.Maste rKey,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? . |
Thread Tools | |
Display Modes | |
|
|