Conditional JOIN

[peterkin9]

I am still trying to convert a Financial Services package into Access. It was originally written in RPG to run on an IBM System/36 but converted to Visual Basic as the company who run it want rid of the System/36. VB proved difficult to distribute so hence the conversion to VBA. The latest problem is that of "The Second Client".
Each policy record has a mandatory client number, but the second client number may be zero. I have added a second copy of the Clients file to the query that populates the Policy Enquiry form (using the default Clients_1 alias) and that works OK, but in doing so I have limited the records to those which have values in both Client fields. Apart from having two copies of the form, can anyone suggest how to modify the SQL string so it only gets the second record if it needs to? I would prefer not to have duplicate forms, as a policy can have up to four Broker references so that would mean eight copies!!

Many thanks in advance

Peter Kinsman

 

[SBendBuckeye]

Hello Peter,

It would help if you could post the SQL you are using. It sounds like you need a left join instead of an inner join on your second client number. If you are doing this in the Query design window, right click on the line connecting the tables and select Join Properties. Instead of option 1 you probably one option 2. Again, it would be easier to help with the actual SQL code you are using.

Here is an example that might help you. Lets say that you have 2 tables, tblCity and tblState and that tblCity contains a StateID which links into the tblState. Here is the standard SQL generated by Access:

Select tblCity.CityID, tblCity.CityName, tblCity.StateID, tblState.StateName
From tblCity INNER JOIN tblState
ON tblCity.StateID = tblState.StateID

The problem is that this will exclude any cities with no or invalid StateIDs in tblCity. If you change INNER JOIN to LEFT JOIN it will bring back all of the cities. The tlbState part of things will be null since there is no corrresponding information for tblState.

Good Luck! Hope this helps! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!