Show all customers even if there are no sales

[Guest]

I am having trouble with what I thought would be an easy query. I have two
tables, one contains my sales history the other contains customers. Sales
history has a multiply primary key based on "invoice #, invoice date & line
#", Customer table has a primary key on customer ID.

When I run a query to see sales on a particular product, I want to see all
the customers whether they had sales or not. I create an outer join to
include all records in customer and only those that match in saleshistory.
My link field is Customer #. I only get customers with sales. I have tried
various ways to get this information with no luck. Does anyone have any
solutions or help for me?

 

[Rick Brandt]

I am having trouble with what I thought would be an easy query. I have two
tables, one contains my sales history the other contains customers. Sales
history has a multiply primary key based on "invoice #, invoice date & line
#", Customer table has a primary key on customer ID.

When I run a query to see sales on a particular product, I want to see all
the customers whether they had sales or not. I create an outer join to
include all records in customer and only those that match in saleshistory.
My link field is Customer #. I only get customers with sales. I have tried
various ways to get this information with no luck. Does anyone have any
solutions or help for me?

If you put criteria on the sales table (= product) then you effectively make the
query revert to an inner join behavior. Unless your criteria is like...

WHERE FieldInSalesTable = SomeValue
OR FieldInSalesTable Is Null

 

[Allen Browne]

LuAnn, I am guessing that you have criteria on some of the fields from the
Sales table. This effectively turns the query back into an inner join,
because the null values do not match your criteria.

More info:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Guest]

My guess would be that you are trying to restrict the query on a column or
columns in the Sales History table, e.g. to a particular year or quarter. If
you do this in an outer join you'll only get rows from the referenced table
(Customers) where there is a matching row in the referencing (Sales History)
table where the criterion on the referencing table evaluates to True. In
short, you can only restrict an outer join query on columns in the referenced
table, e.g. you could restrict it to all customers in London or New York.

The way around this is to left outer join the referenced table to a subquery
on the referencing table, giving the subquery an alias to identify it (SH
below). Say you want to return the sale amounts for all customers for 2005,
with Null amounts for all customers with no sales in 2005 you'd use something
like this:

SELECT Customers.[customer name], SH.[amount]
FROM Customers LEFT JOIN
(SELECT *
FROM [Sales History]
WHERE YEAR([sale date] = 2005) AS SH
ON Customers.[customer #] = SH.[customer #];

Ken Sheridan
Stafford, England

 

[Guest]

Thank you all who answered. My problem is SOLVED! Thanks again!

 

[Guest]

Oops! I missed a closing parenthesis:

WHERE YEAR([sale date]) = 2005) AS SH