Joining date fields from 2 separate tables in Access 2010. Outer join is acting like an inner join

Question

Blockquote

I am joining date fields from 2 separate tables in Access 2010. In 'Join properties' i am including all records from the table on the left, and only those records that match from table on right. For some reason it is treating it like a regular join where only records that match from both tables appear. Example: Left table has 31 records for every day in May - May 1 - May 31 2015. Right table has only 19 records with dates in May plus other fields i want to see when there is a match. When i run the query it returns only 19 records, not the 31 records i am expecting. I've confirmed both tables are formated for dates, and even tried changing data type using 'Clng' to confirm the records match. I am using 'Design' view of query - i'm not at all good with Sql or Vba. All the sites i've searched refer to this problem using SQL..

SQL below for those who can work with this:

SELECT 
    [Raven Download 10 table].Region
    , [Raven Download 10 table].Market
    , [Raven Download 10 table].Location
    , [Date table].dDate
    , [Raven Download 10 table].SessEndDate
    , [Raven Download 10 table].MaxOfDwnldStat
FROM 
    [Raven Download 10 table] 
RIGHT JOIN 
    [Date table] 
ON 
    [Raven Download 10 table].SessEndDate = [Date table].dDate
WHERE 
    ((([Raven Download 10 table].Location)="hay river"));

Answer 1

You can't specify the criteria on the "outer join" table in the way you are. You are then basically forcing it into an inner join again. Rather try this.

SELECT 
      RavenHay.Region
    , RavenHay.Market
    , RavenHay.Location
    , [Date table].dDate
    , RavenHay.SessEndDate
    , RavenHay.MaxOfDwnldStat
FROM 
    [Date table] 
LEFT JOIN
    (
    select 
        [Raven Download 10 table].Region
        , [Raven Download 10 table].Market
        , [Raven Download 10 table].Location
        , [Raven Download 10 table].SessEndDate
        , [Raven Download 10 table].MaxOfDwnldStat
    from 
        [Raven Download 10 table]  
    where
        [Raven Download 10 table].Location="hay river" 
    ) AS RavenHay
ON 
    [Date table].dDate = RavenHay.SessEndDate 

Update:

If you are not comfortable using the SQL, then create a Access Query using [Raven Download 10 table] and it's associated query, i.e. limited by hayriver and then do a right join against that query.