Access Left Join not working properly

Question

In access I wrote this query:

Select
  I.sysid, I.MemberNumber, I.Date, I.Distributer,
  F.MemberNumber as FMember, F.Date as FDate, I.Distributer as FDistributer
From Initial as I
Left Join Final as F ON
  I.MemberNumber=F.MemberNumber and
  I.Distributer=F.Distributer and
  I.Date>=F.Date-14 and
  I.Date<=F.Date+14;

But the left join is not behavior properly. There are fewer rows in this table then there are in Initial... but it should be keeping ALL rows from initial, because I am using a left join, right? I have found several rows in initial (like sysid=7, which is Initial's key) that just isn't coming into this table.

Answer 1

It may have to do with your AND logic. Add some ( ) parenthesis to this to include it all like so:

Select
  I.sysid, I.MemberNumber, I.Date, I.Distributer,
  F.MemberNumber as FMember, F.Date as FDate, I.Distributer as FDistributer
From Initial as I
Left Join Final as F ON
  (I.MemberNumber=F.MemberNumber and
  I.Distributer=F.Distributer and
  (I.Date>=F.Date-14) and
  (I.Date<=F.Date+14));

Also I think there is a dateadd function, I'd use that instead of + / -.

Answer 2

If you're in the Query Designer, make sure all the filters are cleared. I've built your tables and sql and can't reproduce your error.

SELECT I.sysid
    , I.MemberNumber
    , I.Dated
    , I.Distributer
    , F.MemberNumber
    , F.Dated AS FDated
    , F.Distributer AS FDistributer
FROM Initial AS I 
LEFT JOIN Final AS F 
ON I.Distributer = F.Distributer 
      AND I.MemberNumber = F.MemberNumber
      AND I.Dated>=F.Dated-14 
      AND I.Dated<=F.Dated+14;

Answer 3

Try adding additional fields to the original table and using update queries to add extra columns. This way you can be sure you won't drop any columns.