NZ function - can't get to work

[Guest]

Hi,

I have 2 tables - a main table (tbl_Opportunity) and a look-up table
(tbl_AMContacts). I have a field in each (AllianceMgr) that links the two
tables together (AllianceMgr is the primary key on the AMContacts table).
I'm trying to do a query that combines all of this data together so I can
display it on a form. But, if the AllianceMgr is blank on the main table,
when I do the query it doesn't display any of those records. I want it to
display all records -- even if there is a null in the AllianceMgr field. I
tried using the nz function, but couldn't seem to get it to work. Here is
what I was trying:

SELECT tbl_Opportunity.Opportunity_ID, tbl_Opportunity.AccountName,
tbl_Opportunity.EngagementDesc, tbl_Opportunity.AllianceMgr,
Nz(tbl_AMContacts.Title,0), Nz(tbl_AMContacts.Phone1,0)
FROM tbl_AMContacts INNER JOIN tbl_Opportunity ON tbl_AMContacts.AMName =
tbl_Opportunity.AllianceMgr;

Thanks!
Heather

 

[Ken Snell \(MVP\)]

Use a outer join (here, a LEFT JOIN):

SELECT tbl_Opportunity.Opportunity_ID, tbl_Opportunity.AccountName,
tbl_Opportunity.EngagementDesc, tbl_Opportunity.AllianceMgr,
Nz(tbl_AMContacts.Title,0), Nz(tbl_AMContacts.Phone1,0)
FROM tbl_AMContacts LEFT JOIN tbl_Opportunity ON tbl_AMContacts.AMName =
tbl_Opportunity.AllianceMgr;

 

[Guest]

I tried that... It brought back more records, but the new ones that it
brought back were just blank across the board. Each line should at least
have an OpportunityID, and AccountName even if the AllianceMgr is null. Any
other ideas?

Thanks!!

 

[Ken Snell \(MVP\)]

Not knowing exactly what you're seeking, perhaps the JOIN is going in the
wrong direction for what you seek. Try one of these:

SELECT tbl_Opportunity.Opportunity_ID, tbl_Opportunity.AccountName,
tbl_Opportunity.EngagementDesc, tbl_Opportunity.AllianceMgr,
Nz(tbl_AMContacts.Title,0), Nz(tbl_AMContacts.Phone1,0)
FROM tbl_AMContacts RIGHT JOIN tbl_Opportunity ON tbl_AMContacts.AMName =
tbl_Opportunity.AllianceMgr;


SELECT tbl_Opportunity.Opportunity_ID, tbl_Opportunity.AccountName,
tbl_Opportunity.EngagementDesc, tbl_Opportunity.AllianceMgr,
Nz(tbl_AMContacts.Title,0), Nz(tbl_AMContacts.Phone1,0)
FROM tbl_Opportunity LEFT JOIN tbl_AMContacts ON tbl_Opportunity.AllianceMgr
=
tbl_AMContacts.AMName;