Join on a calculated field

[accesswanabe]

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!!

 

[KARL DEWEY]

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)

 

[accesswanabe]

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]

Thanks Marshall...really appreciate your help!

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]

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.

.