Need help comparing tables 1

[Stuartp]

I have two database tables designed identically - they each contain fields: saleref, tradname, sale, profit and are called 1 and 2.

I want to return the values of tradname from table 2 where those values are not already in table 1.

I have written this query:

(Select tradname from 2)
except
(Select tradname from 1);

and keep being told there is a syntax error in it. But I copied this directly from a databases book!!

Could someone please tell me how to do this.

Thanks,

Stuart

 

[RickSpr]

Each database management system has its own idiosyncracies when it comes to the SQL language it supports. In this case, you're getting an error because Microsoft Jet SQL does not have an "except" keyword.

To do this in Access, you use something like:
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.KeyField = Table2.KeyField WHERE Table2.KeyField IS NULL;

A "join" is a combining of data from two tables based on a matching value in one or more columns from each table. In this case, a "left join" indicates that you want data from the first table even if there is no matching data found in the second table. (In an "inner join" you only get result rows where the matched data is found in both tables.)

Since you only want rows where there is no matching data in the second table, the WHERE clause eliminates rows from the result set unless the data from the second table is Null. Rick Sprague

 

[Stuartp]

Rick,

Thanks for your reply but I don't think I have followed this too well. I tried to substitute your query in my database and modified it to reflect the names of my tables etc, but i just get told "Syntax error (missing operator) in query expression 1.tradname = 2.tradname"

What I may not have explained properly last time is that I want to select those records from table 1 where they do not exist at all in table 2. In other words I don't mean just that the value is null or anything, but that it is not in existence. So for example if customer XXX is listed in table 1 but not listed at all in table 2 I want to select him.

Hope that explains a bit better my situation.

Thanks, Stuart

 

[ghubbell]

Stuart:
Try this please:
In the query by expression view show your two tables. Join the two tables related fields by draging one to the other if a join line does not already exist. Click on the join line and chose All records from "table1" and only those records from "table2" whose join fields are equal.
Drag * from "table1" to the first column on the QBE grid.
Now drag your related field from "table2" to the QBE grid.
Underneath it in the criteria section type Is Null .
Run your query. Gord
ghubbell@total.net

 

[RickSpr]

Stuart,

Apparently you have actually named your tables "1" and "2". These are legal table names, but will cause you syntax errors in many places you may need to use them. To avoid the syntax errors, place the table names in between "[" and "]" brackets, i.e. refer to them as [1] and [2].

Thus, your criteria expression should be [1].tradname = [2].tradname. I think that will work.

In the future, it would be better to make table names (and all other names you assign) start with a letter. Rick Sprague

 

[RickSpr]

Oh, I forgot to respond to your last paragraph. You still want a left join. Access will provide Null values for the fields from the right table when no matching record exists. Rick Sprague