Combobox Selections & Display problem

[Andy Roberts]

Heres what im trying to do..

I have a form which contains a combo box which displays a list of companies.
When I select the company, there is a second cbo which just shows me the
people who work for that company - simple so far...

The sond cbo box is running a query from a contacts table (linked to the
company table by id's) which also includes the phone number and job title
for the people who work for each company. When I select the person, the
name appears in the combo and the phone number and job title appear in 2
additional txt boxes on the form. This all works fine except the second cbo
only displays the people who have job titles assigned to them and this is
because the job title is being called from a JobTitle table and linked to
the contacts table via id's.

If I remove the Job Title from the query (i.e. the linked field) then
everything works fine, but the linked field only works if there is data in
that field e.g.

A Smith 012345 Sales rep
B Smith 012345
C Smith 012345 Director

.... would only show me A Smith & C Smith as they have data in the linked Job
Title field. I want to show all 3 records.

How do I get around this problem?

Regards

Andy

 

[Biz Enhancer]

Hi Andy,

Your problem is that you are using an "INNER JOIN" with the 'JobTitle' table
instead of a "LEFT JOIN". Head to the rowsouce proerty of the 2nd combo and
change "INNER JOIN JobTitle ON ...." to "LEFT JOIN JobTitle ON ...."

regards,

Nick

 

[Andy Roberts]

Nick

Thanks for the quick response. I'm not sure what the difference is but I've
followed your advice anyway and it still doesn't work. In my example I
chose simple tables etc for ease. I've now pasted my SQL statement below:-

SELECT tblClientRep.ClientRepID, tblClientRep.ClientID,
[ClientRepFirstName]+" "+[ClientRepLastname] AS Name,
tblJobTitle.IndJobTitle, tblClientRep.ClientRepMobile,
tblClientRep.ClientRepEmail FROM tblJobTitle LEFT JOIN tblClientRep ON
tblJobTitle.IndJobTitleID=tblClientRep.ClientRepJobTitleID WHERE
(((tblClientRep.ClientID)=Forms!frmTenders!cboClientID)) ORDER BY
[ClientRepFirstName]+" "+[ClientRepLastname];

Andy

 

[Andy Roberts]

Genius

Thanks Nick

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

Hi Andy,

Change the LEFT JOIN to RIGHT JOIN and it should solve it.

A very rudimentary explanation:

It all has to do with the way the tables look at each other. An INNER JOIN
has to find a matching value in both tables which is why the query only
worked for those that had job titles.
A LEFT JOIN takes everything from the master table and only that which
matches in the junior table.
A RIGHT JOIN works the other way around. The master table is defined as
the
first one specified in the join statement.

Regards,

Nick.

Nick

Thanks for the quick response. I'm not sure what the difference is but
I've
followed your advice anyway and it still doesn't work. In my example I
chose simple tables etc for ease. I've now pasted my SQL statement
below:-

SELECT tblClientRep.ClientRepID, tblClientRep.ClientID,
[ClientRepFirstName]+" "+[ClientRepLastname] AS Name,
tblJobTitle.IndJobTitle, tblClientRep.ClientRepMobile,
tblClientRep.ClientRepEmail FROM tblJobTitle LEFT JOIN tblClientRep ON
tblJobTitle.IndJobTitleID=tblClientRep.ClientRepJobTitleID WHERE
(((tblClientRep.ClientID)=Forms!frmTenders!cboClientID)) ORDER BY
[ClientRepFirstName]+" "+[ClientRepLastname];

Andy

Hi Andy,

Your problem is that you are using an "INNER JOIN" with the 'JobTitle'
table
instead of a "LEFT JOIN". Head to the rowsouce proerty of the 2nd combo
and
change "INNER JOIN JobTitle ON ...." to "LEFT JOIN JobTitle ON ...."

regards,

Nick

:

Heres what im trying to do..

I have a form which contains a combo box which displays a list of
companies.
When I select the company, there is a second cbo which just shows me
the
people who work for that company - simple so far...

The sond cbo box is running a query from a contacts table (linked to
the
company table by id's) which also includes the phone number and job
title
for the people who work for each company. When I select the person,
the
name appears in the combo and the phone number and job title appear in
2
additional txt boxes on the form. This all works fine except the
second
cbo
only displays the people who have job titles assigned to them and this
is
because the job title is being called from a JobTitle table and linked
to
the contacts table via id's.

If I remove the Job Title from the query (i.e. the linked field) then
everything works fine, but the linked field only works if there is
data
in
that field e.g.

A Smith 012345 Sales rep
B Smith 012345
C Smith 012345 Director

.... would only show me A Smith & C Smith as they have data in the
linked
Job
Title field. I want to show all 3 records.

How do I get around this problem?

Regards

Andy