INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Join Query in Access

SQL Join Query in Access

(OP)
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

RE: SQL Join Query in Access

>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.

RE: SQL Join Query in Access

(OP)
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

RE: SQL Join Query in Access

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close