Outer Join issue

S

Sue Compelling

Hi - using ACCESS2007

I have a query (sourced from 2 others) that is calculated to tell me the
current # of leads assigned to an employee (for various campaigns). It's a
simple calculation of # leads per employee MINUS the number of CLOSED leads
per employee (per campaign).

Unfortunately IF the employee doesn't close any leads that month (for any
particular campaign) no result is returned (for that campaign). I want the
join to return ALL records from the "EmpCampLeadVol" query though when I try
to chnage the jpin proporties I get the following error msg "The SQL stmt cld
not b exe bcos it contained an ambiguous outer join. To force .....)

My code is


SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
[empcampleadvol.countofcustnum]-[empcampleadvolclosed.countofcustnum] AS
[Current]
FROM EmpCampLeadVol INNER JOIN EmpCampLeadVolClosed ON
(EmpCampLeadVol.Employee = EmpCampLeadVolClosed.Employee) AND
(EmpCampLeadVol.Campaign = EmpCampLeadVolClosed.Campaign);

TIA
 
M

Michel Walsh

Make a new query: bring the table with all employee (listed once) and the
actual query. Join them through their employee field. Edit the join (right
click on it) to get the option keeping all records from the table of all
employee (listed once). In the grid, bring the employee name from your
table, and bring the other required fields from the existing query.

That's all.

In Jet, parenthesis are not necessary use to indicate order of evaluation,
so, 'cascading' the query can solve the problem, as the error message
suggested.



Vanderghast, Access MVP
 
S

Sue Compelling

Thanks so much Michel - half way there ...

My query now shows all employees, assoc to all their campaigns BUT where an
employee did NOT close any leads for that campaign for the month the field is
'empty' ...

ie - Kelly Morrelll had 117 leads in July for the 'Back to School' campaign.
She didn't close any of these so her current lead volume should be 117. My
query displays as 'empty'

How do I get it to to recognise that if the field returns empty then it
should display the value from the "EmpCampLeadVol.CountOfCustNum"?


My code is: (ps - I'm a newbie and don't actually write the code per se - I
just view it in SQL once I've created it through query design)

SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum, EmpCampLeadVolCurrentSource.Current
FROM EmpCampLeadVol LEFT JOIN EmpCampLeadVolCurrentSource ON
(EmpCampLeadVol.Campaign = EmpCampLeadVolCurrentSource.Campaign) AND
(EmpCampLeadVol.Employee = EmpCampLeadVolCurrentSource.Employee)
GROUP BY EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum, EmpCampLeadVolCurrentSource.Current;


--
Sue Compelling


Michel Walsh said:
Make a new query: bring the table with all employee (listed once) and the
actual query. Join them through their employee field. Edit the join (right
click on it) to get the option keeping all records from the table of all
employee (listed once). In the grid, bring the employee name from your
table, and bring the other required fields from the existing query.

That's all.

In Jet, parenthesis are not necessary use to indicate order of evaluation,
so, 'cascading' the query can solve the problem, as the error message
suggested.



Vanderghast, Access MVP


Sue Compelling said:
Hi - using ACCESS2007

I have a query (sourced from 2 others) that is calculated to tell me the
current # of leads assigned to an employee (for various campaigns). It's
a
simple calculation of # leads per employee MINUS the number of CLOSED
leads
per employee (per campaign).

Unfortunately IF the employee doesn't close any leads that month (for any
particular campaign) no result is returned (for that campaign). I want
the
join to return ALL records from the "EmpCampLeadVol" query though when I
try
to chnage the jpin proporties I get the following error msg "The SQL stmt
cld
not b exe bcos it contained an ambiguous outer join. To force .....)

My code is


SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
[empcampleadvol.countofcustnum]-[empcampleadvolclosed.countofcustnum] AS
[Current]
FROM EmpCampLeadVol INNER JOIN EmpCampLeadVolClosed ON
(EmpCampLeadVol.Employee = EmpCampLeadVolClosed.Employee) AND
(EmpCampLeadVol.Campaign = EmpCampLeadVolClosed.Campaign);

TIA
Click to expand...
Click to expand...
 
M

Michel Walsh

SELECT EmpCampLeadVol.Employee,
EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum,
NZ( EmpCampLeadVolCurrentSource.Current,
EmpCampLeadVol.CountOfCustNum)

FROM EmpCampLeadVol LEFT JOIN EmpCampLeadVolCurrentSource
ON
(EmpCampLeadVol.Campaign = EmpCampLeadVolCurrentSource.Campaign)
AND
(EmpCampLeadVol.Employee = EmpCampLeadVolCurrentSource.Employee)

GROUP BY EmpCampLeadVol.Employee,
EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum,
EmpCampLeadVolCurrentSource.Current;



Vanderghast, Access MVP


Sue Compelling said:
Thanks so much Michel - half way there ...

My query now shows all employees, assoc to all their campaigns BUT where
an
employee did NOT close any leads for that campaign for the month the field
is
'empty' ...

ie - Kelly Morrelll had 117 leads in July for the 'Back to School'
campaign.
She didn't close any of these so her current lead volume should be 117.
My
query displays as 'empty'

How do I get it to to recognise that if the field returns empty then it
should display the value from the "EmpCampLeadVol.CountOfCustNum"?


