Data in one table but not the other

[garvey]

Hi,



I have two tables (A &B) which have the same design (columns 1-10).
Some of the data in Table B is in Table A, but I need the data in Table A, that is not in B.

I have tried using this code, but am getting no results

SELECT [Table A].*
FROM [Table A], [Table B]
WHERE 
([Table A].[1]+[Table A].[2]+[Table A].[3]+[Table A].[4]+[Table A].[5])
<>
([Table B].[1]+[Table B].[2]+[Table B].[3]+[Table B].[4]+[Table B].[5]);

Can anyone help me??
Should the tables be joined by columns 1-5?

Thanks

[ranman256]

Do an outer join. In the picture the join arrow points to the table that could be missing records. (the table with the base of arrow shows all records)

SELECT tNames.ClientID, tCampByScout.CampID
FROM tNames LEFT JOIN tCampByScout ON tNames.ClientID = tCampByScout.ClientID;

[JoeM]

ranman256,

You forgot your where condition. That will give you all the data from tNames, and any matching data in tCampbyScout, but not only the data that is in tNames that does not have matching values in tCampbyScout. You need to add a WHERE clause to zero in on just that data, i.e.

SELECT tNames.ClientID, tCampByScout.CampID
FROM tNames LEFT JOIN tCampByScout ON tNames.ClientID = tCampByScout.ClientID
WHERE tCampByScout.CampID is Null;

By the way, Access has an Unmatched Query Wizard that can do this for you. The only thing is, it will only match on one field. However, you can edit it after it is created and add in any other Joins that you need.

[JoeM]

Garvey,

Does this work?

SELECT [Table A].*
FROM [Table A]
LEFT JOIN [Table B]
ON  [Table A].[1]=[Table B].[1] 
AND [Table A].[2]=[Table B].[2] 
AND [Table A].[3]=[Table B].[3] 
AND [Table A].[4]=[Table B].[4] 
AND [Table A].[5]=[Table B].[5] 
WHERE [Table B].[1] is Null;