Select specific - comparing tables (1 Viewer)

[Tskutnik]

Here's one for you. I know how to do this with a load of IIF statements, but would rather avoid that....

I have 2 tables
- Source
- Enhanced

They both have the same fields (12 in each table), and relate through a field in both called [AccountNumber].

What I want to do is create a query that produces one output (Sort of merging them together) - selecting specific records from each table. The simple logic is - if the Enhanced table has a value in a field (not null) then use that value. If Null, use the Source value for the field

Is there some super cool slick way to do this without 11 different IIF"s?

 

[jdraw]

Can you show us what you have at the moment?

Is this a 1 time thing or recurring?

Does what you have work consistently? If so, why change?

 

[Tskutnik]

I don't have anything built yet. Wanted to see how best to do it before I dove in.
The DB will have 7 or 8 sets of table comparisons like this, all with different fields, so that is a lot of iif's to write.

 

[jdraw]

How about mocking up some sample data for your tables and posting same?

My first thought would be to consider 2 recordsets Enhanced and Source.
Create a third recordset that has data from Enhanced, except where Enhanced.fld is Null, then use value from Source.

What is the output format suppose to be? Report/form??