SQL Joins , exclude unwanted records without using WHERE 4

[1DMF]

Hi,

Ok , possibly a stupid question coming up and feel free to tell me if it is ;-)

I have a query...

Set rs = CurrentDb.OpenRecordset("SELECT dbo_Business_Register.[Prod_Type],dbo_Business_Register.[Rec_Type],dbo_Business_Register.[Commission],Contacts.[CompanyName] FROM [dbo_Business_Register] LEFT JOIN [Contacts] ON Contacts.[Membership Number] = dbo_Business_Register.[Adv_MemNo] WHERE ([Ins_Lnk] is null) AND (Sub_Date BETWEEN #" & SDate & "# AND #" & EDate & "#) AND ([Status] = 'SUB') AND [Rec_Type] <> 'LOAN'", dbOpenSnapshot, dbSeeChanges)

The senario is that in the [dbo_Business_Register] table there are some dummy records for Adv_MemNo = 'Test Account'.

However, in the actual members database [Contacts] there is no record for [Membership Number] = 'Test Account'

I'm assuming because of LEFT JOIN on [Membership Level] = [Adv_MemNo] , that as there will be no match for the Test Account, those records will not be selected , is this correct?

How does LEFT vs RIGHT work? and does it make no difference?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

cool have a star r937.

I do tend to confuse myself sometimes, but i got there in the end, I now understand table joins much better, so thanks to all who helped.

P.S.

# is called.....

'Number' or 'Nought & Crosses'

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

oh and for our american friends....

Tic Tac Toe!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[ESquared]

I should learn to use preview. Silly forum markup language differences