SQL Join Query in Access

[kriehn16]

Hello,

I have two tables that I am trying to run a Left Join on after I have filtered them with a WHERE condition. I want to filter the Customer table to only the records that are in the customer_group "Investor Owned Util". Then I want to filter the Orders table to only the records with a sales_credit_date after January 1, 2016. Finally, I want to take these two resulting tables and show all the results from the Customer table and any orders that were placed by each of the customers. The problem is that when I try to run the query I get a "Syntax error in Union Query". I have tried a few different versions of what is shown below but nothing is working. The only query I got to run would join the tables first and then filter on the WHERE clause which would result in leaving out any customers that did not place an order this year. I need the WHERE clause to filter first and then JOIN the resulting tables. Any help is greatly appreciated.

(SELECT ship_to_party, sold_to_party, customer_group FROM Customer WHERE customer_group = "Investor Owned Util")
LEFT JOIN
(SELECT sales_order_number, order_ship_to_party, sales_credit_date FROM Orders WHERE sales_credit_date > #1/1/2016#)
ON cust.ship_to_party = ord.order_ship_to_party

Thanks,
joker16

 

[Andrzejek]

>I want to filter the Orders table to only the records with a sales_credit_date after January 1, 2016
and
>filter on the WHERE clause which would result in leaving out any customers that did not place an order this year

That seams consistent, but that's not what you want. (?)

So do you or don't you want the customers who placed orders this year?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[kriehn16]

I want all the customers. The customers that have placed an order this year and the customers who have not placed an order this year. For the customers that have placed an order this year, I want to show those orders.

joker16

 

[kriehn16]

Hello,

I have now also tried following, but it is still not working. The first one runs but there are no records in the query result. The second one does not run and I get a "JOIN expression not supported." message. However, when I remove the "AND c.sales_group = "Investor Owned Util"" line it runs. Again, I want all the records from the Customer table where sales_group = "Investor Owned Util" and then from that query I want to JOIN it with the Orders table and show all orders for each customer. I need the WHERE clause to run first on the Customer table and then the JOIN operation should run after it but I cant figure out how to do this.

SELECT c.ship_to_party, c.ship_to_name, c.sales_group
FROM (SELECT * FROM Customer WHERE Customer.sales_group = "Investor Owned Util") AS c
LEFT JOIN Orders AS o
ON c.ship_to_party = o.order_ship_to_party
ORDER BY c.ship_to_name;

SELECT c.ship_to_party, c.ship_to_name, c.sales_group
FROM Customer AS c
LEFT JOIN Orders AS o
ON c.ship_to_party = o.order_ship_to_party
AND c.sales_group = "Investor Owned Util"
ORDER BY c.ship_to_name;

Thanks,
Eric