Find Dup in in some field, but one field diff 1

[UtahJack]

STARTED WITH Two Dbases/Tables: DIV and SLBOR

I combined Dbases/Tables the combined Dbase/Table has (fields)including
Employees names (Last) (First) and
Professional license numbers(License)and SOURCE)and(BOARD)fields which show info such as (SOURCE) always shows "DIV" and (BOARD) always shows SLBOR.

The SOURCE = DIV records will show actual License numbers and some of the BOARD = SLBOR records will show Temporary License numbers.

I want to pull a list where the Dbases has Duplicate/Multiple records with matching NAME (Last) (First) fields, BUT the License numbers do not match, some of SLBOR will already have proper license number:

Example of what table may look like (But has 30,000 records)
Last First License Source Board
Smith John 123456SA12 DIV
Smith John TM12345667 SLBOR
Jones Sally 456987 DIV
Jones Sally 456987 SLBOR

Results of SQL Query will show me
Smith John 123456SA12 TM12345667 DIV SLBOR

But will not so any info on Jones since licenses already match.

 

[PCLewis]

Try:

SELECT sample2.Last, sample2.First,
       sample2.License, sample2.Source, sample2.Board
FROM sample2
LEFT JOIN (SELECT sample2.Last, sample2.First,
                  sample2.License, sample2.Source, sample2.Board
          FROM sample2
          WHERE (((sample2.Last) In 
                  (SELECT [Last] 
                   FROM [sample2] As Tmp 
                   GROUP BY [Last],[First],[License] 
                   HAVING Count(*)>1  
                     And [First] = [sample2].[First]
                     And [License] = [sample2].[License])))
         AND (sample2.source="DIV" OR sample2.Board="SLBOR")
         ORDER BY sample2.Last, sample2.First, sample2.License) AS Q1 
ON (sample2.Last = Q1.Last) AND (sample2.First = Q1.First)
WHERE (((sample2.Source)="DIV") AND ((Q1.License) Is Null));

 

[UtahJack]

I am using Microsoft ACCESS 2003 and trying to use the above info for an SQL statement in an ACCESS Query (or is there a better way)? Module,Macro etc in access
=============
In your programming above does "sample2" mean the name of the TABLE in ACCESS

The actual TABLE in my Access DBASE which contains all the Fields ref above is named COMBINED

So would I replace all the events of sample2 above with COMBINED?

What does Q1 represent. a new Table?

Thank you for taking your time.. this is a great thing you guys are doing..

Jack

 

[PCLewis]

Yes, sample2 was my name for your data. I am not a genius so I could not rattle off that SQL from the top of my head I created your table and then constructed a query in the query builder.

Q1 is the name given to one of the in-line select statements. Note the "AS Q1"? That's used so that you can reference the fields inside the in-line select statement for the join.

If you replace my double quotes with single quotes, then stuff the whole thing into a string variable, you could run the statement into a recordset:

dim oRS as adodb.recordset
set oRs=currentproject.connection.execute(sql$)

or 

Dim ors As DAO.Recordset
Set ors = CurrentDb().OpenRecordset(SQL$)
[code]

Or yes, paste it into the SQL part of a query in Access and save it.