Compare table Records and Count New Records

[Matt B. via AccessMonster.com]

I’m trying to build a query that will compare SSN from table-1 to table-2. If
table-2 does not have the SSN’s that are on table-1, then they are NEW SSN’s
records to be added to table2.

1. How do I write a query to check for these NEW SSN’s?
2. How do I write a query that will count the New SSN’s found and list the
count in a msgbox popup?

Here's the query I have now...... not sure if it is correct and it doesn't
display or count the number of NEW SSN records.

SELECT tbl1.SSN, tbl2.SSN
FROM tbl1 LEFT JOIN tbl2 ON tbl1.SSN = tbl2.SSN
WHERE (((tbl2.SSN) Is Null));


Thanks,
Matt B.

 

[MGFoster]

I’m trying to build a query that will compare SSN from table-1 to table-2. If
table-2 does not have the SSN’s that are on table-1, then they are NEW SSN’s
records to be added to table2.

1. How do I write a query to check for these NEW SSN’s?
2. How do I write a query that will count the New SSN’s found and list the
count in a msgbox popup?

Here's the query I have now...... not sure if it is correct and it doesn't
display or count the number of NEW SSN records.

SELECT tbl1.SSN, tbl2.SSN
FROM tbl1 LEFT JOIN tbl2 ON tbl1.SSN = tbl2.SSN
WHERE (((tbl2.SSN) Is Null));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Const SQL = "SELECT Count(*) AS NewRecords " & _
"FROM tbl1 RIGHT JOIN tbl2 ON tbl1.SSN = tbl2.SSN " & _
"WHERE tbl1.SSN Is Null"

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

If Not rs.EOF Then
MsgBox rs(0) & " New Records", vbInformation
End IF

Set rs = nothing
Set db = nothing

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxiVtYechKqOuFEgEQLpcACg+6c1UQsTJRq8NvsEqKjmeISv0R0AoNyi
kEsuphR1JooZuIIdIcynW2lv
=kcGf
-----END PGP SIGNATURE-----

 

[Matt B via AccessMonster.com]

Works Great.... THANKS!!

I’m trying to build a query that will compare SSN from table-1 to table-2. If
table-2 does not have the SSN’s that are on table-1, then they are NEW SSN’s

[quoted text clipped - 10 lines]

FROM tbl1 LEFT JOIN tbl2 ON tbl1.SSN = tbl2.SSN
WHERE (((tbl2.SSN) Is Null));

Const SQL = "SELECT Count(*) AS NewRecords " & _
"FROM tbl1 RIGHT JOIN tbl2 ON tbl1.SSN = tbl2.SSN " & _
"WHERE tbl1.SSN Is Null"

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

If Not rs.EOF Then
MsgBox rs(0) & " New Records", vbInformation
End IF

Set rs = nothing
Set db = nothing