Access Left Join not working properly
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开发者_JAVA技巧 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.
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 + / -.
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;
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.
精彩评论