Left Join Help

[cdw0308]

I am having problems with my query. It is based on 4 tables: Delivery, Delivery_Subform, Return, Return_Subform. The code below works but I need to take it one step further by Showing all the items in Delivery_Subform.tran_descrip and only those that match from Return_Subform.ret_descrip. I believe i need a left join for this but i am not sure on how to proceed in making it work.
I need to be able to see all items in the Delivery_Subform. tran_descrip field as well as the ones that meet my criteria in the where statement.

SELECT Delivery_Subform.tran_descrip, Delivery.tran_to, Return.ret_from, Delivery.tran_rec_by_date, Return.ret_rec_by_date, Delivery_Subform.tran_item_num, Delivery_Subform.tran_unit_num, Delivery_Subform.tran_type, Delivery_Subform.tran_price, Delivery_Subform.tran_model_num, Delivery_Subform.tran_serial_num
FROM Return INNER JOIN Return_Subform ON Return.ret_ID = Return_Subform.ret_ID, Delivery INNER JOIN Delivery_Subform ON Delivery.tran_ID = Delivery_Subform.tran_ID
WHERE (((Delivery_Subform.tran_descrip)=[Return_Subform].[ret_descrip]) AND ((Delivery.tran_to)=[Return].[ret_from]));

thanks,
cdw

 

[lespaul]

In the query design view, right click on the "relationship" line between Delivery_Subform.tran_Desc and Return_SubForm.ret_desc, select 'Join Properties'. Your choices will be:

1. Only include rows where the joined fields from both tables are equal.

2. Include ALL records from 'Delivery_Subform' and only those records from Return_SubForm' where the joined fields are equal

3. Include ALL records from 'Return_SubForm' and only those records from 'Delivery_Subform' where the joined fields are equal.


You are going to want to select the ALL records from 'Delivery_Subform' and only those from 'Return_SubForm' where the joined fields are equal.

Now, after completing that, look at your SQL view, you'll see that instead of having the inner join it will have a left or right join.

HTH

Leslie

 

[Quehay]

cdw,

You've got a mix of SQL 89 and 92 JOINs in your SQL. (89 is the 'where t1.field=t2.field' style and 92 is the 'INNER JOIN ON t1.field=t2.field' syntax) with a self join of Delivery_Subform to itself. The fact that you're joining this table to itself with a criterion based on field for which you say that you want to see all entries is contradictory. It's hard to debug this without having your tables to write against but...

If you want an outer join (left and right are really arbitrary) with a table that itself has a natural join with another table you'll have trouble.

Creating a subquery, which in Access GUI will then be available as a 'table', and then creating the OUTER JOIN with it in a subsequent query is the way to go.



Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com