Inner join returning duplicate records access sql

BNO-CSCcode contains duplicates. You are joining the first record of Things to both records of Mapp, then the second record of Things joins to both records of Mapp. Giving you a total of 4 records.

If you want to join these together, you need some unique way of identifying the rows between the tables.

A Distinct should bring it back down to 2 records, but likely you need to join on a few more fields to get it to 2 records:

SELECT DISTINCT
  Things.Prefix,
  Things.Baseletter,
  Things.suff,
  Things.CSCcode,
  Things.WT,
  Mapping.BNO-CSCcode,
  Mapping.EID 
FROM 
  Things 
  INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode 

You are getting duplicates because both records in the Things table have a BNO-CSCcode of 5560-04020, as do both records in the Mapp table. The only distinction between the two that I can see is the Description field. So, if you use the following query:

SELECT Things.Prefix ,Things.Baseletter,Things.suff,Things.CSCcode,Things.WT,Mapping.BNO-CSCcode,Mapping.EID
FROM Things INNER JOIN Mapping ON Things.BNO-CSCcode = Mapping.BNO-CSCcode AND 
Things.Description = Mapping.Description

...you should eliminate the duplication.

However, using a field that is obviously a text field in a join is not best practice, as if they are separately user-generated, they are unlikely to match, and it takes longer (relatively speaking) to match fields with a higher byte-count than those with low byte-counts.

Tags:

Sql

Ms Access