how to find no matching records?

[Danny_H]

Hi Guys

I have a query that looks at 2 tables and returns the values that are in both and this is fine

However I need to replicate this but with the results returned being the values that are not in both tables

Whats the easiest way of doing this?

SQL for working Query is below:
SELECT Query1.[Part Number], Query1.Status, Query1.Composition
FROM Query1 INNER JOIN RRD_Parts_List ON Query1.[Part Number] = RRD_Parts_List.Partnumber;

Thanks in advance

 

[plog]

However I need to replicate this but with the results returned being the values that are not in both tables

That depends. It's super easy if one of those has all possible records (Data1), and the other might have matches (Data2). For that you would use a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp) from Data1 to Data2...but...

If there could be some records only in Data1 and some only in Data2 and you need to find them both, then you need a FULL OUTER JOIN (http://www.w3schools.com/sql/sql_join_full.asp)...which isn't supported by Access, but can be achieved with some work.

So what situation do you have?

 

[Danny_H]

Hi plog

Table 1 has all records
Table 2 has some records

I want to find the records from table 2 that do not appear in table 1

Hopefully that explains a bit better

 

[plog]

Then, in the SQL of your first query, you use LEFT JOIN instead of an INNER JOIN. And you also have a WHERE clause that finds the records where the Part Number field in Table2 is null.

 

[arnelgp]

to get records in Query1 that are not in RDD_Parts_List:

SELECT Query1.[Part Number], Query1.Status, Query1.Composition
FROM Query1 LEFT JOIN RRD_Parts_List ON Query1.[Part Number] = RRD_Parts_List.Partnumber WHERE RDD_Parts_List.[PartNumber] Is Null;


to get records in RDD_Parts_List that are not in Query1:

SELECT Query1.[Part Number], Query1.Status, Query1.Composition
FROM Query1 RIGHT JOIN RRD_Parts_List ON Query1.[Part Number] = RRD_Parts_List.Partnumber WHERE Query1.[Part Number] Is Null;

 

[plog]

You can never just let people get there themselves, can you?

 

[Danny_H]

Thanks both for the help

arnelgp

The code worked spot on and did exactly what I needed

 

[arnelgp]

im happy for you.