Issue with multiple joins to one table

[kdw3]

Hi All,

I am fast approaching my wits end :banghead:. I have a very simple query to run but for whatever reason, I am not getting the results that I want. I am working with 2 tables. Table_A contains a field (Field_Status) that has 10+ values which I want to map onto a smaller set of values. Table_B Contains corresponding field (Field_Status) and 4 other fields (CountryA_Conversion, CountryB_Conversion...). The idea is to run a query compare the value in Table A - Field_Status to Table_B - Field_Status and return corresponding value from Table_B - CountryA_Conversion.

The Access generated SQL below works as expected as it only returns records for CountryA_Conversion

SELECT tblcMDR_Input.[Global SAPStatus], tbl_SAP_Conversion.[GL Conversion]
FROM tblcMDR_Input LEFT JOIN tbl_SAP_Conversion ON tblcMDR_Input.[Global SAPStatus] = tbl_SAP_Conversion.[SAP Status];

Once I try and return the value for CountryA_Conversion and CountryB_Conversion, the query falls over and doesnt return any records. The SQL below returns no records

SELECT tblcMDR_Input.[Global SAPStatus], tbl_SAP_Conversion.[GL Conversion], tbl_SAP_Conversion.[LA Conversion]
 
FROM tblcMDR_Input LEFT JOIN tbl_SAP_Conversion ON (tblcMDR_Input.[Lat Am SAPStatus] = tbl_SAP_Conversion.[SAP Status]) AND (tblcMDR_Input.[Global SAPStatus] = tbl_SAP_Conversion.[SAP Status]);

Table_B has no primary key. If someone could please explain why this wont work, i would be very greatful

Thanks for your help

 

[boblarson]

If you are referring to the same field for matching up to multiple fields you need to add the table in more than once. So, for example, if I have a table with EmployeeID and then I have a field ManagerID and both of those really are set using the Employees table lookup, I would need to add the Employees table into my query twice and link one of them to the EmployeeID and the other one to ManagerID.

The same goes for yours and I think you would need tableA in once for each of those fields.

 

[kdw3]

Thanks for the reply. I don't think I am trying to achieve what you are describing. I have attached the SQL below (not enough posts to insert picture) which should clarify it. If someone could tell me the reason this is not returning any records, I would be very intersted. For information, when only GL Conversion and LA Conversion fields are selected for output, the query works as intended.

SELECT TestDB.Key, tbl_SAP_Conversion.[GL Conversion],
tbl_SAP_Conversion.[PL Conversion], 
tbl_SAP_Conversion.[SA Conversion], 
tbl_SAP_Conversion.[LA Conversion]
 
FROM TestDB LEFT JOIN tbl_SAP_Conversion ON (TestDB.[Lat Am SAPStatus] = tbl_SAP_Conversion.[SAP Status]) 
AND (TestDB.[Horizon SAPStatus] = tbl_SAP_Conversion.[SAP Status]) 
AND (TestDB.SASAPStatus = tbl_SAP_Conversion.[SAP Status]) 
AND (TestDB.[Global SAPStatus] = tbl_SAP_Conversion.[SAP Status]);

Thanks

 

[boblarson]

Thanks for the reply. I don't think I am trying to achieve what you are describing. I have attached the SQL below (not enough posts to insert picture)

Actually, if you zip the picture first and upload the zip file, you can post that.


After looking at your SQL, I would like to have you upload a zip file with the database - you can create a separate one with just those tables in it and leave just a few fake records in each field for each table and like for 4 records (unless you just want to upload it all). I'd like to take a look and see what it is you have as I'm not sure what I've initially told you really fits with what you have.

 

[kdw3]

Hi,

I think I have worked it out. I believe it has to do with the 'FROM' clause. My assumption is that because there are 4 'AND' statements in there which all need to be satisfied to return a record. Since there is never a combination like that it returns 0 records. If you would still like to see the database I can send it to you but could you let me know what you think of my conclusions.

Many thanks for your help