innjer join issue

[roi8877]

My understanding of inner joins and what i've read is that it displays a subset of records only present on both sides of the table according to the fields they are joining on.

So in my experiment, I have table A which has some duplicate records. I select disticnt on field1 and make a new table (table B) out of it. Great.

Now I inner join table A to table B inner joining them on field1 in the hopes the result would just produce all rows common to each, which ideally it was suppose to show only all rows that are on table B. But this is not the case, it will always show the total number of rows present on table A.

Is this the right behaviour?

Also could it be that table B has no primary key? Table B was created by selecting just field 1 and than converting that to a make table...which results in a table with field1 but no primary key...

Ultimately my goal is to remove duplicate records on table A.

 

[Galaxiom]

Yes the join returns records that match in both tables. This does not mean it won't find and return the same record twice on one side if it matches one on the other side.
Any derived table is still going to match both records since they are duplicates.

There is lots of information available all over the Net about removing duplicates from tables. Basically they use totals queries to select one of the duplicates and delete the others.