Inner Join Question

[Guest]

I have a report that links two tables together, "Contacts" and "Calls". I
have created a query (see below) that has an inner join so I can display all
of the wholesalers that don't meet certain criteria in a report. However,
only records with linked data in both "Contacts" and "Calls" are displayed...
instead of all records despite the presence of linked data in the "Calls"
table.

Any idea on how to modify the following query so everything will show up?

-Adam

SELECT Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID, Calls.CallDate

FROM Contacts INNER JOIN Calls ON Contacts.ContactID=Calls.ContactID
WHERE Contacts.[Employee]=forms!Employee!EmployeeName AND Contacts.[Packet
Sent to Wholesaler YN]=True AND (Contacts.[Agreement Received YN]=False OR
Contacts.[Check Received YN]=False)

GROUP BY Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID;

 

[Jeff Boyce]

Adam

You'll need to change the INNER JOIN to a LEFT JOIN (or a RIGHT JOIN).
Which one depends on which table to want to see "all" of, and which table
you want to see "any" of.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Left join worked!

Thanks Jeff!

-Adam

Adam

You'll need to change the INNER JOIN to a LEFT JOIN (or a RIGHT JOIN).
Which one depends on which table to want to see "all" of, and which table
you want to see "any" of.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a report that links two tables together, "Contacts" and "Calls". I
have created a query (see below) that has an inner join so I can display
all
of the wholesalers that don't meet certain criteria in a report. However,
only records with linked data in both "Contacts" and "Calls" are
displayed...
instead of all records despite the presence of linked data in the "Calls"
table.

Any idea on how to modify the following query so everything will show up?

-Adam

SELECT Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID, Calls.CallDate

FROM Contacts INNER JOIN Calls ON Contacts.ContactID=Calls.ContactID
WHERE Contacts.[Employee]=forms!Employee!EmployeeName AND Contacts.[Packet
Sent to Wholesaler YN]=True AND (Contacts.[Agreement Received YN]=False OR
Contacts.[Check Received YN]=False)

GROUP BY Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID;

 

[Guest]

Does this query work in SQL Server?
I'm working on a similar query, but SQL throws error near
Contacts.[Employee]=forms!Employee!EmployeeName ???

Should this be converted to a stored procedure instead?