JOIN confusion...

[ineedahelp]

When I run a "without matching" query on DailyPrice and DATAFILE_111306, I get a list of 300+ instances. I thought that the query below would give me ALL records in DATAFILE even if there were no corresponding records in DailyPrice. In fact, none of the names on the without matching query ended up on the list created from the query below. Any help with why this is happening?



SELECT DISTINCT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice
FROM DailyPrice RIGHT JOIN DATAFILE_111306 ON DailyPrice.Symbol = DATAFILE_111306.Symbol
WHERE (((DailyPrice.LocateDate) Between Now() And (Now()-8)))
ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;

[NeoPa]

If you do a LEFT or RIGHT JOIN (as you've done), when the data is a record with no match, the fields of the other table are all Null.
Your WHERE clause tells the query to drop ALL records where there is no match because it doesn't test for Null.
Try something like :-

SELECT DISTINCT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice
FROM DailyPrice RIGHT JOIN DATAFILE_111306 ON DailyPrice.Symbol = DATAFILE_111306.Symbol
WHERE (((DailyPrice.LocateDate) IS Null) OR ((DailyPrice.LocateDate) Between (Date()-8) And Date()))
ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;

Notice I changed Now() to Date() - Can you tell me why I would do that?
And I put Date()-8 first in the Between clause for clarity.
Also, bear in mind when you sort by fields that may exist only as Nulls, the unmatched records will all appear together at one end.

[MMcCarthy]

When I run a "without matching" query on DailyPrice and DATAFILE_111306, I get a list of 300+ instances. I thought that the query below would give me ALL records in DATAFILE even if there were no corresponding records in DailyPrice. In fact, none of the names on the without matching query ended up on the list created from the query below. Any help with why this is happening?



SELECT DISTINCT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice
FROM DailyPrice RIGHT JOIN DATAFILE_111306 ON DailyPrice.Symbol = DATAFILE_111306.Symbol
WHERE (((DailyPrice.LocateDate) Between Now() And (Now()-8)))
ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;

You've taken all your fields from the left side DailyPrice. The query isn't returning any data from DATAFILE_111306 so there is nothing to show.

Try this:

SELECT DATAFILE_111306.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice
FROM DailyPrice RIGHT JOIN DATAFILE_111306 ON DailyPrice.Symbol = DATAFILE_111306.Symbol
WHERE (((DailyPrice.LocateDate) Between Now() And (Now()-8)))
ORDER BY DATAFILE_111306.Symbol, DailyPrice.LocateDate DESC;

[NeoPa]

The reason you NEVER use Now() in date checks is that Now() is a Date AND Time, whereas the fields you are checking against are just Dates.
This means if you want a date range between yesterday and today - you would actually only find items for today. This is because dates (alone) are stored as midnight of the date in question so would fall outside of the range

Between Now()-1 And Now()

Just a point - if you don't check for nulls explicitly in your WHERE clause, all non-matched records will be dropped from the query output.

[ineedahelp]

Thank you both for your help. I am starting to grasp the access logic! Using the "Null" statement made sense and WORKED! Thanks again!