Left Right Straight Backwards Zigzag Join?

[jv2]

would anyone be able to tell me how to construct a query that will return all values from another query even if one of the tables has noe related records?
I have query which joins another query and a table... the query works fine if that table has related data... then the query returns all the records as it should... but this table will not necessarily have records for all of the items in the first query...
How do I get this query to list all record returned by the query even when there is nothing in the table when filtered with the criteria?

SELECT DISTINCTROW Qry_Stock.*, NewSTOCK.NewStock
FROM NewStock LEFT JOIN Qry_STOCK ON (Qry_Stock.StockID = NewSTOCK.StockID) AND (Qry_Stock.SiteID = NewSTOCK.SiteID)
WHERE (((Format([NewStockDate],"m"))=[forms]![Reporting]![select_month].[value]));

 

[FoFa]

Try swaping the table/query around:
FROM Qry_STOCK LEFT JOIN NewStock ON (Qry_Stock.StockID = NewSTOCK.StockID) AND (Qry_Stock.SiteID = NewSTOCK.SiteID)

 

[jv2]

thank you for the suggestion...
but this gives me the same results... the query works if there is data for the selected month in table NewStock... but when there isn't... I dont get anything back from the query...

 

[jv2]

problem solved by the lovely people at Experts Exchange...

I now have QryA

SELECT Format([NewStockDate],"m") AS NSMonth, *
FROM NewSTOCK
WHERE (((Format([NewStockDate],"m"))=[forms]![Reporting]![select_month].[value]));

and MainQuery

SELECT DISTINCTROW Qry_Stock.*, IIf(IsNull(qryA.NewStock),0,qryA.NewStock) As NewStockAmnt
FROM Qry_Stock LEFT JOIN qryA ON (Qry_Stock.StockID = qryA.StockID) AND (Qry_Stock.SiteID = qryA.SiteID);

 

[FoFa]

Sorry, I missed the WHERE clause, the before menetioned changed plus this should work also:
WHERE (((Format([NewStockDate],"m"))=[forms]![Reporting]![select_month].[value]) OR [NewStock].[SiteID] IS NULL);