My code is: (ps - I'm a newbie and don't actually write the code per se -
I
just view it in SQL once I've created it through query design)

SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum, EmpCampLeadVolCurrentSource.Current
FROM EmpCampLeadVol LEFT JOIN EmpCampLeadVolCurrentSource ON
(EmpCampLeadVol.Campaign = EmpCampLeadVolCurrentSource.Campaign) AND
(EmpCampLeadVol.Employee = EmpCampLeadVolCurrentSource.Employee)
GROUP BY EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum, EmpCampLeadVolCurrentSource.Current;


--
Sue Compelling


Michel Walsh said:
Make a new query: bring the table with all employee (listed once) and the
actual query. Join them through their employee field. Edit the join
(right
click on it) to get the option keeping all records from the table of all
employee (listed once). In the grid, bring the employee name from your
table, and bring the other required fields from the existing query.

That's all.

In Jet, parenthesis are not necessary use to indicate order of
evaluation,
so, 'cascading' the query can solve the problem, as the error message
suggested.



Vanderghast, Access MVP


message
Hi - using ACCESS2007

I have a query (sourced from 2 others) that is calculated to tell me
the
current # of leads assigned to an employee (for various campaigns).
It's
a
simple calculation of # leads per employee MINUS the number of CLOSED
leads
per employee (per campaign).

Unfortunately IF the employee doesn't close any leads that month (for
any
particular campaign) no result is returned (for that campaign). I want
the
join to return ALL records from the "EmpCampLeadVol" query though when
I
try
to chnage the jpin proporties I get the following error msg "The SQL
stmt
cld
not b exe bcos it contained an ambiguous outer join. To force .....)

My code is


SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
[empcampleadvol.countofcustnum]-[empcampleadvolclosed.countofcustnum]
AS
[Current]
FROM EmpCampLeadVol INNER JOIN EmpCampLeadVolClosed ON
(EmpCampLeadVol.Employee = EmpCampLeadVolClosed.Employee) AND
(EmpCampLeadVol.Campaign = EmpCampLeadVolClosed.Campaign);

TIA
Click to expand...
Click to expand...
Click to expand...
 
S

Sue Compelling

Michel

Thank yo so much for the the learning and the answer (it works!). This
forum (and you) ROCK!
--
Sue Compelling


Michel Walsh said:
SELECT EmpCampLeadVol.Employee,
EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum,
NZ( EmpCampLeadVolCurrentSource.Current,
EmpCampLeadVol.CountOfCustNum)

FROM EmpCampLeadVol LEFT JOIN EmpCampLeadVolCurrentSource
ON
(EmpCampLeadVol.Campaign = EmpCampLeadVolCurrentSource.Campaign)
AND
(EmpCampLeadVol.Employee = EmpCampLeadVolCurrentSource.Employee)

GROUP BY EmpCampLeadVol.Employee,
EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum,
EmpCampLeadVolCurrentSource.Current;



Vanderghast, Access MVP


Sue Compelling said:
Thanks so much Michel - half way there ...

My query now shows all employees, assoc to all their campaigns BUT where
an
employee did NOT close any leads for that campaign for the month the field
is
'empty' ...

ie - Kelly Morrelll had 117 leads in July for the 'Back to School'
campaign.
She didn't close any of these so her current lead volume should be 117.
My
query displays as 'empty'

How do I get it to to recognise that if the field returns empty then it
should display the value from the "EmpCampLeadVol.CountOfCustNum"?


My code is: (ps - I'm a newbie and don't actually write the code per se -
I
just view it in SQL once I've created it through query design)

SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum, EmpCampLeadVolCurrentSource.Current
FROM EmpCampLeadVol LEFT JOIN EmpCampLeadVolCurrentSource ON
(EmpCampLeadVol.Campaign = EmpCampLeadVolCurrentSource.Campaign) AND
(EmpCampLeadVol.Employee = EmpCampLeadVolCurrentSource.Employee)
GROUP BY EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
EmpCampLeadVol.CountOfCustNum, EmpCampLeadVolCurrentSource.Current;


--
Sue Compelling


Michel Walsh said:
Make a new query: bring the table with all employee (listed once) and the
actual query. Join them through their employee field. Edit the join
(right
click on it) to get the option keeping all records from the table of all
employee (listed once). In the grid, bring the employee name from your
table, and bring the other required fields from the existing query.

That's all.

In Jet, parenthesis are not necessary use to indicate order of
evaluation,
so, 'cascading' the query can solve the problem, as the error message
suggested.



Vanderghast, Access MVP


message
Hi - using ACCESS2007

I have a query (sourced from 2 others) that is calculated to tell me
the
current # of leads assigned to an employee (for various campaigns).
It's
a
simple calculation of # leads per employee MINUS the number of CLOSED
leads
per employee (per campaign).

Unfortunately IF the employee doesn't close any leads that month (for
any
particular campaign) no result is returned (for that campaign). I want
the
join to return ALL records from the "EmpCampLeadVol" query though when
I
try
to chnage the jpin proporties I get the following error msg "The SQL
stmt
cld
not b exe bcos it contained an ambiguous outer join. To force .....)

My code is


SELECT EmpCampLeadVol.Employee, EmpCampLeadVol.Campaign,
[empcampleadvol.countofcustnum]-[empcampleadvolclosed.countofcustnum]
AS
[Current]
FROM EmpCampLeadVol INNER JOIN EmpCampLeadVolClosed ON
(EmpCampLeadVol.Employee = EmpCampLeadVolClosed.Employee) AND
(EmpCampLeadVol.Campaign = EmpCampLeadVolClosed.Campaign);

TIA
Click to expand...
Click to expand...
Click to expand...
 
Top