Microsoft Access 97

Table A has all the information
Table B has some of the rows from table A
they both have the same columns

how do I get table A's rows minus table B's rows into table C?

mars bar for the most efficient solution (no cartesian selects please )

I've tried...
select x.1, x.2, x.3
from (select a.1, a.2, a.3 from tablea a
union
select b.1, b.2, b.3 from tableb b) x
group by x.1, x.2, x.3
having count(*)=1

...but it's not having it.

 

Okay...

Table A has

Row with Keys : 1,2,3,4,5,6,7,8,9,10

Table B has

Rows with Keys : 1,2,3,4,5

So Table C will have 6,7,8,9,10?

This works for me:

SELECT DISTINCTROW tblOne.IndexID, tblOne.KeyID, tblOne.FieldB, tblOne.FieldC INTO tblThree
FROM tblOne LEFT JOIN tblTwo ON tblOne.KeyID = tblTwo.KeyID
WHERE (((tblTwo.KeyID) Is Null));

I think

 

select * from tablea
where key_a not in(select key_b from tableb)

 

That's a slightly quicker way of doing it

 

The magic "not in" solution is the one you want

 

Were it only that simple chaps

table A column data | table B column data
a,b,c,d,e | a,b,c,d,e
a,f,c,d,e | a,f,c,d,e
a,b,c,d,f | a,b,c,d,e
a,b,c,d,e | a,b,c,d,g
x,b,c,d,t |
j,b,c,d,e | a,b,c,d,e
a,b,c,d,e | a,y,c,d,e
x,b,c,d,e | a,b,c,d,e
x,b,c,d,h |

none of them are keyed fields, and you can see row 1 and 2 are the only matching rows columns 1 to 5

I want rows 1 and 2 in table C and the remaining unmatched rows in tables D and E (from A and B, respectively).