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
|
|||
|
|||
Multiple left joins
I'm trying to develop a conditional join that will allow me lookup the data
in one field and if it doesn't match, it will look to another field to match. I currently have the following setup: I'm using a left join to include all records from "Table 1 Column A" and only those records in "Table 2 Column A" where the joined fields are equal. Here's what I'm looking to achieve: If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table 1 Column A" where the fields match in "Table 2 Column B" |
#2
|
|||
|
|||
Multiple left joins
That is a bit complex ... can you describe the kind of data you have that
could have a potential matching value in one field some times and a different field other times? Often when what you're looking for could be in Column A or Column B (or Column C, or ...), this is because the data is structured more like a spreadsheet than a relational database. More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "KS" wrote in message ... I'm trying to develop a conditional join that will allow me lookup the data in one field and if it doesn't match, it will look to another field to match. I currently have the following setup: I'm using a left join to include all records from "Table 1 Column A" and only those records in "Table 2 Column A" where the joined fields are equal. Here's what I'm looking to achieve: If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table 1 Column A" where the fields match in "Table 2 Column B" |
#3
|
|||
|
|||
Multiple left joins
In design view when you place a second instance of Table2 above the grid
Access adds a suffix to name it Table2_1. Try this -- SELECT Table1.Column_A, IIf([Table2].[Column_A] Is Null,[Table2_1].[Column_B],[Table2].[Column_A]) AS Match_Test FROM (Table1 LEFT JOIN Table2 ON Table1.Column_A = Table2.ClientID) LEFT JOIN Table2 AS Table2_1 ON Table1.Column_A = Table2_1.Column_B; -- Build a little, test a little. "KS" wrote: I'm trying to develop a conditional join that will allow me lookup the data in one field and if it doesn't match, it will look to another field to match. I currently have the following setup: I'm using a left join to include all records from "Table 1 Column A" and only those records in "Table 2 Column A" where the joined fields are equal. Here's what I'm looking to achieve: If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table 1 Column A" where the fields match in "Table 2 Column B" |
#4
|
|||
|
|||
Multiple left joins
To design an updatable query plan, Access needs to
be able to uniquely identify which record in which table will be updated when you update a recordset. So the design you describe, where the join could be on one field, or on another field, depending on the data, will never be completely updatable in a plain JET query. You can make both joins, and then select the data from one join or from the other join, and either the selected (calculated) data will not be updatable, or the whole recordset will not be updatable, depending on the nature of the relationship. (david) "KS" wrote in message ... I'm trying to develop a conditional join that will allow me lookup the data in one field and if it doesn't match, it will look to another field to match. I currently have the following setup: I'm using a left join to include all records from "Table 1 Column A" and only those records in "Table 2 Column A" where the joined fields are equal. Here's what I'm looking to achieve: If "Table 1 Column A" does not match "Table 2 Column A", look to join "Table 1 Column A" where the fields match in "Table 2 Column B" |
Thread Tools | |
Display Modes | |
|
|