sam sam - 3 months ago 7
SQL Question

Unmatched records in access table

I have 2 tables and trying to pull the ECID field in table B that has different numbers when compared to table A.

Table A has the fields

Bas BPN ECID Wt
A57890 57890 1001 5.6
CC4455 4455 1003 2.9
456778 6778 1003 8.9
345666 5666 1009 1.8
7899 7899 2030 2.7
3456 3456 3567 3.99


Table B has the fields

Bas BPN ECID Wt
A57890 57890 1001 5.6
CC4455 4455 1004 2.9
456778 6778 1003 8.9
345666 5666 1011 1.8
7899 7899 2030 2.7
3456 3456 3009 3.99


Result looks like

Bas BPN ECID.TableA ECID.TableB Wt
CC4455 4455 1003 1004 2.9
345666 5666 1009 1011 1.8
3456 3456 3567 3009 3.99

Answer

It looks like the tables match on "BAS", so that's the field you need to join. (They might need to match on "BPN" instead - cannot tell from your example. If so, use that field for the join, or use both if necessary.) Then look for ECNs that are different.

SELECT A.BAS, A.BPN, A.ECID, A.Wt, B.ECID, B.Wt
FROM A INNER JOIN B ON A.BPN = B.BPN
WHERE (((B.ECID)<>[A].[ECID]));

This assumes you have all BAS records on both tables. If not, you may need a union query instead.

Comments