How can I change this VBA code into a SQL Query using JOIN?

Question

It's a simple question, I'm getting started with SQL and I'm having a lot of problems with it.

I have TBL_SALES and TBL_CUSTOMERS. Both can be JOINed by the field CUSTOMER_ID. A customer may have a CUSTOMER_ID and have no entry on TBL_CUSTOMERS

I would like to FITER all entries on TBL_SALES that don't have an entry on TBL_CUSTOMERS.

Which I think it's the opposite of

SELECT UNIQUE TBL_SALES.CUSTOMER_ID, TBL_CUSTOMERS.CUSTOMER_ID
FROM TBL_SALES, TBL_CUSTOMERS
WHERE TBL_SALES.CUSTOMER_ID = TBL_CUSTOMERS.CUSTOMER_ID

Well... I can do that with a VBA code using SEEK and NOMATCH. But I know it's not productive. And if I try to use this code, it doesn't work:

SELECT UNIQUE TBL_SALES.CUSTOMER_ID, TBL_CUSTOMERS.CUSTOMER_ID
FROM TBL_SALES, TBL_CUSTOMERS
WHERE TBL_SALES.CUSTOMER_ID <> TBL_CUSTOMERS.CUSTOMER_ID

I realized that there must be a 'SEEK' command in SQL but I'm pretty sure that is a smarter way to run this query.

Maarten van Leunen · Accepted Answer · 2011-12-05 14:18:32Z

5

Try

SELECT *
FROM TBL_SALES
WHERE NOT EXISTS
(SELECT * FROM TBL_CUSTOMERS
WHERE TBL_CUSTOMERS.CUSTOMER_ID = TBL_SALES.CUSTOMER_ID)

It's also possible to use a LEFT JOIN, which selects all records on "the left" regardless wether or not there are existing records "on the right".

SELECT *
FROM TBL_SALES LEFT JOIN TBL_CUSTOMERS
ON TBL_SALES.CUSTOMER_ID = TBL_CUSTOMERS.CUSTOMER_ID
WHERE TBL_CUSTOMERS.CUSTOMER_ID IS NULL

answered Dec 5, 2011 at 14:18

Maarten van Leunen

4602 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Fionnuala Over a year ago

You are generally better off with joins in Access, so I would go for option 2.

Johnny Bigoode Over a year ago

BTW: Can I use SELECT UNIQUE so I can have no repeated CUSTOMER_ID fields? Or is it not necessary?

Fionnuala Over a year ago

@Johnny You can use select distinct

Johnny Bigoode Over a year ago

Double crap! I tried to update the query - but that's impossible right? In this particular case I really don't need to update it, I can simply add a new entry on the correct table - but as a reference for the future: I can never use access commands of EDIT, UPDATE and ADDNEW when I run a SQL query right? I guess it's a little obvious that I really cant, but, just to be sure ^_^

Fionnuala Over a year ago

@Johnny A query must be updateable. There are a number of reasons why it may not be: support.microsoft.com/kb/328828, one of the most common is a problem with indexes or the keyword distinct.

Simon · Accepted Answer · 2011-12-05 14:23:31Z

2

@Maarten van Leunen has just answered with pretty much exactly what I was going to put so I won't repeat it again. I did have one point that I wanted to make though: Can you really have orders with no related customer? I'm not sure if this a valid use case for you (seems unlikely) but if not you need to investigate the integrity of your database and start enforcing some foreign key relationships. i.e. You cannot save an order record without a link to a valid customer record.

answered Dec 5, 2011 at 14:23

Simon

6,17214 gold badges65 silver badges99 bronze badges

3 Comments

Fionnuala Over a year ago

+1 However, there may be a valid reason, for example, across the counter sales to an unidentified customer.

Johnny Bigoode Over a year ago

Here's the deal, I just made up the table's names to help identify my problem. I'm actually running a VBA script to capture ~1000 daily SWIFT messages and I need to cross the broker's national ID with another table (it's kinda like a social security code for legal people) But I don't have a complete list of all brokers in my database. So I need to filer like I mentioned above so I can update my broker list before generating the reports.

Simon Over a year ago

@Remou, indeed that would be a good example. I think I'd still have a generic customer record that I'd use for this kind of thing though.

Collectives™ on Stack Overflow

How can I change this VBA code into a SQL Query using JOIN?

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related