Query, Module or How find records in Table that match NAME but hav

[UtahJack]

Please help.. I need to be able to get a Query reslt and thus a report
(Report I can do if get results)

What it will accomplish is find people that have been given Temp License
numbers (normally starts with Letters, but also want to find license numbers
that might be off by only 1 digit), last name, first name (Which would have
SLBOR in the Board field but Source will always blanks if has SLBOR in Board)
and also show in results the perm license number, last name, first name
(Which would have DIV in the Source field but Board field will always be
blank if has DIV in Source)

Basic info Access 2003 -- Dbase name SLBOR.mdb, Table name is COMBINED,
Fields of interest - License ID, First Name, Last Name, Source, Board

Sample data records

Fields - License ID, First Name, Last Name, Source, Board
Rec info- NM12465 Jack Price SLBOR
Rec info- 34567SA00 Jack Price DIV
Rec info- 45867DB50 Sally Smith SLBOR
Rec info- 45867SA00 Sally Smith DIV
Rec info- 6345SA655 Tom Hanson SLBOR
Rec info- 86117SA00 Sara Sanchez DIV

Results would show either:
NM12465 Jack Price SLBOR
34567SA00 Jack Price DIV
45867DB50 Sally Smith SLBOR
45867SA00 Sally Smith DIV
Where I can see who has temp license and what the perm on is (Sorted by Last
then first name)

OR

Field in new table - SLBOR license ID DIV License First name
Last name
NM12465 34567SA00 Jack
Price
45867DB50 45867SA00 Sally
Smith

But would NOT show anything on
Rec info- 6345SA655 Tom Hanson SLBOR
Rec info- 86117SA00 Sara Sanchez DIV
Since no matching record was found with same last and first name with a
license number different in SLBOR vs the DIV fields.

I hope this is clear -- It is probably simple for someone who is not new to
this... I can not find anyway in Query Wizard to do this.. SO I guess I need
an SQL statement I could copy and paste into the SQL View of the Query ..

Thanks for your HELP!!!!!

 

[Eric D via AccessMonster.com]

In reality, Temp License and Perm License are two different entities.
Why don't you create another field - TempLic in your table. Might make your
query easier to define.

 

[UtahJack]

The purpose is to find the TEMP License or mismatched licenses numbers
between one Source DIV and are also in another Board SLOBOR, but there are
many names in the Source DIV that are not in the Board SLOBOR 30,000 records
when tables are combined...

Is ther a better way to do this before I have combined the two tables (DIV
and SLBOR) into one table... I thought it would be easier to Query one table..

Okay if I added a field TEMP or WRONG Licenses how would that help and how
would I accomplish my goal listed above.

Thanks for your time and efforts

-
Utah Jack

 

[Eric D via AccessMonster.com]

You really never want to combine fields into one table... simply to make it
easier to run a query. With 30,000 records, having a seperate table for Temp
Licenses is probably the correct thing to do... notice I said a seperate
table... not a seperate field. You would link the two by the Record ID. Then,
you could create a query that allows you to list all records in one table
that have corresponding records in the other table.

Can you create a small subset of records and let me take a look? Can't
guarantee anything, but I'm willing to take a look at what you have. Maybe I
can come up with a quick solution.

The purpose is to find the TEMP License or mismatched licenses numbers
between one Source DIV and are also in another Board SLOBOR, but there are
many names in the Source DIV that are not in the Board SLOBOR 30,000 records
when tables are combined...

Is ther a better way to do this before I have combined the two tables (DIV
and SLBOR) into one table... I thought it would be easier to Query one table..

Okay if I added a field TEMP or WRONG Licenses how would that help and how
would I accomplish my goal listed above.

Thanks for your time and efforts

-
Utah Jack

In reality, Temp License and Perm License are two different entities.
Why don't you create another field - TempLic in your table. Might make your

[quoted text clipped - 52 lines]