Compare to a table

[Guest]

I have a list of approved appraisers in a table called appraisers and a table
called collateral. In the collateral table is a field for appraisers. The
two tables are not linked.

I want to create a report that compares the appraiser in the collateral
table to the list of approved appraisers and adds the appraiser to the report
if the appraiser is not also listed in the approved appraiser table.

Is this possible, and if so, any ideas pointing me in the right direction
would be appreciated.

Thank you,
Kelvin

 

[Vincent Johns]

Here's a way to do that. Suppose your Tables look like this:

[appraisers] Table Datasheet View:

appraisers_ID Appraiser Name
------------- --------------
-2144411794 Ella
-1769413055 Sam
835233600 Jim

[collateral] Table Datasheet View:

collateral_ID Appraiser Name
------------- --------------
-629532073 Sam
1038142284 Butch

Then define the following Query...

[Q_NotThere] SQL:
SELECT collateral.[Appraiser Name]
FROM collateral LEFT JOIN appraisers
ON collateral.[Appraiser Name]
= appraisers.[Appraiser Name]
WHERE (((appraisers.appraisers_ID) Is Null))
ORDER BY collateral.[Appraiser Name];

.... to produce a list of appraisers not in your Table.

[Q_NotThere] Query Datasheet View:

Appraiser Name
--------------
Butch

You can use this Query as a basis for your Report.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.