Joins Issues

[pd2004]

Hello,

I am trying to join our REPORT_MM_RECEIPTS table to our MV_ITEMMAST table. I am joining on the ITEM field. The issue is that in the REPORT_MM_RECEIPTS table, the field is trimmed. In the other table, it has the 32 characters of the field filled with blanks. Anyway, although aside from this there are matches, the query returns no results. I understand this and can work around with a sub-query to trim the field in the MV_ITEMMAST table. However here is what I found interesting...

When I join to a third table (Process Level_Tbl), the query returns results from all three tables. Can you tell me what is causing Access to accommodate the field when their is an additional table joined? I appreciate your help.

Thank you,

Pat

Here is the SQL from the first scenario ( returns no records):

SELECT REPORT_MM_RECEIPTS.ITEM, MV_ITEMMAST.ITEM, REPORT_MM_RECEIPTS.REC_DATE
FROM REPORT_MM_RECEIPTS INNER JOIN MV_ITEMMAST ON REPORT_MM_RECEIPTS.ITEM = MV_ITEMMAST.ITEM
WHERE (((REPORT_MM_RECEIPTS.REC_DATE)=#10/29/2015#));

Here is the SQL from the second scenario (returns results from all three tables:

SELECT REPORT_MM_RECEIPTS.ITEM, MV_ITEMMAST.ITEM, REPORT_MM_RECEIPTS.REC_DATE, [Process Level_Tbl].[PROC-LEVEL Code]
FROM (REPORT_MM_RECEIPTS INNER JOIN MV_ITEMMAST ON REPORT_MM_RECEIPTS.ITEM = MV_ITEMMAST.ITEM) INNER JOIN [Process Level_Tbl] ON REPORT_MM_RECEIPTS.PROCESS_LEVEL = [Process Level_Tbl].[PROC-LEVEL Code]
WHERE (((REPORT_MM_RECEIPTS.REC_DATE)=#10/29/2015#));

Screen shots attached.

 

[Andrzejek]

Since "the field is trimmed. In the other table, it has the 32 characters of the field filled with blanks",
did you try Trim them both:
[tt]
SELECT REPORT_MM_RECEIPTS.ITEM, MV_ITEMMAST.ITEM, REPORT_MM_RECEIPTS.REC_DATE
FROM REPORT_MM_RECEIPTS INNER JOIN [blue]
TRIM([/blue]MV_ITEMMAST ON REPORT_MM_RECEIPTS.ITEM[blue])[/blue] = [blue]TRIM([/blue]MV_ITEMMAST.ITEM[blue])[/blue]
WHERE (((REPORT_MM_RECEIPTS.REC_DATE)=#10/29/2015#));[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[pd2004]

Hello Andy,

Thank you for your quick reply. As far as I know, MS Access won't allow me to just insert the trim function into the SQL join. I need to do that in a sub-query first. I understand that this is a way to handle the problem, but I am really interested in why MS Access handles the join when there is a third table with an unrelated join included in the query. Any insights are greatly appreciated.

Thank you,

Pat

 

[Andrzejek]

When both joins are INNER JOIN, and your first 2 tables do not return any records, then whatever you add to it with INNER JOIN will return nothing.

If you want to add 3rd table to your first 2 tables (that return Nothing) then you may want to use LEFT or RIGHT JOIN

If your 2 first tables should return record(s), I would work on it first.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.