A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Join on a calculated field



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 08:21 PM posted to microsoft.public.access.queries
accesswanabe
external usenet poster
 
Posts: 5
Default 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  
Old May 11th, 2010, 09:17 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 12th, 2010, 03:04 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 13th, 2010, 07:18 PM posted to microsoft.public.access.queries
accesswanabe
external usenet poster
 
Posts: 5
Default 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  
Old May 13th, 2010, 08:34 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 14th, 2010, 11:29 PM posted to microsoft.public.access.queries
accesswanabe
external usenet poster
 
Posts: 5
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.