Left Join Help

[sccofer]

I have two tables Returns and ReturnDetails, I want to receive back
from a query all records from Returns and only records from
ReturnDetails that = specific date. The below query almost works, the
problem I am having is when there is a record in ReturnDetails with a
date different than the criteria date, in this case 3/1/2006, the
record is not showing up in the results. Obviously this is becuase
Rtn_Detail.Month_Processed <> 3/1/2006 and it also isn't equal to null.


I want ALL records from the Returns table the meet the FREQ criteria
every time and cant get my head around a solution. I think I have been
looking at it to long as it doesnt seem like it should be that
difficult. Thanks in advance for any pointers...








SELECT [T RETURNS].RTN, [T RETURNS].ST, [T RETURNS].ENTITY, [T
RETURNS].[RTN TYPE], [T RETURNS].PREP, [T RETURNS].FREQ,
Rtn_Detail.Month_Processed, Rtn_Detail.Amount_Paid,
Rtn_Detail.Date_Delivered, Rtn_Detail.Review_Date, Rtn_Detail.Notes,
Rtn_Detail.Discount_Taken
FROM [T RETURNS] LEFT JOIN Rtn_Detail ON [T RETURNS].RTN =
Rtn_Detail.[RTN Number]
WHERE
((([T RETURNS].FREQ)=1 Or ([T RETURNS].FREQ)=7 Or ([T RETURNS].FREQ)=11
Or ([T RETURNS].FREQ)=15 Or ([T RETURNS].FREQ)=14) AND
((Rtn_Detail.Month_Processed)=#3/1/2006#))
OR ((([T RETURNS].FREQ)=1 Or ([T RETURNS].FREQ)=7 Or ([T
RETURNS].FREQ)=11 Or ([T RETURNS].FREQ)=15 Or ([T RETURNS].FREQ)=14)
AND ((Rtn_Detail.Month_Processed) Is Null))

ORDER BY
[T RETURNS].RTN DESC;

 

[Michel Walsh]

Hi,


SELECT y.RTN,
y.ST,
y.ENTITY,
y.[RTN TYPE],
y.PREP,
y.FREQ,
x.Month_Processed,
x.Amount_Paid,
x.Date_Delivered,
x.Review_Date,
x.Notes,
x.Discount_Taken

FROM [T RETURNS] As y
LEFT JOIN
(SELECT *
FROM Rtn_Detail
WHERE Month_Processed =#3/1/2006#)
As x
ON y.RTN = x.[RTN Number]

WHERE y.FREQ IN(1, 7, 11, 15, 14)




Hoping it may help,
Vanderghast, Access MVP

 

[sccofer]

worked like a charm! Thank you so much. If I could bother you with
one more thing - could you just briefly go through your SQL and explain
the section after LEFT JOIN so that I can understand it? Translate the
SQL into english so to speak!

I would really appreciate it! Thanks again!!

 

[Michel Walsh]

Hi,


The (SELECT ... ) statement is what we can call a "virtual table". That
select statement picks some records of the "real" table and that subset of
records is then used "as if" it was a table in the database. Here, our
virtual table is made of the records where Month_Processed= given date, and
no other records. The virtual table is "aliased" (named) x. The table [T
Returns] is also aliased, as y. That help the typing, avoid typo error and
makes the statement shorter, but otherwise, is just that, "another name".
The ON clause is as you did, but it implies your virtual table x.

The where clause uses IN list rather than a lengthily OR... OR... OR...
Again, just a question to make the statement shorter.

Instead of using a virtual table as we did, you can use a saved query. You
make a saved query with the SELECT ... statement we used to define the
virtual table, but then,you use that query, instead of using a table (or a
virtual table) in the "main" query. Sometimes, it is faster to do so.


Hoping it may help,
Vanderghast, Access MVP