Missing data alert

[Obalola]

I create a monthly report from two tables (product data and mortgages) using an inner join query on product type column. Product data table contains static data whilst mortgages contain transactional data.

There are instances where new product types have been created during the month and included in mortgage data and i have not been informed to update my Product data table with details of the new product. This leads to inaccurate results in my report.

Can anyone please tell me how to include a function in my query that will alert me if there are non-matching product types in the two tables?

Thanks

 

[PHV]

Have a look at OUTER JOIN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Obalola]

Thanks PHV for your response.

I know an outer join can return values from non-matching records, but i will prefer to be alerted to ensure Product data is updated when required.

Regards

 

[BNPMike]

What do you mean by alerted?

An outer join can generate ###ALERT### in the rows where the join key is null. Isn't that enough for you?

 

[lameid]

Obalola,

To clarify, I think you want a query that shows where there are non-matching records from mortgage data to products. If there are no records here, your report should be valid. Or for that matter you could modify your query to show all mortgage data and related prduct data.... Then you will not be missing any mortgage data from your report, just the related product data.

A better question to ask and resolve is why the tables you are getting allow this to happen and what can be done to resolve it?

I'm guessing these tables are not access tables and therefore not maintained directly in your application. Likely there is a a problem relating to the two reported 'tables' being reported out of sync.