How to Pull Data into Query from "Correction" table

[rbeyga]

Hello everyone, and thank you in advance for any suggestions/help



I've recently built a database in Access 2010. The database has several tables that culminate in a "Final" query. However, there is a "Correction" table which will need to over ride the data in one column in the "Final" query, but only if data exists for a particular "Record Number." If the "Record Number" does not exist in the "Correction" table, then the data from the "Final" query should display normally. I'm trying to use the "Is Not Null" argument in my formula, but am having difficulty in making this work. Does anyone know a way to write this formula so that the data is pulled as described?

[rbeyga]

I realize I could run an update, but then I would need to make a table and run the update afterwards. Anyway to update a query?

[NTC]

Table A and Table B both have records ID 1,2,3

Table A has a field "ThisA" Table B has a field "ThisB"

In a query have both tables joined by the ID: drag their fields into the query

The business logic is to use ThisA unless there is a ThisB

In a new field in your query, which is called a calculated field you have: FinalThis: iif([ThisB]>0,[ThisB],[ThisA])

The >0 will typically work for both numbers and text when the alternative is a null.... so if something is in This B - it uses This B - else it uses This A