Query Problem

[tdwyer1]

Hi,

I have a little problem getting some information from 2 tables.

Table A has a list of cars with the reg numbers being the main field (not the key).
Table B has another list of Cars with reg number being the main field (not the key).

I'm trying to search through both tables and only display information that is not in both tables.

Thanks for your help.
Terry

 

[mp9]

This returns Reg Numbers from Table A that don't exist in Table B:

SELECT [Table A].[Reg Number]
FROM [Table A] LEFT JOIN [Table B] ON [Table A].[Reg Number] = [Table B].[Reg Number]
WHERE [Table B].[Reg Number] Is Null;

This returns Reg Numbers from Table B that don't exist in Table A:

SELECT [Table B].[Reg Number]
FROM [Table B] LEFT JOIN [Table A] ON [Table B].[Reg Number] = [Table A].[Reg Number]
WHERE [Table A].[Reg Number] Is Null;

So the following gives you Reg Numbers that exist in one or other table but not both:

SELECT [Table A].[Reg Number]
FROM [Table A] LEFT JOIN [Table B] ON [Table A].[Reg Number] = [Table B].[Reg Number]
WHERE [Table B].[Reg Number] Is Null
UNION
SELECT [Table B].[Reg Number]
FROM [Table B] LEFT JOIN [Table A] ON [Table B].[Reg Number] = [Table A].[Reg Number]
WHERE [Table A].[Reg Number] Is Null;

Hope this helps. Obviously you'll need to change table and field names in the above to match yours.

http://www22.brinkster.com/accessory/

 

[tdwyer1]

That worked a treat thanks mp9.