CODEWITHSUNDEEP
sqlms-accessms-access-2007
Jamie S
Jamie S

Reputation: 75

How can I get ALL records with criteria?

I'm having a tough time with this Access SQL... I would like ALL active CustomerIDs to appear, whether the OrderDate matches or not, and I can't figure out what I'm doing wrong.

SELECT 
    tblCustomers_PullLists.CustomerID, 
    tblCustomers_PullLists.Title, 
    tblCustomers_PullLists.Active, 
    tblCustomers.FullName, 
    tblOrders.OrderDate, 
    tblCustomers.Notes
FROM 
    (
        (
            tblCustomers_PullLists 
            INNER JOIN 
            tblOrderDetails 
                ON tblCustomers_PullLists.Title = tblOrderDetails.Title
        ) 
        INNER JOIN 
        tblOrders 
            ON tblOrderDetails.OrderNumber = tblOrders.ID
    ) 
    LEFT JOIN 
    tblCustomers 
        ON tblCustomers_PullLists.CustomerID = tblCustomers.ID
WHERE (((tblCustomers_PullLists.Active)=Yes) 
    AND ((tblOrders.OrderDate)=[Please enter order date]))
ORDER BY tblCustomers_PullLists.CustomerID;

Any help would be greatly appreciated!!

Upvotes: 0

Views: 69

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123484

The first thing to note is that your query has 

(stuff) LEFT JOIN tblCustomers

If you want all customers to appear then you probably want a RIGHT JOIN, not a LEFT JOIN.

The other issue is the OrderDate. Customers without orders for that date will have NULL OrderDate values, so you may have to change your WHERE condition from...

(tblOrders.OrderDate)=[Please enter order date]

...to...

(((tblOrders.OrderDate)=[Please enter order date]) OR (tblOrders.OrderDate IS NULL))

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166376

Well, in that case the filter 

AND ((tblOrders.OrderDate)=[Please enter order date])

does not make any sense.

Remove that part of the where cluase, and you should get all active Customers.

Upvotes: 0

Related Questions